SQL / NULL AND THREE-VALUED LOGIC
Why comparing with NULL never matches
Predict how =, <>, and IN behave when either side is NULL, and rewrite those comparisons with IS NULL or IS DISTINCT FROM so no rows go missing.
What you will learn
- Read x = NULL and x <> NULL as UNKNOWN, and remember that WHERE keeps only TRUE.
- Use IS NULL and IS NOT NULL, which return TRUE or FALSE but never UNKNOWN.
- Compare two nullable columns with IS DISTINCT FROM instead of <> or =.
- Spot the NOT IN trap: one NULL in the list makes the predicate never TRUE.
Understanding Why comparing with NULL never matches
Every comparison operator in SQL (=, <>, <, >, BETWEEN, LIKE) takes two values and produces a truth value. NULL is not a value the operator can inspect; it is a marker meaning the value is missing, so the operator has nothing to compare and cannot honestly answer yes or no. SQL therefore defines a third truth value, UNKNOWN, and any comparison with a NULL operand produces it. Written out, salary = NULL does not ask whether salary is marked NULL, it asks whether an unknown number equals another unknown number, and the engine refuses to guess.
UNKNOWN looks like a disappearing act because WHERE, ON, and HAVING keep a row only when the predicate is TRUE, and UNKNOWN is thrown away exactly like FALSE. So WHERE note = NULL returns no rows, and WHERE note <> NULL also returns no rows, since an inequality test against NULL is just as undecidable as an equality test. Two filters that look like exact opposites can both come back empty, and no engine raises an error, so the symptom is a wrong count in a report rather than a crash.
To escape three-valued logic you need predicates defined on the marker itself. IS NULL and IS NOT NULL do that: they inspect whether the value is absent and return only TRUE or FALSE. IS DISTINCT FROM extends the same idea to two operands, treating NULL as comparable, so a NULL and a value are distinct while two NULLs are not, giving a NULL-safe <> (and IS NOT DISTINCT FROM a NULL-safe =). The habit to build is watching for hidden equality: IN, NOT IN, the simple CASE x WHEN form, and join conditions are all built on =, so they inherit this behaviour even when no literal = NULL appears in your query.
The practical consequence is that any predicate touching a nullable column silently splits rows into three groups, not two, and the third group is invisible unless you ask for it explicitly.
CREATE TABLE task (id INTEGER, note TEXT);
INSERT INTO task VALUES (1, 'ok'), (2, NULL), (3, 'late');
SELECT id,
CASE WHEN note = 'ok' THEN 'TRUE'
WHEN NOT (note = 'ok') THEN 'FALSE'
ELSE 'UNKNOWN' END AS eq_ok,
CASE WHEN note <> 'ok' THEN 'TRUE'
WHEN NOT (note <> 'ok') THEN 'FALSE'
ELSE 'UNKNOWN' END AS ne_ok,
CASE WHEN note IS NULL THEN 'TRUE' ELSE 'FALSE' END AS is_null
FROM task
ORDER BY id;A comparison with a NULL operand evaluates to UNKNOWN rather than TRUE or FALSE, and because filters keep only TRUE, those rows are dropped by the test and by its apparent opposite.
Worked examples
NULL = NULL is not a match
Shows that two NULLs do not compare equal, while IS NULL still answers definitively.
SELECT CASE WHEN NULL = NULL THEN 'TRUE' WHEN NOT (NULL = NULL) THEN 'FALSE' ELSE 'UNKNOWN' END AS eq,
CASE WHEN NULL <> NULL THEN 'TRUE' WHEN NOT (NULL <> NULL) THEN 'FALSE' ELSE 'UNKNOWN' END AS ne,
CASE WHEN NULL IS NULL THEN 'TRUE' ELSE 'FALSE' END AS marker_test;Example explained
Line 1NULL = NULL asks whether one unknown value equals another unknown value, so the result is UNKNOWN and the first WHEN is not taken.
Line 2NOT (NULL = NULL) is NOT UNKNOWN, which is still UNKNOWN, so the second WHEN is skipped too and the ELSE branch runs.
Line 3NULL IS NULL tests the marker rather than the value, which is why it can return TRUE and never leaves the CASE undecided.
Line 4Two rows that are both NULL therefore never match under =, even though GROUP BY and DISTINCT will still collapse them into one group.
The NOT IN trap
Demonstrates why a single NULL in an IN list breaks NOT IN but not IN.
CREATE TABLE emp (name TEXT, dept_id INTEGER);
INSERT INTO emp VALUES ('ana', 1), ('bo', 2), ('cy', NULL);
SELECT name FROM emp WHERE dept_id IN (1, NULL);
SELECT name FROM emp WHERE dept_id NOT IN (1, NULL);Example explained
Line 1IN (1, NULL) expands to dept_id = 1 OR dept_id = NULL; ana satisfies the first comparison, so the UNKNOWN from the second one cannot spoil the result.
Line 2NOT IN (1, NULL) expands to dept_id <> 1 AND dept_id <> NULL, and the second comparison is UNKNOWN for every row, so the predicate is never TRUE.
Line 3bo has dept_id 2 and clearly is not in department 1, yet the NULL in the list makes it impossible to confirm that 2 is absent from the list.
Line 4The fix is to keep NULLs out of the list, for example by adding IS NOT NULL to the query that builds it.
Detecting changes with IS DISTINCT FROM
Compares <> against IS DISTINCT FROM when either side of the comparison may be NULL.
CREATE TABLE change_log (id INTEGER, old_email TEXT, new_email TEXT);
INSERT INTO change_log VALUES
(1, 'a@x.com', 'a@y.com'),
(2, NULL, 'b@y.com'),
(3, NULL, NULL),
(4, 'd@x.com', 'd@x.com');
SELECT id FROM change_log WHERE old_email <> new_email ORDER BY id;
SELECT id FROM change_log WHERE old_email IS DISTINCT FROM new_email ORDER BY id;Example explained
Line 1Row 2 is a real change (no address before, an address now), but NULL <> 'b@y.com' is UNKNOWN, so the <> filter drops it.
Line 2IS DISTINCT FROM returns TRUE when exactly one side is NULL, so the second query reports row 2 as changed.
Line 3Row 3 has NULL on both sides and IS DISTINCT FROM calls them not distinct, so it is correctly reported as unchanged.
Line 4Row 4 is excluded by both queries because the two values are equal and the comparison is a plain FALSE.
Important notes
IS DISTINCT FROM is standard SQL and works in PostgreSQL, SQLite 3.39+, and SQL Server 2022+; on MySQL write NOT (a <=> b), and on older SQLite use a IS NOT b.
The simple form CASE status WHEN NULL THEN ... never takes that branch because it compares with =; use the searched form CASE WHEN status IS NULL THEN ... instead.
Common mistakes
Writing WHERE deleted_at = NULL to find live rows: engines accept it without error and return zero rows, so the table looks empty instead of the query looking wrong.
Assuming status <> 'open' is the complement of status = 'open': rows with NULL status appear in neither result, so the two subsets silently fail to cover the table.
Using NOT IN against a nullable column: the query works on clean development data and starts returning nothing the moment one NULL shows up in production.
Try it yourself
Change, predict, then run
Create a table with a nullable text column and insert three rows where one value is NULL, then count the rows matching col = 'x', col <> 'x', and col IS NULL. Confirm the first two counts do not add up to three and that the IS NULL count accounts for the difference.
Open the SQL workspaceCheck your understanding
A table has nullable columns old_price and new_price. Which predicate returns exactly the rows where the two values differ, counting a change from NULL to a number as a difference and NULL on both sides as no difference?
- WHERE old_price <> new_price
- WHERE old_price IS DISTINCT FROM new_price
- WHERE NOT (old_price = new_price)
- WHERE old_price <> new_price OR old_price IS NULL
Show answer
IS DISTINCT FROM always returns TRUE or FALSE: it calls the sides distinct when exactly one is NULL and not distinct when both are, which is precisely the rule asked for. The last option looks like a repair but reports a row where both prices are NULL as a change, and it still misses rows where only new_price is NULL; options one and three both evaluate to UNKNOWN whenever a NULL is involved, so those rows are filtered out.