Outer Joins

NULL Padding

Unmatched right columns are padded with NULL.

highlighted = computed this step

NULL padding

When a left row has no right match, the right-side columns are padded with NULL.

right side padded\text{right side padded}

Read padded rows

The compiler pads 2 rows.

padded rows=2\text{padded rows}=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.

NULL padding: left tablesidname1Ada2Bea3CalNULLNoKey NULL padding: 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

Find the NULL cells

The output table shows NULL in the right-side columns for unmatched left rows.

NULL padding visible\text{NULL padding visible}

Summary

Padding is how the left row stays present when the right side is absent.

left row plus NULLs\text{left row plus NULLs}