IN Lists

Value in the List

A matching non-NULL list value gives TRUE.

highlighted = computed this step

Value in the list

If a non-NULL value equals a non-NULL list item, IN is TRUE.

exact member match\text{exact member match}

Read TRUE rows

The literal list produces 3 TRUE rows.

TRUE rows=3\text{TRUE rows}=3

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.

Value in the 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

Duplicates stay as rows

Both red rows match because each source row is tested separately.

row by row\text{row by row}

Summary

IN is TRUE for rows whose value is present in the list.

present means TRUE\text{present means TRUE}