NOT IN and NULLs

WHERE Keeps TRUE Only

WHERE drops FALSE and UNKNOWN.

highlighted = computed this step

WHERE keeps TRUE only

Rows with FALSE or UNKNOWN do not pass the WHERE filter.

TRUE only output\text{TRUE only output}

Read output rows

With the NULL candidate list, NOT IN outputs 0 rows.

output rows=0\text{output rows}=0

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.

WHERE keeps TRUE only: input rowsidcolor1red2blue3green4NULL5yellow WHERE keeps TRUE only: 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

Empty output is honest

The empty output comes from recomputed truth rows, not from a hand-written warning.

computed empty output\text{computed empty output}

Summary

A NULL candidate can turn every non-matching row into UNKNOWN.

no TRUE rows\text{no TRUE rows}