Join Types and What They Tell You

INNER JOIN is what you write. Nested Loops, Hash Match and Merge Join are what the engine does. The optimizer picks among the three based on estimated row counts — so the join type it picked is a readout of what it believed about your data, which makes it a diagnostic.

Nested Loops

For each row on the outer (top) input, probe the inner (bottom) input. Cost is roughly outer_rows × cost_of_one_inner_probe.

Implies: the optimizer expected the outer input to be small, and expects an efficient index seek on the inner side.

Good when the outer side really is small — a few hundred rows joining to a well-indexed table.

Bad when the outer estimate was wrong. This is the failure mode from Lesson 2: estimated 1 row, actual 940,000, and the plan performs 940,000 index probes one at a time. The plan still looks cheap in cost percentages. It runs for a minute.

What to check: Number of Executions on the inner operator. That is the real multiplier.

Hash Match

Build a hash table from the smaller (build) input in memory, then stream the larger (probe) input through it.

Implies: the optimizer expected large inputs, or there was no useful index for a seek.

Good when you are joining big sets — which is normal and correct for reporting. A Hash Match in a report plan is not a defect.

Watch for: a spill warning. If the memory grant is too small for the build side, the hash table spills to tempdb and performance collapses. That is a memory-grant problem caused by a bad row estimate, not a join-choice problem (next lesson).

A Hash Match where you expected a seek is also a hint that a join column is missing an index, or that the join has an implicit conversion in it (Module 4).

Merge Join

Both inputs arrive sorted on the join key; walk them together in one pass. Very efficient.

Implies: both inputs were already sorted (usually via indexes whose keys align with the join column) or the optimizer decided sorting was worth it.

Watch for: an explicit Sort operator feeding the merge. Sorting a large input to enable a merge join is often more expensive than a hash join would have been, and it needs a memory grant that can spill.

Reading it as a diagnosis

The useful mental shortcut:

• Nested Loops on big data → the optimizer thought the data was small. Investigate the estimate.
• Hash Match on small data → the optimizer thought the data was big, or no index supported a seek.
• Merge with a big Sort in front → the sort may be costing more than the join saves.

You are not usually trying to force a different join type. You are using the join type as evidence about what the optimizer believed, then fixing the belief.

Seeing all three

-- Run against any database; sys.all_objects gives us real data to join.
SET STATISTICS IO, TIME ON;

-- Small outer set: expect Nested Loops
SELECT o.name, c.name
FROM   sys.all_objects  AS o
JOIN   sys.all_columns  AS c ON c.object_id = o.object_id
WHERE  o.object_id = OBJECT_ID('sys.objects');

-- Large sets: expect Hash Match
SELECT COUNT_BIG(*)
FROM   sys.all_objects AS o
JOIN   sys.all_columns AS c ON c.object_id = o.object_id;

SET STATISTICS IO, TIME OFF;

Turn on the actual plan (Ctrl+M) before running and compare the two.

Hints exist; use them last

You can force a join type:

SELECT o.OrderId, c.CustomerName
FROM   dbo.Orders   AS o                    -- placeholder tables
JOIN   dbo.Customer AS c ON c.CustomerId = o.CustomerId
OPTION (HASH JOIN);

Two things to know before you do. First, a join hint at the query level also forces the join order as written, which constrains the optimizer far more than people expect. Second, it locks in today's answer against tomorrow's data volumes. Reach for a join hint only to prove a hypothesis during diagnosis, or as a temporary stabilizer while you fix the estimate. If you ship one, leave a comment saying which estimate it is compensating for, so the next person can remove it when that is fixed.

The short version

The join operator tells you what the optimizer believed. Nested Loops over a large outer input means it expected a small one, so look at why the estimate was off rather than forcing a hash join. Hash Match is normal and fine in reporting — what to check there is whether it spilled to tempdb, because that is a memory grant problem from a bad estimate.