Joins

Question

Joins

tonyarp05 61903

SSCommitted

Points: 1515
More actions
April 18, 2012 at 3:03 am

#256568

Hi,
Could you please help me on following joins.
1.Merge Join
2.Hash Join
3.Nested Loop join
Please let me know is there any link to study about above joins briefly with examples.
Thanks,
Tony

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

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

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

Merger Join -- if the both left and right tables are sorted then merge join will take place.

Nested loop-- If the left side table having less number of rows and right table has large number of records then nested loop will take place.

If both cases are not satisfied then Hash join will take place .

It will be decided by sql server engine.

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

ravi@sql (4/18/2012)
Merger Join -- if the both left and right tables are sorted then merge join will take place.
Nested loop-- If the left side table having less number of rows and right table has large number of records then nested loop will take place.
If both cases are not satisfied then Hash join will take place .
It will be decided by sql server engine.

Nothing to do with which table is on the left or on the right, unless you are forcing join order. The order that tables are specified in the query does not decide the order they will be joined.

There's a post on my blog that covers these, I'll find it when I get back to a real computer.

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

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 3

GilaMonster (4/18/2012)
ravi@sql (4/18/2012)
Merger Join -- if the both left and right tables are sorted then merge join will take place.
Nested loop-- If the left side table having less number of rows and right table has large number of records then nested loop will take place.
If both cases are not satisfied then Hash join will take place .
It will be decided by sql server engine.
Nothing to do with which table is on the left or on the right, unless you are forcing join order. The order that tables are specified in the query does not decide the order they will be joined.
There's a post on my blog that covers these, I'll find it when I get back to a real computer.

I'll save you the trouble Gail 🙂

To the OP here is the link Gail was referring to:

http://sqlinthewild.co.za/index.php/2007/12/30/execution-plan-operations-joins/

Andy

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

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

Andy Hyslop (4/18/2012)
To the OP here is the link Gail was referring to:
http://sqlinthewild.co.za/index.php/2007/12/30/execution-plan-operations-joins/

Yes, indeed. Thank you. Also read Craig Freedman's post that it links to.

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