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 joinnested loop joinrows 3scan Studentsrows 3scan Enrollrows 3 resultsidnamecourses1AnnDBs1AnnOSs2BoDB

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}