What is the difference between SubQuery and Joins in terms of Performance

Question

What is the difference between SubQuery and Joins in terms of Performance

ravi@sql

SSCrazy

Points: 2121
More actions
April 5, 2012 at 9:36 pm

#266883

Hi All,
What is the difference between SubQuery and Joins in terms of Performance,
I know that joins are good rather than sub queries, but how it make the dissference while executing internally ?
Thanks in advance
Ravi@sql

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

CapnHector SSCoach Points: 16143 More actions · Answer 1

in general. sub selects have to be executed first or for each row depending on the case and then joined to the outer query. a join is executed once as the outer query. but it is not a blanket statement that joins are better than sub queries. there are cases where a sub query will be faster (not many but they are out there). as always test your solutions before rolling them out to production.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Usually none.

The idea that subqueries execute once per row of the outer query is wrong in most cases (there are only two cases where that is true). The vast majority of subqueries are treated by the optimiser just like a join)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ravi@sql SSCrazy Points: 2121 More actions · Answer 3

Thanks Gail Shaw.

Can you share me some example or any links which describes what you have said.

Its one of best practice in my comapny to not use sub queries any where. Even while tuning

i have removed and replaced with joins which increases the query performance.

But your statements are contradictory so can you explain around it ?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

That 'best practice' is nothing of the sort. Subqueries are very useful and usually very well performing.

The type that you should avoid is the type that have (SELECT TOP (1) ... ORDER BY) and are in the select clause. Those are just about the only ones that really do execute once per row of the outer query.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass