NOT IN and NULLs

TRUE FALSE UNKNOWN

NOT IN produces SQL three-valued truth.

highlighted = computed this step

TRUE FALSE UNKNOWN

With a clean candidate list, NOT IN can return TRUE, FALSE, or UNKNOWN.

three truth values\text{three truth values}

Read truth counts

This clean-list run has 2 TRUE rows, 2 FALSE rows, and 1 UNKNOWN row.

TRUE rows=2\text{TRUE rows}=2

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.

TRUE FALSE UNKNOWN: input rowsidcolor1red2blue3green4NULL5yellow TRUE FALSE UNKNOWN: candidate literal_listsourcevalue0red1blue NOT IN evaluationsourcevaluecandidatesmodeinTruthtruthkeptreason0red[red, blue]not_inTRUEFALSEnomatched candidate1blue[red, blue]not_inTRUEFALSEnomatched candidate2green[red, blue]not_inFALSETRUEyesno match3NULL[red, blue]not_inUNKNOWNUNKNOWNnotested NULL4yellow[red, blue]not_inFALSETRUEyesno match WHERE output rowsidcolor3green5yellow NOT IN factsfactvalueinputRowCount5candidateCount2candidateHasNullnooutputRowCount2trueRows2falseRows2unknownRows1testedNullRows1nullListNoMatchRows0modenot_inrowOrdersource_order

UNKNOWN is not TRUE

The tested NULL row is UNKNOWN and does not pass WHERE.

UNKNOWN drops\text{UNKNOWN drops}

Summary

NOT IN is still a WHERE-style TRUE-only filter.

WHERE TRUE only\text{WHERE TRUE only}