Outer Joins

Inner Match Pairs

The matching pairs are found before padding.

highlighted = computed this step

Inner match pairs

First find the matching pairs: a left key equals a right key.

match pairs first\text{match pairs first}

Read match pairs

The compiler finds 3 inner match pairs.

match pairs=3\text{match pairs}=3

Outer-join examples are tiny finite-table transforms; SQL dialect completeness, type coercion, optimizer behavior, execution cost, indexing, and database-product claims are out of scope.

Inner match pairs: left tablesidname1Ada2Bea3CalNULLNoKey Inner match pairs: right tablesidscore1A1A23CNULLGhost match pairsleftSourceleftKeyrightSourcesmatchCount01[0, 1]212[]023[2]13NULL[]0 left outer join rowsleft.sidleft.nameright.sidright.score1Ada1A1Ada1A22BeaNULLNULL3Cal3CNULLNoKeyNULLNULL post-join filter rowsjoinSourcekeptvalues0yes[1, Ada, 1, A]1yes[1, Ada, 1, A2]2yes[2, Bea, NULL, NULL]3yes[3, Cal, 3, C]4yes[NULL, NoKey, NULL, NULL] outer join factsfactvalueleftRowCount4rightRowCount4matchPairCount3unmatchedLeftRows2paddedRows2joinRowCount5filteredRowCount5whereAppliednonullKeyLeftRows1nullKeyMatches0rowOrderleft_source_then_right_source

Use source order

Pairs are listed by left source row, then right source row.

left source then right source\text{left source then right source}

Summary

The inner matches are the rows an inner join would keep.

inner part\text{inner part}