SQL / NULL AND THREE-VALUED LOGIC
AND, OR, and NOT when values can be unknown
Predict what AND, OR and NOT return when an operand is unknown, and write filters that keep or drop NULL rows on purpose.
What you will learn
- Work out AND/OR results by testing whether true or false in NULL's place changes them
- Apply the absorbing rules: one false sinks an AND, one true saves an OR
- Explain why WHERE p and WHERE NOT p stop being complements once NULLs exist
- Rewrite filters with IS NOT TRUE, IS DISTINCT FROM or IS NULL to keep unknown rows
Understanding AND, OR, and NOT when values can be unknown
SQL conditions do not evaluate to two values but to three: true, false, and unknown. Unknown appears whenever a comparison touches a NULL, and the connectives then have to decide what to do with it. One rule generates every case: substitute both true and false for the unknown part, and if the overall answer is the same either way, that answer is the result; if the two substitutions disagree, the result is unknown. That is why false AND unknown is false (false either way) but true AND unknown is unknown, and why true OR unknown is true but false OR unknown is unknown.
NOT is where the trouble starts. Negating unknown yields unknown, because the opposite of something you do not know is still something you do not know. Combine that with the fact that WHERE, JOIN ON and HAVING keep only rows whose condition came out true, and WHERE p and WHERE NOT p stop being complements: a row where p is unknown is rejected by both, so the two result sets together can be smaller than the table. Nothing errors, nothing warns, the row is simply in neither answer.
In practice this makes AND and OR fragile in opposite directions. A long AND chain is only as strong as its weakest link, since a single unknown term drops a row that every other term matched, while an OR chain is rescued by any one true term, so an unknown branch there is often harmless. When you do want the unknown rows, reach for a construct that cannot itself return unknown: an explicit OR col IS NULL, (p) IS NOT TRUE, a IS DISTINCT FROM b, or COALESCE applied to the column before you compare it.
-- unknown is spelled out with COALESCE so it does not print as a blank
WITH v(label, val) AS (
VALUES ('true', true), ('false', false), ('unknown', null::boolean)
)
SELECT x.label AS a,
y.label AS b,
COALESCE((x.val AND y.val)::text, 'unknown') AS a_and_b,
COALESCE((x.val OR y.val)::text, 'unknown') AS a_or_b,
COALESCE((NOT x.val)::text, 'unknown') AS not_a
FROM v AS x, v AS y
ORDER BY 1, 2;AND and OR return unknown only when the missing value would actually change the answer, and NOT can never turn unknown into a row that passes WHERE.
Worked examples
OR rescues a row, AND drops it
The same two conditions on the same row give different outcomes depending on the connective.
CREATE TABLE orders (id int, status text, shipped_on date);
INSERT INTO orders VALUES
(1, 'shipped', DATE '2026-01-04'),
(2, 'shipped', NULL),
(3, 'pending', NULL);
SELECT id FROM orders
WHERE status = 'shipped' OR shipped_on < DATE '2026-02-01';
SELECT id FROM orders
WHERE status = 'shipped' AND shipped_on < DATE '2026-02-01';Example explained
Line 1Row 2 has status = 'shipped' true and the date comparison unknown, so the OR is true and the row survives.
Line 2The same row under AND gives true AND unknown = unknown, and WHERE discards unknown exactly as it discards false.
Line 3Row 3 is missing from both: false OR unknown is unknown, false AND unknown is false.
Line 4The AND query is not wrong syntactically, it just quietly returns one row fewer than the data suggests.
NOT IN with a NULL in the list
Why IN can still match while the negated form returns nothing at all.
WITH t(id, name) AS (
VALUES (1, 'ada'), (2, 'lin'), (3, 'kay')
)
SELECT id, name FROM t WHERE id IN (1, NULL);
WITH t(id, name) AS (
VALUES (1, 'ada'), (2, 'lin'), (3, 'kay')
)
SELECT id, name FROM t WHERE id NOT IN (1, NULL);Example explained
Line 1IN expands to an OR chain: id = 1 OR id = NULL, and for row 1 that is true OR unknown = true.
Line 2For rows 2 and 3 the chain is false OR unknown = unknown, so they are not returned.
Line 3NOT IN expands to an AND chain: id <> 1 AND id <> NULL, where the second term is unknown for every row.
Line 4The best any row can reach is true AND unknown = unknown, so a NULL anywhere in a NOT IN list empties the result.
Getting a real complement with IS NOT TRUE
NOT preserves unknown, while IS NOT TRUE collapses it into a definite answer.
WITH r(sensor, celsius) AS (
VALUES ('a', 12), ('b', 30), ('c', null::int)
)
SELECT sensor,
COALESCE((celsius > 20)::text, 'unknown') AS is_hot,
COALESCE((NOT (celsius > 20))::text, 'unknown') AS not_hot,
((celsius > 20) IS NOT TRUE)::text AS not_hot_safe
FROM r
ORDER BY sensor;Example explained
Line 1For sensor c the comparison is unknown because the reading is missing, not because it is small.
Line 2NOT (celsius > 20) is still unknown for c: NOT swaps true and false but leaves unknown alone.
Line 3(celsius > 20) IS NOT TRUE asks about the truth value itself, so it answers true for c and never returns unknown.
Line 4Filtering on the last column therefore splits the table into exactly two groups, which the not_hot column cannot do.
Important notes
IS NULL, IS NOT NULL, IS TRUE, IS NOT TRUE and IS DISTINCT FROM always return true or false, which is exactly why they are the tools for pulling unknown rows back into a result.
CHECK constraints are the exception to the true-only rule: they reject a row only when the condition is false, so a condition that evaluates to unknown is accepted.
Common mistakes
Treating WHERE NOT (p) as everything WHERE p missed: rows where p is unknown fail both filters, so the two counts add up to less than the table and no error is raised.
Writing col NOT IN (SELECT ...) when the subquery can produce a NULL: the AND chain becomes unknown for every row and the query returns zero rows that look like a legitimate empty result.
Bolting on one more AND condition against a nullable column as a harmless extra check: rows that satisfied every other condition turn unknown and vanish from the output.
Try it yourself
Change, predict, then run
Build a three-row table with n = 5, NULL and 20, then run WHERE n > 10, WHERE NOT (n > 10) and WHERE (n > 10) IS NOT TRUE in turn. Confirm the first two return two rows between them while the first and third account for all three.
Open the SQL workspaceCheck your understanding
A table has 100 rows. SELECT count(*) FROM t WHERE score > 50 returns 30, and SELECT count(*) FROM t WHERE NOT (score > 50) returns 55. What explains the 15 rows that appear in neither result?
- Those 15 rows have score exactly 50, so neither predicate is true for them
- count(*) ignores rows that contain a NULL, so the two results cannot sum to 100
- Those 15 rows have a NULL score: score > 50 is unknown, NOT leaves it unknown, and WHERE keeps only true
- The second query needed different parentheses; NOT bound to score alone and silently dropped 15 rows
Show answer
For a NULL score the comparison is unknown, NOT unknown is still unknown, and WHERE passes only true, so those rows are absent from both counts. Rows with score exactly 50 cannot be the answer: 50 > 50 is false, so NOT (50 > 50) is true and they are already inside the 55.