LEFT JOIN Filters

Unmatched Left Gets NULL Padding

Unmatched left rows remain with NULL right cells.

highlighted = computed this step

Unmatched left rows get NULLs

When there is no right match, the left row stays and right-side columns become NULL.

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

Read padded rows

The compiler creates 3 NULL-padded rows.

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

Unmatched left rows get NULLs: left tablecidname1Ada2Bea3Cal4DeeNULLNoKey Unmatched left rows get NULLs: right tablecidorder_idstate1101paid1102open3103paid5104paidNULL105ghost match pairsleftSourceleftKeyrightSourcesmatchCount01[0, 1]212[]023[2]134[]04NULL[]0 left outer join rowsleft.cidleft.nameright.cidright.order_idright.state1Ada1101paid1Ada1102open2BeaNULLNULLNULL3Cal3103paid4DeeNULLNULLNULLNULLNoKeyNULLNULLNULL post-join filter rowsjoinSourcekeptvalues0yes[1, Ada, 1, 101, paid]1yes[1, Ada, 1, 102, open]2yes[2, Bea, NULL, NULL, NULL]3yes[3, Cal, 3, 103, paid]4yes[4, Dee, NULL, NULL, NULL]5yes[NULL, NoKey, NULL, NULL, NULL] outer join factsfactvalueleftRowCount5rightRowCount5matchPairCount3unmatchedLeftRows3paddedRows3joinRowCount6filteredRowCount6whereAppliednonullKeyLeftRows1nullKeyMatches0rowOrderleft_source_then_right_source

Padding is visible

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

visible padding\text{visible padding}

Summary

This is the left-preserving part of LEFT JOIN.

left side preserved\text{left side preserved}