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.

--
Jim Lucas

   "Some men are born to greatness, some achieve greatness,
       and some have greatness thrust upon them."

Twelfth Night, Act II, Scene V
    by William Shakespeare

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to