Nested Loops, Hash Match, and Merge Join

A query can contain a single INNER JOIN and produce the same result through different physical strategies. The JOIN type belongs to the logical expression; Nested Loops, Hash Match, and Merge Join describe how SQL Server related the two inputs.

None of the three operators is favorable or unfavorable by itself. To interpret it, observe what it receives, how it finds matches, and what work it accumulates.

The examples isolate each strategy through join hints to make it visible. OPTION and query hints explains what these instructions restrict and why they should not automatically become a permanent correction.

How to open the three plans

This test prepares #ClienteJoin and #PedidoJoin in the same connection. Run the following block once in the query window that will be used for all three cases:

IF OBJECT_ID(N'tempdb..#PedidoJoin', N'U') IS NOT NULL
    DROP TABLE #PedidoJoin;

IF OBJECT_ID(N'tempdb..#ClienteJoin', N'U') IS NOT NULL
    DROP TABLE #ClienteJoin;

CREATE TABLE #ClienteJoin
(
    IdCliente INT NOT NULL,
    Segmento  VARCHAR(20) NOT NULL,
    CONSTRAINT PK_ClienteJoin
        PRIMARY KEY CLUSTERED (IdCliente)
);

CREATE TABLE #PedidoJoin
(
    IdCliente   INT NOT NULL,
    IdPedido    INT NOT NULL,
    FechaPedido DATE NOT NULL,
    Total       DECIMAL(12, 2) NOT NULL,
    CONSTRAINT PK_PedidoJoin
        PRIMARY KEY CLUSTERED (IdCliente, IdPedido)
);

INSERT INTO #ClienteJoin
    (IdCliente, Segmento)
SELECT
    customers.IdCliente,
    CASE customers.IdCliente % 3
        WHEN 0 THEN 'Enterprise'
        WHEN 1 THEN 'Professional'
        ELSE 'Starter'
    END
FROM
(
    SELECT DISTINCT p.IdCliente
    FROM dbo.PedidoLaboratorio AS p
) AS customers;

INSERT INTO #PedidoJoin
    (IdCliente, IdPedido, FechaPedido, Total)
SELECT
    p.IdCliente,
    p.IdPedido,
    p.FechaPedido,
    p.Total
FROM dbo.PedidoLaboratorio AS p;

Enable Include Actual Execution Plan with Ctrl+M and run each case separately after preparing the tables. Open Execution plan and confirm the full name of the central operator: Nested Loops, Hash Match (Inner Join), or Merge Join. The hints isolate mechanisms for study; they do not establish which algorithm should be forced in an application.

Two inputs and one condition

A join operator receives two row sets. The condition identifies which values can match; the physical operator defines the procedure used to find them.

SELECT
    c.IdCliente,
    c.Segmento,
    p.IdPedido,
    p.Total
FROM #ClienteJoin AS c
INNER JOIN #PedidoJoin AS p
    ON p.IdCliente = c.IdCliente;

The query does not instruct SQL Server to use loops, a hash table, or sorted inputs. The optimizer compares alternatives using estimated rows, indexes, available order, memory, and calculated cost.

The following captures come from the same controlled test, with 12,000 fictional customers and 100,000 fictional orders. The LOOP, HASH, and MERGE hints isolate each strategy for instructional purposes; they are not recommendations for application queries.

Nested Loops

Nested Loops takes one row from the outer input and requests matches from the inner input. It repeats that request for every outer row.

1. 1
   Outer input

   Supplies the rows that start each cycle.

2. 2
   Inner input

   Finds the matches requested by the current outer row.

1. 1
   Join operator

   Nested Loops coordinates the two branches and returns rows that satisfy the condition.

2. 2
   Outer input

   The first Clustered Index Seek supplies the three customers that start the loops.

3. 3
   Inner input

   The second Clustered Index Seek finds the orders associated with each customer it receives.

Query that produces the plan

The filter limits the outer input to three customers. INNER LOOP JOIN makes it possible to observe how each customer starts a search in the inner input.

SELECT
    c.IdCliente,
    c.Segmento,
    p.IdPedido,
    p.FechaPedido,
    p.Total
FROM #ClienteJoin AS c
INNER LOOP JOIN #PedidoJoin AS p
    ON p.IdCliente = c.IdCliente
