Missing Matches

Right-Side IS NULL Keeps Missing

A right-side IS NULL filter keeps padded rows.

highlighted = computed this step

Right-side key IS NULL

A post-join right-side key IS NULL test keeps the NULL-padded rows.

right key IS NULL\text{right key IS NULL}

Read kept missing rows

From 6 joined rows, the IS NULL filter keeps 3 missing-match rows.

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

Right-side IS NULL keeps missing rows: left tablepidproject1alpha2beta3gamma4deltaNULLnokey Right-side IS NULL keeps missing rows: right tablepidtask1design1build3test5orphanNULLghost match pairsleftSourceleftKeyrightSourcesmatchCount01[0, 1]212[]023[2]134[]04NULL[]0 left outer join rowsleft.pidleft.projectright.pidright.task1alpha1design1alpha1build2betaNULLNULL3gamma3test4deltaNULLNULLNULLnokeyNULLNULL post-join filter rowsjoinSourcekeptvalues0no[1, alpha, 1, design]1no[1, alpha, 1, build]2yes[2, beta, NULL, NULL]3no[3, gamma, 3, test]4yes[4, delta, NULL, NULL]5yes[NULL, nokey, NULL, NULL] outer join factsfactvalueleftRowCount5rightRowCount5matchPairCount3unmatchedLeftRows3paddedRows3joinRowCount6filteredRowCount3whereAppliedyesnullKeyLeftRows1nullKeyMatches0rowOrderleft_source_then_right_source

TRUE rows only

Rows where the right-side key is NULL are kept; rows with a matched right key are dropped.

keep padded rows\text{keep padded rows}

Summary

This is the finite anti-match pattern shown as table facts.

LEFT JOIN then IS NULL\text{LEFT JOIN then IS NULL}