Outer Joins

Honest by Construction

Expected outer-join facts are assertions only.

highlighted = computed this step

Honest by construction

Expected outer-join facts are assertions only.

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

Render checked rows

The guarded join renders 5 joined rows after recomputation.

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

Honest by construction: left tablesidname1Ada2Bea3CalNULLNoKey Honest by construction: right tablesidscore1A1A23CNULLGhost match pairsleftSourceleftKeyrightSourcesmatchCount01[0, 1]212[]023[2]13NULL[]0 left outer join rowsleft.sidleft.nameright.sidright.score1Ada1A1Ada1A22BeaNULLNULL3Cal3CNULLNoKeyNULLNULL post-join filter rowsjoinSourcekeptvalues0yes[1, Ada, 1, A]1yes[1, Ada, 1, A2]2yes[2, Bea, NULL, NULL]3yes[3, Cal, 3, C]4yes[NULL, NoKey, NULL, NULL] outer join factsfactvalueleftRowCount4rightRowCount4matchPairCount3unmatchedLeftRows2paddedRows2joinRowCount5filteredRowCount5whereAppliednonullKeyLeftRows1nullKeyMatches0rowOrderleft_source_then_right_source

Try to tamper

Wrong expected facts, derived sidecars, missing keys, duplicate columns, bools, floats, and unsupported filters fail closed.

tamper rejected\text{tamper rejected}

Summary

The book shows exact finite outer-join behavior only; product SQL behavior is outside the model.

scoped outer join model\text{scoped outer join model}