NOT IN and NULLs

Subquery Result List

A finite subquery result list has the same NULL edge.

highlighted = computed this step

Subquery result list

A finite subquery result list behaves like the visible candidate rows it returns.

subquery result list\text{subquery result list}

Read subquery candidates

The subquery result has 2 candidate rows and produces 4 UNKNOWN input rows.

subquery candidates=2\text{subquery candidates}=2

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.

Subquery result list: input rowsidcolor1red2blue3green4NULL5yellow Subquery result list: candidate subquery_resultsourcecolor0red1NULL 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

No hidden subquery work

The subquery is already a tiny pinned result list in this model.

finite result list\text{finite result list}

Summary

A NULL returned by the subquery has the same sharp edge.

subquery NULL visible\text{subquery NULL visible}