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
