Microsoft SQL Server Query Hints JOIN Hints
Example
When you join two tables, SQL Server query optimizer (QO) can choose different types of joins that will be used in query:
- HASH join
- LOOP join
- MERGE join
QO will explore plans and choose the optimal operator for joining tables. However, if you are sure that you know what would be the optimal join operator, you can specify what kind of JOIN should be used. Inner LOOP join will force QO to choose Nested loop join while joining two tables:
select top 100 *
from Sales.Orders o
inner loop join Sales.OrderLines ol
on o.OrderID = ol.OrderID
inner merge join will force MERGE join operator:
select top 100 *
from Sales.Orders o
inner merge join Sales.OrderLines ol
on o.OrderID = ol.OrderID
inner hash join will force HASH join operator:
select top 100 *
from Sales.Orders o
inner hash join Sales.OrderLines ol
on o.OrderID = ol.OrderID
