I agree with this. Never use a subquery when a join will work. The
optimizer with thank you with performance.
James Ausmus wrote:
On 10/3/07, jd.pillion@xxxxxxxxx <jd.pillion@xxxxxxxxx> 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@xxxxxxxx
-----Original Message-----
From: John Pillion [mailto:john@xxxxxxxxxxx] <mailto:john@xxxxxxxxxxx%5D>
On Behalf Of
jd.pillion@xxxxxxxxx
Sent: Wednesday, October 03, 2007 2:21 PM
To: php-general@xxxxxxxxxxxxx; php-db@xxxxxxxxxxxxx
Subject: 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.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
