I would also suggest to limit yourself to things you actually need not to select the whole table.


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.


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.

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

Reply via email to