Re: RE: the opposite of a join?

On 10/3/07, jd.pillion@gmail.com <jd.pillion@gmail.com> wrote: > > Hi J, > > > > > > Checkout this, > > > > > > SELECT * FROM tbl_company where id not in (SELECT companyID from > > tbl_contacts) > > > > > Brilliant! This is exactly what I was looking for, and is quite > logical/readable! Thanks to everyone for the ideas! > > J > No, don't do this! It is a very inefficient way to retrieve the information you are looking for (Use a query analysis tool to check it out yourself, if you want) - if your tables get to any larger size at all, it will start having a noticeable performance impact on your script (not to mention your DB) - let the DB do the hard work and use a LEFT JOIN syntax, the database can optimize that much more efficiently. Only if your DB doesn't support the LEFT JOIN syntax would you want to do the above. -James > > > > > > > > > > > > > > > Regards, > > Lasitha Alawatta > > Application Developer > > Destinations of the World Holding Establishment > > P O Box: 19950 > > Dubai, United Arab Emirates > > ( Ph +971 4 295 8510 (Board) / 1464 (Ext.) > > 7 Fax +971 4 295 8910 > > + lasitha.a@dotw.com > > > > -----Original Message----- > > From: John Pillion [mailto:john@conv-dg.com] > > <mailto:john@conv-dg.com%5D> > On Behalf Of > > jd.pillion@gmail.com > > Sent: Wednesday, October 03, 2007 2:21 PM > > To: php-general@lists.php.net; php-db@lists.php.net > > Subject: [PHP-DB] the opposite of a join? > > > > I have a company table and a contacts table. In the contacts table, > > there > > is a field called "companyID" which is a link to a row in the company > > table. > > > > > > > > What is the easiest way to query the company table for all the company > > rows > > whose ID is NOT linked to in the contact table? Basically, the opposite > > of a > > join? > > > > > > > > Thanks > > > > > > > > J > > > > > > > > DOTW DISCLAIMER: > > > > This e-mail and any attachments are strictly confidential and intended > > for the addressee only. If you are not the named addressee you must not > > disclose, copy or take > > any action in reliance of this transmission and you should notify us as > > soon as possible. If you have received it in error, please contact the > > message sender immediately. > > This e-mail and any attachments are believed to be free from viruses but > > it is your responsibility to carry out all necessary virus checks and > > DOTW accepts no liability > > in connection therewith. > > > > This e-mail and all other electronic (including voice) communications > > from the sender's company are for informational purposes only. No such > > communication is intended > > by the sender to constitute either an electronic record or an electronic > > signature or to constitute any agreement by the sender to conduct a > > transaction by electronic means. > > > > > > >