I would imagine a subquery. As it's really going through two queries, the
first a table scan.

You could find out in actual figures buy using the show execution plan, in
Query Analyser.

Reg.
__________________________________________
Anthony Hand
DBA | Systems Integration
Aon Risk Services Australia Ltd
Ph: +61 2 9253 7516
Fax: +61 2 92537952
Mob: +61 415 877 988




Varsha
Savyasachi To: "LazyDBA.com Discussion" <[Email Address Removed]
<[Email Address Removed] cc:
Gain.com> bcc:
Subject: Which is faster?
29/04/2003
03:30 PM






Hi Gurus,

Which is faster - a subquery or a join ?

select groupid,ob
jectid from egmemberobject
where groupid in (select groupid from eggroup where grouptype=2)

select a.groupid,a.objectid from egmemberobject a, eggroup b
where a.groupid=b.groupid
and b.grouptype=2

Could somebody please list or site examples of specific cases when you
would
use one over the other despite the performance issues?

thanks,

Varsha Savyasachi









**********************************************************************
"The information contained in this email and any attached files is strictly private and confidential. This email should be read by the intended addressee only. If the recipient of this message is not the intended addressee, please call Aon Risk Services Australia, Aon Consulting, Aon Re Australia Ltd and other associated Aon Companies on +61 2 92537000 and promptly delete this email and any attachments.

The intended recipient of this email may only use, reproduce, disclose or distribute the information contained in this email and any attached files with Aon's permission. If you are not the intended addressee, you are strictly prohibited from using, reproducing, disclosing or distributing the information contained in this email and any attached files.

Aon advises that this email and any attached files should be scanned to detect viruses. Aon accepts no liability for loss or damage (whether caused by negligence or not) resulting from the use of any attached files."
**********************************************************************

MS Sql Server LazyDBA home page