Re: Re: the opposite of a join?

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