NOT IN and NULLs

Candidate List

The right-side list is visible.

highlighted = computed this step

Candidate list

NOT IN compares each input value with a candidate list.

candidate list\text{candidate list}

Read candidates

The candidate list has 2 values, and candidateHasNull is yes.

candidate has NULL\text{candidate has NULL}

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.

Candidate list: input rowsidcolor1red2blue3green4NULL5yellow Candidate list: 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

NULL candidate is visible

The NULL candidate is shown as a list row before any truth value is trusted.

visible NULL candidate\text{visible NULL candidate}

Summary

A NULL in the list is the sharp edge for later non-matches.

NULL list edge\text{NULL list edge}