SQL NULL and three-valued logic: the NOT IN trap
Why comparisons with SQL NULL return UNKNOWN in three-valued logic, why WHERE drops them, and why NOT IN with one NULL returns no rows.
Part of Relational queries.
Read first
- Relational algebra: a query as a tree of operators
Relational algebra explained: relations as sets of rows, the operators σ, π, ×, ⋈, ∪, − and ∩, and how every SQL query becomes a tree of them, a query plan.
Read next
- Predicate pushdown: filter before you join
Predicate pushdown in a query optimizer: split the WHERE into CNF clauses and push selections and projections below joins without changing the answer.
Related
- For all queries in SQL: double NOT EXISTS
How to write "for all" and "only" queries in SQL and relational algebra: ∀ = ¬∃¬, the double NOT EXISTS, and everyone minus the counterexamples.