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 *
NOT IN (
SQL #2 SELECT company.*
LEFT JOIN contacts
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