WHERE c.IdCliente BETWEEN 1 AND 3
ORDER BY
    c.IdCliente,
    p.IdPedido
OPTION (RECOMPILE);

A small outer input and selective inner access can form an efficient combination. The same mechanism accumulates work when it receives many outer rows or each search reads more than expected.

In the plan, relate Actual Number of Rows on the outer input to Number of Executions on the inner branch. In the Key Lookup example, 90 locators produced 90 retrieval requests.

Hash Match

Hash Match gives its inputs different roles. The build input creates an in-memory hash table organized by the join key. Each row from the probe input then computes the same hash function and searches the group where its matches may exist.

1. 1
   Build

   Creates the hash table from one input.

2. 2
   Probe

   Consults that structure with rows from the other input.

1. 1
   Hash join

   The relevant operator is Hash Match (Inner Join). The Hash Match (Aggregate) to its left consumes the result for grouping and serves a different purpose.

2. 2
   One visible input

   The Clustered Index Scan reads one temporary table. Confirm each input's build or probe role in the properties; its position in the drawing is not enough to establish that role safely.

Query that produces the plan

This query processes the large sets without relying on an existing order. INNER HASH JOIN isolates the build and probe work of the hash table.

SELECT
    c.Segmento,
    COUNT_BIG(*) AS Pedidos,
    SUM(p.Total) AS TotalAcumulado
FROM #ClienteJoin AS c
INNER HASH JOIN #PedidoJoin AS p
    ON p.IdCliente = c.IdCliente
GROUP BY c.Segmento
OPTION (RECOMPILE);

This strategy does not require sorted inputs and can relate large sets through equality. It does require memory to build the hash table. If the grant is insufficient, part of the work can move to tempdb; that condition is verified through actual-plan properties and warnings, not from the name Hash Match alone.

The properties include Hash Keys Build and Hash Keys Probe. They identify the columns used to build and probe the hash table. Memory Grant, actual rows, and any spill warning complete the reading.

Merge Join

Merge Join receives both inputs sorted by their join keys. It compares the current values and advances through the input containing the lower value; when values match, it produces the corresponding rows.

1. 1
   First sorted input

   Advances through the join key without returning to the beginning.

2. 2
   Second sorted input

   Advances alongside it and supplies the matches.

1. 1
   Ordered join

   Merge Join (Inner Join) compares the keys supplied by both branches.

2. 2
   First input

   The Clustered Index Scan traverses #ClienteJoin in clustered-index order.

3. 3
   Second input

   The other Clustered Index Scan traverses #PedidoJoin with the same join key available in order.

Query that produces the plan

The clustered indexes supply both inputs ordered by IdCliente. INNER MERGE JOIN makes it possible to verify how the operator uses that order without adding a Sort.

SELECT
    c.IdCliente,
    c.Segmento,
    COUNT_BIG(*) AS Pedidos,
    SUM(p.Total) AS TotalAcumulado
FROM #ClienteJoin AS c
INNER MERGE JOIN #PedidoJoin AS p
    ON p.IdCliente = c.IdCliente
GROUP BY
    c.IdCliente,
    c.Segmento
OPTION (RECOMPILE);

The order may come from an index or a Sort operator. When it already exists, Merge Join can traverse large inputs continuously. When it must be produced solely for the join, the sorting cost and memory are also part of the strategy.

The Merge Columns properties show the keys used. The tree reveals whether the branches preserved useful order or a Sort appeared. A many-to-many merge can also require a worktable to handle duplicate values.

What to inspect in the plan

Then compare estimated and actual rows. An inaccurate estimate can make a strategy look attractive during compilation even though it accumulates more work with the observed rows.

When the three checks are complete, remove the temporary tables or close the query window:

DROP TABLE IF EXISTS #PedidoJoin;
DROP TABLE IF EXISTS #ClienteJoin;

What this still does not establish

The operator alone does not prove that the query is optimized. Nested Loops can suit a few lookups and become costly when repeated; Hash Match can process large inputs in memory or spill; Merge Join can exploit ordered indexes or depend on additional sorts.

It is also unsafe to force an algorithm because it worked once. Volume, distribution, parameters, indexes, memory, and concurrency can change the lowest-cost alternative.

Related topics

• Operator map
• Estimated rows and actual rows
• Sort, Segment, and Sequence Project