NOT IN and NULLs

NOT EXISTS Contrast

NOT EXISTS is shown as a scoped finite contrast.

highlighted = computed this step

NOT EXISTS contrast

A NOT EXISTS-style check asks whether a non-NULL match exists; the right-side NULL does not poison unrelated non-matches here.

NOT EXISTS contrast\text{NOT EXISTS contrast}

Compare outputs

NOT IN outputs 0 rows, while the NOT EXISTS contrast outputs 4 rows.

NOT EXISTS rows=4\text{NOT EXISTS 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.

NOT IN with NULL: input rowsidcolor1red2blue3green4NULL5yellow NOT IN with NULL: 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
NOT EXISTS contrast: input rowsidcolor1red2blue3green4NULL5yellow NOT EXISTS contrast: candidate literal_listsourcevalue0red1NULL NOT IN evaluationsourcevaluecandidatesmodeinTruthtruthkeptreason0red[red, NULL]not_existsTRUEFALSEnomatched candidate1blue[red, NULL]not_existsUNKNOWNTRUEyesno match and list has NULL2green[red, NULL]not_existsUNKNOWNTRUEyesno match and list has NULL3NULL[red, NULL]not_existsUNKNOWNTRUEyestested NULL4yellow[red, NULL]not_existsUNKNOWNTRUEyesno match and list has NULL WHERE output rowsidcolor2blue3green4NULL5yellow NOT IN factsfactvalueinputRowCount5candidateCount2candidateHasNullyesoutputRowCount4trueRows4falseRows1unknownRows0testedNullRows1nullListNoMatchRows3modenot_existsrowOrdersource_order

Contrast is scoped

This is a toy finite contrast, not a product rewrite rule.

scoped contrast\text{scoped contrast}

Summary

The contrast makes the NULL-list boundary visible.

boundary visible\text{boundary visible}