When should I use an INNER -LOOP- JOIN instead of an INNER JOIN

sql-server sql-server-2008 inner-join join-hints
Pure.Krome picture Pure.Krome · Jun 30, 2011 · Viewed 39.2k times · Source

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?

Answer

Ash picture Ash · Jun 30, 2011

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.

Related questions

How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
Is having an 'OR' in an INNER JOIN condition a bad idea?

In trying to improve the speed of an immensely slow query (several minutes on two tables with only ~50,000 rows each, on SQL Server 2008 if it matters), I narrowed down the problem to an OR in my inner join, as in: …

Read More
SQL: Inner joining two massive tables

I have two massive tables with about 100 million records each and I'm afraid I needed to perform an Inner Join between the two. Now, both tables are very simple; here's the description: BioEntity table: BioEntityId (int) Name (nvarchar 4000, although this …

Read More