Missing Matches

Left Rows to Check

Missing-match queries start from preserved left rows.

highlighted = computed this step

Left rows to check

The left table is the list we want to keep visible while we look for missing matches.

start with left rows\text{start with left rows}

Read left rows

The project table has 5 left rows to check.

left rows=5\text{left rows}=5

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 rows to check: left tablepidproject1alpha2beta3gamma4deltaNULLnokey Left rows to check: 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

Keep the question concrete

Each project either finds right-side task rows or gets NULL padding.

match or padding\text{match or padding}

Summary

Missing-match queries begin by preserving the left side.

left side preserved\text{left side preserved}