Types of Join in SQL Server

There are three types of join in SQL Server, and I'm not talking about INNER, LEFT/RIGHT, OUTER or CROSS. I'm talking about how SQL Server works under the hood to actually perform the query.

The query can either use:

• Nested loop join (LOOP)
• Merge join (MERGE)
• Hash join (HASH)

The Query Optimizer in SQL Server will automatically figure out which join to use when you execute the query, but you can tell SQL which to using a 'join hint'. If you find your query is running slow, you can try out different options, like this:

FROM 

Table A a

INNER LOOP JOIN

Table B b

ON a.Col = b.Col

As a rule of thumb, Nested Loops should be used when either both tables in the join expression have a small number of rows (up to 10K), or when one of the tables has many rows, an the other table has few (up to 10K). Merge joins should be used when both tables have a medium amount of rows, and Hash joins should be used when both tables have many rows.