SQL Server - Join vs. Subquery

Asked By Audrey Ng on 21-Jul-08 12:46 PM
Hi everyone,

I couldn't find a good answer on google....but here goes..

What is the difference between a join and a subquery?
Which one is faster and why?


*** Sent via Developersdex http://www.developersdex.com ***

Eric Isaacs replied on 23-Jul-08 09:17 PM
I would say the performance would depend on a number of factors
including indexes on the tables.

Here's a thread that discusses it as well.


JOINs are typically done in one pass and the results are displayed,
whereas subqueries may require more than one execution of the subquery
to produce the expected results.   But that can sometimes be faster.

-Eric Isaacs
TheSQLGuru replied on 21-Jul-08 01:40 PM
Second question:  it depends on a LOT of factors

First question:  Join actually matches (inner) or attempts to (left/right)
data on criteria (a.ID = b.ID, a.ID > b.ID, etc).  Thus you can get 1 to 1,
1 to many, 0 to many, many to 0, etc rows back, including multiples of rows
(cartesian product)
Subquery looks to restrict rows by matching to a set produced based on
rows in outer query.

Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
Roy Harvey (SQL Server MVP) replied on 21-Jul-08 01:54 PM
Which kind of subquery?  There could be a subquery in the SELECT list,
in the FROM clause as a derived table, in the WHERE clause as an
EXISTS or an IN.....

In general there is no reason to assume that a subquery will be faster
or slower than a join.  Specific cases can point one way or the other,
but there are too many variables for a general answer.  There are
cases where a subquery should be faster - an EXISTS test against a
JOIN, where the EXISTS stops at the first match but the JOIN has to
deal with every match.

Roy Harvey
Beacon Falls, CT

On Mon, 21 Jul 2008 09:46:35 -0700, Audrey Ng <odd26uk@yahoo.co.uk>
Alex Kuznetsov replied on 23-Jul-08 09:17 PM
Note that in many cases the optimizer rewrites queries with correlated
subqueries as outer joins, so in many cases the performance is the
MarianoGome replied on 21-Jul-08 10:36 PM
In many cases you are forced to create joins to subqueries as well, because
you may be forced to summarize data prior to attempting the join. So I agree
with others, performance is on a case by case basis. Sometimes you have to
try several query methods before selecting the one that is less taxing on the
query engine.

Best regards,

Mariano Gomez