NOT IN and NULLs

Honest by Construction

Expected NOT IN facts are assertions only.

highlighted = computed this step

Honest by construction

Expected NOT IN facts are assertions only.

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

Render checked truth

The guarded NOT IN example recomputes 4 UNKNOWN rows.

checked UNKNOWN rows=4\text{checked UNKNOWN rows}=4

NOT IN examples are tiny finite WHERE-style filters with SQL-style TRUE/FALSE/UNKNOWN behavior; correlation, optimizer behavior, execution cost, indexing, SQL dialect completeness, and database-product claims are out of scope.

Honest by construction: input rowsidcolor1red2blue3green4NULL5yellow Honest by construction: candidate literal_listsourcevalue0red1NULL NOT IN evaluationsourcevaluecandidatesmodeinTruthtruthkeptreason0red[red, NULL]not_inTRUEFALSEnomatched candidate1blue[red, NULL]not_inUNKNOWNUNKNOWNnono match and list has NULL2green[red, NULL]not_inUNKNOWNUNKNOWNnono match and list has NULL3NULL[red, NULL]not_inUNKNOWNUNKNOWNnotested NULL4yellow[red, NULL]not_inUNKNOWNUNKNOWNnono match and list has NULL WHERE output rowsidcolornonenone NOT IN factsfactvalueinputRowCount5candidateCount2candidateHasNullyesoutputRowCount0trueRows0falseRows1unknownRows4testedNullRows1nullListNoMatchRows3modenot_inrowOrdersource_order

Try to tamper

Wrong expected facts, derived sidecars, missing columns, empty lists, bad modes, bools, floats, and unsafe cells fail closed.

tamper rejected\text{tamper rejected}

Summary

This book shows exact finite NOT IN, NULL, and NOT EXISTS contrast behavior only.

scoped NOT IN model\text{scoped NOT IN model}