How is Nested Loop Join is different from Inner Join in terms of working\pseudocode and what points suggests using Nested Loop Join instead of Inner Join.
2
-
4sorry, we can't do your homework.Matt Busche– Matt Busche2013-04-04 13:18:38 +00:00Commented Apr 4, 2013 at 13:18
-
An inner join is a logical operator and nested loops is a physical operator, so an inner join may be using a nested loops join anyway when executed depending on the inputs. "The Nested Loops operator performs the inner join, left outer join, left semi join, and left anti semi join logical operations." from msdn.microsoft.com/en-gb/library/ms187871(v=sql.100).aspxsteoleary– steoleary2013-04-04 13:25:18 +00:00Commented Apr 4, 2013 at 13:25
Add a comment
|
1 Answer
It isn't "instead of" - a logical INNER JOIN can be processed in multiple physical ways - nested loops, hash match, or merge. There is also a fourth type, adaptive join, but that's kind of cheating... it's really just deferring a choice between nested loops and hash match.
A nested loops join is typically chosen when one side of the join is relatively small. Think about joining a Customers table to the Orders table - ideally a customer will have placed a large number of orders, so the way a nested loop join would work (conceptually), if the imbalance is high enough, is that it starts with the first customer, collects their orders, then moves to the next customer, collects their orders, etc. In reality the optimizer will make a decision at plan compilation time about which physical operation makes most sense, and it does this based on a variety of factors, such as cardinality of the two tables, statistics, hardware resources, etc. etc.
(I'll briefly state that a merge join works like a zipper, and a hash join works like a set of buckets. But you can read up on these elsewhere for more details.)
Next time, please search for these concepts, and come for programming help when you have actual problems implementing them or understanding why a specific join method was chosen in a specific case. This isn't the library. :-)
1 Comment
jaczjill
The sentence "a logical INNER JOIN can be processed in multiple physical ways" cleared my doubts! Thanks for detailed answer & suggestion for future!