IN Lists

List NULL No Match

A no-match list containing NULL gives UNKNOWN.

highlighted = computed this step

No match but list contains NULL

If nothing matches but the list contains NULL, IN is UNKNOWN.

NULL in list can make UNKNOWN\text{NULL in list can make UNKNOWN}

Read uncertain no-match rows

The NULL-list case makes 2 nonmatching rows UNKNOWN.

NULL-list no-match rows=2\text{NULL-list no-match rows}=2

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.

No match but list contains NULL: base rowsidcolor1red2blue3green4NULL5red IN-list evaluationsourcevaluevaluesmodeinTruthtruthkeptreason0red[red, NULL]inTRUETRUEyesmatched literal1blue[red, NULL]inUNKNOWNUNKNOWNnono match and list has NULL2green[red, NULL]inUNKNOWNUNKNOWNnono match and list has NULL3NULL[red, NULL]inUNKNOWNUNKNOWNnotested NULL4red[red, NULL]inTRUETRUEyesmatched literal IN-list output rowsidcolor1red5red IN-list factsfactvalueinputRowCount5listLength2outputRowCount2trueRows2falseRows0unknownRows3testedNullRows1nullListNoMatchRows2modeinrowOrdersource_order

Not a clear no

Blue and green do not match red, but the NULL list item prevents a clear FALSE answer.

uncertain no match\text{uncertain no match}

Summary

A NULL inside the list can turn no-match into UNKNOWN.

list NULL matters\text{list NULL matters}