Menu
Today I learned about a thing in SQL Server called INNER LOOP JOIN.
What does this mean? (Google is not helping .. or should I say ... the blog posts about it are a bit .. technical and are blowing my mind).
Also, what are some common scenarios that would be a good idea to use an INNER LOOP JOIN over a standard INNER JOIN?
839
Difference between JOIN and INNER JOINJOIN returns all rows from tables where the key record of one table is equal to the key records of another table. The INNER JOIN selects all rows from both participating tables as long as there is a match between the columns.
You'll use INNER JOIN when you want to return only records having pair on both sides, and you'll use LEFT JOIN when you need all records from the “left” table, no matter if they have pair in the “right” table or not.
The biggest difference between an INNER JOIN and an OUTER JOIN is that the inner join will keep only the information from both tables that's related to each other (in the resulting table). An Outer Join, on the other hand, will also keep information that is not related to the other table in the resulting table.
INNER JOIN is the default if you don't specify the type when you use the word JOIN. You can also use LEFT OUTER JOIN or RIGHT OUTER JOIN, in which case the word OUTER is optional, or you can specify CROSS JOIN.
LOOP | HASH | MERGE are Join hints, specifying that the join in the query should use looping, hashing, or merging. Using LOOP |HASH | MERGE JOIN enforces a particular join between two tables. LOOP cannot be specified together with RIGHT or FULL as a join type.
You should always use INNER JOIN. Let the query optimizer decide whether it wants to do a LOOP, MERGE, or HASH join. In almost all cases the optimizer will make a better judgement. Which one will be used and when can be found from my presentation http://sqlbits.com/Sessions/Event4/Understanding_Graphical_Execution_Plans.
153
What you are referring to is a join hint. Like other hints, join hints should only be specified as a last resort as most of the time SQL server would pick correct algorithm. A good article to explain some of it is this.
45
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
