Outer Joins

Keep Unmatched Left Rows

Left outer joins preserve unmatched left rows.

highlighted = computed this step

Keep unmatched left rows

A left outer join keeps left rows even when no right row matches.

left rows survive\text{left rows survive}

Read unmatched rows

There are 2 unmatched left rows, but the output still has 5 rows.

unmatched left=2\text{unmatched left}=2

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.

Keep unmatched left rows: left tablesidname1Ada2Bea3CalNULLNoKey Keep unmatched left rows: 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

Read keptByOuter

The match table marks each left row as kept by the outer join.

kept by outer join\text{kept by outer join}

Summary

Left outer means the left side is preserved.

left preserved\text{left preserved}