The simplest way to join two sets is to look at every combination, and the refinements of that idea account for a large share of the plans a transactional database runs and almost all of the ones that go catastrophically wrong.

What the algorithm does

For each row of one input it examines the other input and emits the combinations that satisfy the condition, which is a loop inside a loop.

Starting with the plain form is right because every variant is a refinement of it. The outer input is read once and the inner input is consulted once per outer row.

Why the naive form is almost never used

Reading the entire inner input for every outer row means the work is the product of the two sizes, which is unacceptable beyond very small inputs.

Dismissing the naive form early avoids confusion. When a plan shows this join it is nearly always one of the refined forms, and the distinction is where the cost lives.

What the indexed form changes

If an index on the inner input's join column exists, each consultation becomes a lookup rather than a scan, so the cost per outer row is small and bounded.

Describing the indexed variant is the centre of the section. This is the form that appears constantly in transactional plans, and it is genuinely excellent when the outer input is small.

Why the indexed form is so good for small outer inputs

A few hundred lookups against an index is trivial work, and no memory is needed, so the join starts producing rows immediately and finishes quickly.

Praising it properly is necessary for balance. It is the right choice far more often than it is the wrong one, which is why engines reach for it readily.

Why it is also the catastrophic failure mode

The per-row cost is small, so the plan's total cost depends almost entirely on the number of outer rows, which is an estimate that can be wrong by orders of magnitude.

Connecting the strength to the weakness is the key insight. Nothing about the algorithm is wrong, and its cost is linear in a number the planner guessed.

Why that failure is so common

Underestimates are the systematic direction of estimation error, and an underestimated outer input is exactly what makes this join look cheapest.

Explaining the frequency closes the loop with the previous lesson. The estimation bias and this algorithm's cost profile combine to produce most catastrophic plans.

What the blocked form changes

Rather than one outer row at a time, a batch of outer rows is collected and the inner input is consulted once for the whole batch.

Introducing batching matters because it changes the arithmetic. The inner input is read once per batch rather than once per row, which divides the inner cost by the batch size.

Why batching helps most without an index

With no index the inner input must be scanned, so reading it once per batch of a thousand rows rather than once per row is a thousandfold reduction.

Stating where the benefit lands is useful. Batching is a mitigation for the unindexed case, and its effect on the indexed case is smaller and different.

How batching helps the indexed case

The lookups for a batch can be sorted into index order, which turns scattered page reads into something closer to sequential access.

Covering this is worthwhile because it is a real and underappreciated optimisation. Sorting the lookups is why some engines handle large outer inputs better than the naive analysis suggests.

Why the inner input must be re-readable

Consulting it repeatedly requires it to be either an index, a table, or something materialised, which constrains what can sit on the inner side.

Noting the constraint explains a plan shape. An inner side that is itself a complex subplan often gets materialised first, precisely so it can be read repeatedly.

Why materialising the inner side is sometimes a trap

The materialised result must fit somewhere, and if it is large the materialisation is a substantial cost paid before any output appears.

Marking the hazard is fair. The optimisation that makes repeated reading possible can itself be the expensive part of the plan.

Why the outer side is the one to make small

Every cost in the join is proportional to the outer input, so reducing it by applying a filter before the join rather than after is worth far more than anything done to the inner side.

Stating the lever explicitly is useful because the instinct is to index the inner side. Indexing the inner side bounds the per-row cost, and shrinking the outer side reduces how many times that cost is paid.

Why the memory profile is attractive

Nothing needs to be held, so the join uses almost no memory regardless of input sizes, which is a real advantage under memory pressure.

Including the resource picture matters because it is a genuine differentiator. The alternatives need memory proportional to an input, and this one does not.

Why it produces rows immediately

The first outer row can be processed and emitted before anything else is read, so the startup cost is nearly zero.

Connecting to startup cost links back to the previous lesson. This is why a query with a small limit so often gets this join, and why it is the right choice there.

How a semi-join uses it well

When the question is only whether a match exists, the inner consultation can stop at the first match, which makes each one cheaper than a full lookup.

Covering early exit is worthwhile because the optimisation is substantial. An existence test against a value with many matches does a fraction of the work a full join would.

How an anti-join uses it

The inner consultation stops at the first match too, but the outer row is emitted when no match is found, so the cost profile is the same with the opposite output.

Including the complement is natural and cheap. Both forms benefit from early exit, and both appear constantly in queries written with existence conditions.

Why the algorithm handles any condition

The inner consultation can test anything, so a condition that is not an equality works, which the alternatives cannot always say.

Pointing out the generality is important because it is sometimes the deciding factor. A join on an inequality or on a function has this algorithm as its only option in many engines.

Why that generality makes it the fallback

When no other algorithm applies, this one is chosen regardless of size, which means a large join on a non-equality condition can be enormously expensive with no alternative.

Stating the consequence is practical. A plan showing this join over two large inputs with an unusual condition is not a planner mistake, it is the only plan available.

What to take from nested loops

Three things. The indexed form makes each inner consultation a cheap bounded lookup and uses almost no memory and produces rows immediately, which is why it is correct far more often than not. Its total cost is almost entirely the number of outer rows, which is an estimate, and since estimation error is systematically low, an underestimated outer input choosing this join accounts for most catastrophic plans. And it is the only algorithm that handles any condition, which makes it the fallback for non-equality joins at any size.