Different between LOOP JOIN, HASH JOIN and MERGE JOIN in SQL Server

LOOP JOIN

As its name, it use Loop Algorithm to perform join operation. For a example:

        SELECT *            
        FROM
            Sales.SalesOrderHeader H
            INNER LOOP JOIN Sales.SalesOrderDetail D ON H.SalesOrderID = D.SalesOrderID

• Step 1: Loop each record in table [SalesOrderHeader].
• Step 2: Loop each record in table [SalesOrderDetail]. If table [SalesOrderDetail] has an index on column [SalesOrderID], it will loop on index instead of loop on table.
• Step 3: If match – return Data.

HASH JOIN

use Hash Algorithm to perform join operation. For a example:

        SELECT *
        FROM
            Sales.SalesOrderHeader H
            INNER HASH JOIN Sales.SalesOrderDetail D ON H.SalesOrderID = D.SalesOrderID

MERGE JOIN 

like LOOP JOIN but it will be sort 2 table [Order] and table [OrderDetail] before do loop operation. For a example:

        SELECT *
        FROM
            Sales.SalesOrderHeader H
            INNER MERGE JOIN Sales.SalesOrderDetail D ON H.SalesOrderID = D.SalesOrderID

The default sort of table [OrderDetail] is by [OrderDetailID] column. When MERGE JOIN is used. It’s will sort [Order] and [OrderDetail] by OrderID and do the loop. The loop on [OrderDetail] will not be repeated to the beginning. For a example:

HOW DOES SYSTEM DECIDE TO USE JOIN?

Which table is run first, I will call it as source table, the rest is target table. In this example, source table is [SaleOrderHeader], target is [SaleOrderDetail].

Except you write the explicit query such as INNER LOOP|HASH|MERGE JOIN, the system will use the Join Algorithm by itself based on multiple criterions (statistic, index…).

By default, system will use:

• HASH JOIN – when target table has no index on on join column OR source table is large and target table is small.
• LOOP JOIN – when target table has an index on join column.

        --LOOP JOIN Example
        SELECT
            H.SalesOrderNumber,
            H.CustomerID,
            H.SalesOrderID,
            D.ProductID,
            D.LineTotal
        FROM
            Sales.SalesOrderHeader H
            INNER JOIN Sales.SalesOrderDetail D ON H.SalesOrderID = D.SalesOrderID
        WHERE
            H.SalesOrderNumber = 'SO43677'

WHEN TO USE MANUAL FORCE JOIN OPERATION?

• LOOP JOIN – source table is large and target table is large and target table has index on join column.
• HASH JOIN – source table is large and target table is small (with index or without index on join column).
• MERGE JOIN – source table is large and target table is large and no index on target table (usually target table is temporary table and large). The

Updating…