IN Lists

No Match and No NULL

No match with no NULL in the list gives FALSE.

highlighted = computed this step

No match and no NULL in list

If the value is not present and the list has no NULL, IN is FALSE.

no match no NULL\text{no match no NULL}

Read FALSE rows

The compiler records 1 FALSE row.

FALSE rows=1\text{FALSE 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.

No match and no NULL in list: 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

Green is not listed

Green is not red or blue, and the list has no NULL to make the answer uncertain.

not listed means FALSE\text{not listed means FALSE}

Summary

FALSE is only the clear no-match case.

clear no match\text{clear no match}