IN Lists

Tested Value Is NULL

A NULL tested value gives UNKNOWN.

highlighted = computed this step

Tested value is NULL

When the row value is NULL, IN gives UNKNOWN.

NULL tested value\text{NULL tested value}

Read tested-NULL rows

The compiler records 1 tested-NULL row.

tested NULL rows=1\text{tested NULL rows}=1

IN-list examples are tiny finite WHERE-style filters with SQL-style TRUE/FALSE/UNKNOWN behavior; subqueries, type coercion, optimizer behavior, indexes, dialect details, and product claims are out of scope.

Tested value is NULL: base rowsidcolor1red2blue3green4NULL5red IN-list evaluationsourcevaluevaluesmodeinTruthtruthkeptreason0red[red, blue]inTRUETRUEyesmatched literal1blue[red, blue]inTRUETRUEyesmatched literal2green[red, blue]inFALSEFALSEnono match3NULL[red, blue]inUNKNOWNUNKNOWNnotested NULL4red[red, blue]inTRUETRUEyesmatched literal IN-list output rowsidcolor1red2blue5red IN-list factsfactvalueinputRowCount5listLength2outputRowCount3trueRows3falseRows1unknownRows1testedNullRows1nullListNoMatchRows0modeinrowOrdersource_order

Not TRUE

UNKNOWN is not TRUE, so this WHERE-style filter does not keep that row.

UNKNOWN not kept\text{UNKNOWN not kept}

Summary

NULL as the tested value makes the membership answer unknown.

NULL gives UNKNOWN\text{NULL gives UNKNOWN}