NOT IN and NULLs

NULL in List Makes UNKNOWN

A NULL candidate makes non-matches UNKNOWN.

highlighted = computed this step

NULL in list changes non-matches

If the value does not match any non-NULL candidate and the list contains NULL, NOT IN becomes UNKNOWN.

no match plus NULL gives UNKNOWN\text{no match plus NULL gives UNKNOWN}

Read UNKNOWN rows

The NULL-list run has 4 UNKNOWN rows, including 3 non-matches made UNKNOWN by the list NULL.

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

NULL in list makes non-matches UNKNOWN: input rowsidcolor1red2blue3green4NULL5yellow NULL in list makes non-matches UNKNOWN: 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

Non-match is not enough

Blue, green, and yellow do not match red, but the NULL candidate prevents TRUE.

not TRUE\text{not TRUE}

Summary

This is the common NOT IN and NULL surprise.

NULL poisons nonmatch\text{NULL poisons nonmatch}