Inner loop join and Left outer loop join

Question

Inner loop join and Left outer loop join

prasannaj 13253

Old Hand

Points: 332
More actions
May 30, 2018 at 7:21 am

#360221

Can any one explain me what is inner loop join and left outer loop join .. I tried to search but I dint find good answer any where.
Below is the sample of it.
inner join Person as emp1 on ( emp1.PersonPK = VU__PKS.p1)
inner loop join PersonDomain on emp1.PersonPK = PersonDomain.PersonFK and isprimary = 1
Thanks in Advance,
Prasanna.
October 10, 2022 at 11:54 am

This was removed by the editor as SPAM

Viewing 14 posts - 1 through 13 (of 13 total)

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

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

prasannaj 13253 - Wednesday, May 30, 2018 7:20 AM
Can any one explain me what is inner loop join and left outer loop join .. I tried to search but I dint find good answer any where.
Below is the sample of it.
inner join Person as emp1 on ( emp1.PersonPK = VU__PKS.p1)
inner loop join PersonDomain on emp1.PersonPK = PersonDomain.PersonFK and isprimary = 1
Thanks in Advance,
Prasanna.

Are you referring to INNER JOIN and LEFT OUTER JOIN in the T-SQL syntax?
😎

davidandrews13 SSCertifiable Points: 6792 More actions · Answer 2

i found this. never heard of it before, though.

https://stackoverflow.com/questions/6529053/when-should-i-use-an-inner-loop-join-instead-of-an-inner-join

prasannaj 13253 Old Hand Points: 332 More actions · Answer 3

Eirikur Eiriksson - Wednesday, May 30, 2018 7:23 AM
prasannaj 13253 - Wednesday, May 30, 2018 7:20 AM
Can any one explain me what is inner loop join and left outer loop join .. I tried to search but I dint find good answer any where.
Below is the sample of it.
inner join Person as emp1 on ( emp1.PersonPK = VU__PKS.p1)
inner loop join PersonDomain on emp1.PersonPK = PersonDomain.PersonFK and isprimary = 1
Thanks in Advance,
Prasanna.
Are you referring to INNER JOIN and LEFT OUTER JOIN in the T-SQL syntax?
😎

No, I I am referring to Inner loop join and Left outer loop join

prasannaj 13253 Old Hand Points: 332 More actions · Answer 4

davidandrews13 - Wednesday, May 30, 2018 7:37 AM
i found this. never heard of it before, though.
https://stackoverflow.com/questions/6529053/when-should-i-use-an-inner-loop-join-instead-of-an-inner-join

Thank you 🙂 I will try to understand this

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

LOOP, HASH & MERGE are JOIN hints to tell the SQL Server engine what physical operation should use for that specific JOIN. Usually, the engine will choose the best operation based on statistics of the tables. This is a way to force them when it's constantly failing to choose the correct one.
You should not use them frequently and they should be a last resource.
More info on:
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-join?view=sql-server-2017
https://blogs.msdn.microsoft.com/bradchen/2016/11/06/sql-server-physical-joins/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Steve Jones - SSC Editor SSC Guru Points: 734434 More actions · Answer 6

Luis Cazares - Wednesday, May 30, 2018 8:31 AM
You should not use them frequently and they should be a last resource.

In general, unless you are very advanced at tuning and have specific reasons, you shouldn't use them at all. Your default position should be that you don't know more than the optimizer.

Luis Cazares SSC Guru Points: 183698 More actions · Answer 7

Steve Jones - SSC Editor - Wednesday, May 30, 2018 8:35 AM
Luis Cazares - Wednesday, May 30, 2018 8:31 AM
You should not use them frequently and they should be a last resource.
In general, unless you are very advanced at tuning and have specific reasons, you shouldn't use them at all. Your default position should be that you don't know more than the optimizer.

And the warning applies to all kind of query hints.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 8

Luis Cazares - Wednesday, May 30, 2018 8:31 AM
LOOP, HASH & MERGE are JOIN hints to tell the SQL Server engine what physical operation should use for that specific JOIN. Usually, the engine will choose the best operation based on statistics of the tables. This is a way to force them when it's constantly failing to choose the correct one.
You should not use them frequently and they should be a last resource.
More info on:
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-join?view=sql-server-2017
https://blogs.msdn.microsoft.com/bradchen/2016/11/06/sql-server-physical-joins/

Also the join direction:
FROM TableA a
INNER LOOP JOIN TableB b
= for each row in a, return any matches in b.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 9

It sounds like some clarification may be in order here...
There are the joins that we specify when writing T-SQL and there are the joins that SQL Server uses in the background to actually execute the query.
These are two very different things.

The joins we specify when writing T-SQL (JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN & CROSS JOIN) all provide information about how one table relates to the other. They say noting about the specific algorithms to be used when the query is executed.
When your query is sent off to to the optimizer, it gets parsed and it decides, based on several factors, how best to execute your query and creates an execution plan. In that plan, your joins are represented as loop joins, merge joins & hash joins. These represent the specific join algorithms SQL Server has deemed most appropriate based on the information it has available.

As stated in previous posts, YES, you can can force SQL Server to use a specific algorithm using join hints BUT, you'd be well advised to steer well clear of them until you have a solid understanding of how each of the algorithms works and why one is more advantageous than another and under what circumstances. Even then they should only be used when you can clearly demonstrate that SQL Server is choosing the wrong algorithm when left to it's own devises... and probably not even then...

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 10

prasannaj 13253 - Wednesday, May 30, 2018 7:59 AM
Eirikur Eiriksson - Wednesday, May 30, 2018 7:23 AM
prasannaj 13253 - Wednesday, May 30, 2018 7:20 AM
Can any one explain me what is inner loop join and left outer loop join .. I tried to search but I dint find good answer any where.
Below is the sample of it.
inner join Person as emp1 on ( emp1.PersonPK = VU__PKS.p1)
inner loop join PersonDomain on emp1.PersonPK = PersonDomain.PersonFK and isprimary = 1
Thanks in Advance,
Prasanna.
Are you referring to INNER JOIN and LEFT OUTER JOIN in the T-SQL syntax?
😎
No, I I am referring to Inner loop join and Left outer loop join

So just to clarify, you're question is on the join hints?
😎

Jason A. Long SSC-Insane Points: 23780 More actions · Answer 11

Eirikur Eiriksson - Thursday, May 31, 2018 8:28 AM
prasannaj 13253 - Wednesday, May 30, 2018 7:59 AM
Eirikur Eiriksson - Wednesday, May 30, 2018 7:23 AM
prasannaj 13253 - Wednesday, May 30, 2018 7:20 AM
Can any one explain me what is inner loop join and left outer loop join .. I tried to search but I dint find good answer any where.
Below is the sample of it.
inner join Person as emp1 on ( emp1.PersonPK = VU__PKS.p1)
inner loop join PersonDomain on emp1.PersonPK = PersonDomain.PersonFK and isprimary = 1
Thanks in Advance,
Prasanna.
Are you referring to INNER JOIN and LEFT OUTER JOIN in the T-SQL syntax?
😎
No, I I am referring to Inner loop join and Left outer loop join
So just to clarify, you're question is on the join hints?
😎

... or trying to read an execution plan...

Janakiram Newbie Points: 2 More actions · Answer 12

Janakiram

Newbie

Points: 2

October 10, 2022 at 11:53 am

#4097431

Very good, thanks for the explanation