Missing Matches

Honest by Construction

Expected missing-match facts are assertions only.

highlighted = computed this step

Honest by construction

Expected missing-match facts are assertions only.

compiler is source of truth\text{compiler is source of truth}

Render checked rows

The guarded anti-match filter renders 3 rows after recomputation.

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

Honest by construction: left tablepidproject1alpha2beta3gamma4deltaNULLnokey Honest by construction: 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

Try to tamper

Wrong expected facts, derived sidecars, missing keys, missing filter columns, invalid IS NULL payloads, bools, and floats fail closed.

tamper rejected\text{tamper rejected}

Summary

This book shows exact finite LEFT JOIN plus right-side IS NULL filtering only.

scoped missing match model\text{scoped missing match model}