LEFT JOIN Filters

Honest by Construction

Expected LEFT JOIN filter facts are assertions only.

highlighted = computed this step

Honest by construction

Expected LEFT JOIN filter facts are assertions only.

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

Render checked rows

The guarded filter renders 2 rows after recomputation.

checked rows=2\text{checked rows}=2

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 tablecidname1Ada2Bea3Cal4DeeNULLNoKey Honest by construction: right tablecidorder_idstate1101paid1102open3103paid5104paidNULL105ghost match pairsleftSourceleftKeyrightSourcesmatchCount01[0, 1]212[]023[2]134[]04NULL[]0 left outer join rowsleft.cidleft.nameright.cidright.order_idright.state1Ada1101paid1Ada1102open2BeaNULLNULLNULL3Cal3103paid4DeeNULLNULLNULLNULLNoKeyNULLNULLNULL post-join filter rowsjoinSourcekeptvalues0yes[1, Ada, 1, 101, paid]1no[1, Ada, 1, 102, open]2no[2, Bea, NULL, NULL, NULL]3yes[3, Cal, 3, 103, paid]4no[4, Dee, NULL, NULL, NULL]5no[NULL, NoKey, NULL, NULL, NULL] outer join factsfactvalueleftRowCount5rightRowCount5matchPairCount3unmatchedLeftRows3paddedRows3joinRowCount6filteredRowCount2whereAppliedyesnullKeyLeftRows1nullKeyMatches0rowOrderleft_source_then_right_source

Try to tamper

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

tamper rejected\text{tamper rejected}

Summary

This book shows exact finite LEFT JOIN rows plus right-side post-join filters only.

scoped LEFT JOIN filters\text{scoped LEFT JOIN filters}