Subqueries

NOT IN and NULL

NOT IN with NULL is rejected visibly in this scoped model.

highlighted = computed this step

NOT IN and NULL

NOT IN with a NULL inside the inner result is a caveat, so this toy model rejects the result visibly.

NULL caveat is explicit\text{NULL caveat is explicit}

Fail closed

The request is rejected, and result rows are 0.

result rows=0\text{result rows}=0

Subquery examples are tiny finite table evaluations; SQL dialects, optimizer rewrites, NULL truth tables beyond the explicit NOT IN caveat, performance, and product behavior are out of scope.

NOT IN and NULL: outer tablesourceRowidnamecity_id01Ada1012Ben2023Cy30 NOT IN and NULL: inner tablesourceRowcity_id0201NULL per-row subquery evaluationsourceRowouterValuestatusreasoninnerRows010rejectnull_in_not_in[]120rejectnull_in_not_in[]230rejectnull_in_not_in[] result rowssourceRowidnamecity_idnonenonenonenone subquery factsfactvaluemodenot_inouterRowCount3innerRowCount2resultRowCount0rejectedyesrejectionReasonnull_in_not_innullInInneryesrowOrdersource

Read the reason

Each outer row points to null_in_not_in instead of pretending the answer is simple.

reason is visible\text{reason is visible}

Summary

The lesson teaches the caveat without expanding into full three-valued SQL logic.

scoped NULL handling\text{scoped NULL handling}