3.4NULL and three-valued logic
The idea
NULL means unknown. Any comparison with NULL is UNKNOWN, and WHERE keeps only rows that are TRUE.
Think of it like this: Asking "is this unknown number equal to 5?": you cannot say yes or no.
Why it exists
NULL traps (NOT IN, COUNT, comparisons) appear in many one-mark questions.
How it works
- a = NULL is UNKNOWN even when a is NULL; use IS NULL.
- AND: F wins over U; OR: T wins over U; NOT U = U.
- NOT IN with a NULL in the set can never be TRUE, so no row is returned.
- Aggregates skip NULLs; GROUP BY puts all NULLs in one group.
Worked example: R(a) = {1, 2, NULL}; WHERE a <> 2
Only row 1 is TRUE; the NULL row is UNKNOWN and is dropped.
Now play with it
- →Run each predicate in the lab and look at the UNKNOWN row.
- →Compare IN with NOT IN against {2, NULL}.
| a | a = 2 | row returned? |
|---|---|---|
| 1 | FALSE | no |
| 2 | TRUE | yes |
| NULL | UNKNOWN | no |
| T | U | F | |
|---|---|---|---|
| T | T | U | F |
| U | U | U | F |
| F | F | F | F |
| T | U | F | |
|---|---|---|---|
| T | T | T | T |
| U | T | U | U |
| F | T | U | F |
| T | F |
|---|---|
| U | U |
| F | T |
T = TRUE, U = UNKNOWN, F = FALSE. WHERE keeps only rows that evaluate to TRUE. Any comparison with NULL is UNKNOWN, so NOT IN against a set containing NULL returns nothing.
Check yourself
SELECT … WHERE a NOT IN (SELECT b FROM S) where S contains a NULL returns
Exam notes (the dense version)
Any comparison with NULL is UNKNOWN, not true or false. WHERE keeps only rows that are TRUE. NULL = NULL is UNKNOWN; test with IS NULL. AND/OR/NOT follow three-valued tables (F AND U = F, T OR U = T, NOT U = U). x NOT IN (set containing NULL) is never TRUE, so it returns no rows. Grouping treats all NULLs as one group.
Previous year questions: SQL (13)
PYQs are grouped by topic, so this is the full set for SQL; try the ones that match this subtopic. Open all 13 on the topic page.