Hi Varsha,

Generaly if you have the choice of using a JOIN or a subquery to perform the
same task, generally the JOIN (often an OUTER JOIN) is faster. But this is
not always the case. For example, if the returned data is going to be small,
or if the are no indexes on the joined columns, then a subquery may indeed
be faster.

The only way to really know for sure is to try both methods and then take a
look at their query plans. If this operation is run often, you should
seriously consider writing the code both ways, and selecting the code that
is most efficient.

HTH,
Ravinder


-----Original Message-----
From: Varsha Savyasachi [mailto:[Email Address Removed] Tuesday, April 29, 2003 11:00 AM
To: LazyDBA.com Discussion
Subject: Which is faster?


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



MS Sql Server LazyDBA home page