Missing Matches

NULL Padding Marks No Match

No right match becomes visible NULL padding.

highlighted = computed this step

NULL padding marks no match

When a left row has no matching right row, the right-side cells are NULL.

NULL padded right side\text{NULL padded right side}

Read padded rows

The compiler creates 3 NULL-padded joined 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.

NULL padding marks no match: left tablepidproject1alpha2beta3gamma4deltaNULLnokey NULL padding marks no match: right tablepidtask1design1build3test5orphanNULLghost match pairsleftSourceleftKeyrightSourcesmatchCount01[0, 1]212[]023[2]134[]04NULL[]0 left outer join rowsleft.pidleft.projectright.pidright.task1alpha1design1alpha1build2betaNULLNULL3gamma3test4deltaNULLNULLNULLnokeyNULLNULL post-join filter rowsjoinSourcekeptvalues0yes[1, alpha, 1, design]1yes[1, alpha, 1, build]2yes[2, beta, NULL, NULL]3yes[3, gamma, 3, test]4yes[4, delta, NULL, NULL]5yes[NULL, nokey, NULL, NULL] outer join factsfactvalueleftRowCount5rightRowCount5matchPairCount3unmatchedLeftRows3paddedRows3joinRowCount6filteredRowCount6whereAppliednonullKeyLeftRows1nullKeyMatches0rowOrderleft_source_then_right_source

The marker is visible

Those NULL right-side cells are what the next filter will test.

visible missing marker\text{visible missing marker}

Summary

Padding turns no right match into a visible row fact.

no match becomes NULLs\text{no match becomes NULLs}