41

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?

johnnyRose
  • 7,310
  • 17
  • 40
  • 61
Pure.Krome
  • 84,693
  • 113
  • 396
  • 647

2 Answers2

40

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.

(Edit: The link to the video presentation on that page is broken, but Archive.org has archived it here)

Chrstfer CB
  • 53
  • 6
Ash
  • 2,531
  • 2
  • 29
  • 38
  • quote "Let the query optimizer decided(sic)" .. How is this (bascially) decided? Via statistics? – Pure.Krome Jun 30 '11 at 01:31
  • 2
    The different join types are optimized for different query semantics, and yes, these are mostly based on statistics / cardinality, but also other factors such as whether one side of the join is much larger than the other side, whether the input on each side of the join is sorted, etc. I am with the others... if the blog posts about this are blowing your mind, you are far better off letting the optimizer decide until you have mastered the differences between the types of joins *and* feel that you are smarter than the optimizer. – Aaron Bertrand Jun 30 '11 at 01:44
  • 1
    @pure.Krone . You are correct . Though the extact algo is not known to me I know that it is a function of Statistics of the underlined tables and Indexes, the way filegroups are created for underlined objects , no of cores of the server (MAXDOP) and many other factor. If you want to dig deep I suggest you to read Connor Cunningham's Blog who is Principal Architect of MS SQL Core. – Ash Jun 30 '11 at 11:04
11

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.

amit_g
  • 30,880
  • 8
  • 61
  • 118