Plan Trees

Nested-loop Join

Nested-loop join keeps matching row pairs.

highlighted = computed this step

Nested-loop join checks pairs

A nested-loop join considers pairs from its two inputs and keeps matching join keys. This is a physical mechanism for producing the same relational join result. Note: the kept rows appear in the render.

nested loop join\text{nested loop join}

Join result

The recomputed join row count is 3 and arity is 3. Note: nonmatching rows do not appear in the result table.

rows=3,arity=3\text{rows}=3,\quad \text{arity}=3

cardinalities are exact counts on these tiny tables; choosing a plan by cost needs statistics and is deferred - no speed/perf claims; nested-loop vs hash is mechanism, not speed.

Nested-loop join nested loop join rows 3 scan Students rows 3 scan Enroll rows 3 result sid name course s1 Ann DB s1 Ann OS s2 Bo DB

Summary

Nested-loop join is one mechanism for matching rows across inputs. Note: choosing by cost needs statistics and is deferred.

join mechanism\text{join mechanism}