NOT IN and NULLs

Input Rows

NOT IN starts from visible input rows.

highlighted = computed this step

Input rows

Start with visible rows. Some tested color cells are present, and one is NULL.

input rows first\text{input rows first}

Read input rows

The pinned table has 5 input rows.

rows=5\text{rows}=5

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.

Input rows: input rowsidcolor1red2blue3green4NULL5yellow Input rows: candidate literal_listsourcevalue0red1NULL NOT IN evaluationsourcevaluecandidatesmodeinTruthtruthkeptreason0red[red, NULL]not_inTRUEFALSEnomatched candidate1blue[red, NULL]not_inUNKNOWNUNKNOWNnono match and list has NULL2green[red, NULL]not_inUNKNOWNUNKNOWNnono match and list has NULL3NULL[red, NULL]not_inUNKNOWNUNKNOWNnotested NULL4yellow[red, NULL]not_inUNKNOWNUNKNOWNnono match and list has NULL WHERE output rowsidcolornonenone NOT IN factsfactvalueinputRowCount5candidateCount2candidateHasNullyesoutputRowCount0trueRows0falseRows1unknownRows4testedNullRows1nullListNoMatchRows3modenot_inrowOrdersource_order

WHERE keeps TRUE only

The output table later keeps only rows whose final truth is TRUE.

TRUE only\text{TRUE only}

Summary

The NULL behavior starts from the displayed cells, not prose.

visible cells\text{visible cells}