Missing Matches

LEFT JOIN Keeps Every Left Row

The joined table preserves left rows before filtering.

highlighted = computed this step

LEFT JOIN keeps every left row

Before the missing-match filter, the LEFT JOIN output still represents every left row.

LEFT JOIN output\text{LEFT JOIN output}

Read joined rows

5 left rows become 6 joined rows because one project has multiple task matches.

joined rows=6\text{joined rows}=6

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.

LEFT JOIN keeps every left row: left tablepidproject1alpha2beta3gamma4deltaNULLnokey LEFT JOIN keeps every left row: 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

Expanded but preserved

Repeated matches can expand row count, but unmatched left rows are still present.

preserved before filter\text{preserved before filter}

Summary

The missing-match filter will run after this joined table exists.

filter later\text{filter later}