Re: Re: the opposite of a join?

I would also suggest to limit yourself to things you actually need not to select the whole table. Aleksandar Jim Lucas wrote:

Colin Guthrie wrote:

Martin Marques wrote:

SELECT * FROM company WHERE id NOT IN (SELECT companyID FROM contacts);

Not ideal as has been mentioned else where in this thread. Col

I think one would have to take into account the DB type being used here. I can have MySQL and PostgreSQL setup and running with the same table structure (well, as close as you can get) configured with two different databases in them.

SQL #1    SELECT    *
    FROM    company
    WHERE    id
    NOT IN    (
        SELECT    companyID
        FROM    contacts
        );

SQL #2     SELECT    company.*
    FROM    company
    LEFT JOIN contacts
    ON    (
        company.companyID = contacts.companyID
        )
    WHERE    contacts.companyID IS NULL

Now, both SQL statements will perform relatively the same on either DB's with a small data set. but, if you have a large data set, MySQL will benefit from having the Sub-Query style statement Where-as PostgreSQL will shine with the JOIN command. This is only from my own personal testing. Mind you that I have only been using PostgreSQL for a year or so. But one problem that I have always ran into with MySQL is that when JOIN'ing tables that have large data sets is a PITA. So, if I was running MySQL, I would use SQL #1, but if I were using PostgreSQL, I would use SQL #2 If anybody else has suggestions or comments about performance between MySQL vs. PostgreSQL with regards to similarly formed SQL calls, I would like to hear their experiences.