Three-valued logic: how SQL reasons about NULL

NULL marks a value that is missing, and a comparison with a missing value cannot be true or false: SQL gives it a third truth value, UNKNOWN. With three values, AND, OR and NOT follow one compact rule. Order the values FALSE < UNKNOWN < TRUE: AND returns the lower of its operands, OR the higher, and NOT swaps TRUE and FALSE but leaves UNKNOWN as it is. The real work is done where a clause has to turn UNKNOWN into a decision, because the clauses decide differently. WHERE, ON, HAVING and CASE WHEN treat UNKNOWN like FALSE, a CHECK constraint treats it like TRUE, and DISTINCT, GROUP BY and the set operators treat two NULLs as the same value even though NULL = NULL is UNKNOWN. Knowing which rule each place applies predicts almost every NULL surprise.

The SQL track’s NULL lesson drills IS NULL, COALESCE and NULLIF. This lesson works out the logic underneath, with each table recorded in SQLite and the PostgreSQL rules from its manual.

Three truth values and the order behind them

SQLite has no separate Boolean type: it stores TRUE as 1, FALSE as 0 and UNKNOWN as NULL. The query below builds all nine pairs of truth values, computes AND, OR and NOT for each, and names the results, so that UNKNOWN shows as a word rather than as an empty cell:

-- SQLite stores the three truth values as 1 (TRUE), 0 (FALSE) and NULL (UNKNOWN).
CREATE TABLE tv (t INTEGER, name TEXT, rank INTEGER);
INSERT INTO tv VALUES (0, 'FALSE', 0), (NULL, 'UNKNOWN', 1), (1, 'TRUE', 2);

-- All nine pairs (p, q). IS NOT DISTINCT FROM finds the name of a result even when it is NULL.
SELECT p.name AS p, q.name AS q, a.name AS "p AND q", o.name AS "p OR q", n.name AS "NOT p"
FROM tv AS p
CROSS JOIN tv AS q
JOIN tv AS a ON a.t IS NOT DISTINCT FROM (p.t AND q.t)
JOIN tv AS o ON o.t IS NOT DISTINCT FROM (p.t OR q.t)
JOIN tv AS n ON n.t IS NOT DISTINCT FROM (NOT p.t)
ORDER BY p.rank, q.rank;

-- Rank FALSE < UNKNOWN < TRUE: AND is the smaller rank and OR the larger, in every one of the nine pairs.
SELECT count(*) AS pairs,
       sum(a.rank = min(p.rank, q.rank)) AS and_is_min,
       sum(o.rank = max(p.rank, q.rank)) AS or_is_max
FROM tv AS p
CROSS JOIN tv AS q
JOIN tv AS a ON a.t IS NOT DISTINCT FROM (p.t AND q.t)
JOIN tv AS o ON o.t IS NOT DISTINCT FROM (p.t OR q.t);

┌─────────┬─────────┬─────────┬─────────┬─────────┐
│    p    │    q    │ p AND q │ p OR q  │  NOT p  │
├─────────┼─────────┼─────────┼─────────┼─────────┤
│ FALSE   │ FALSE   │ FALSE   │ FALSE   │ TRUE    │
│ FALSE   │ UNKNOWN │ FALSE   │ UNKNOWN │ TRUE    │
│ FALSE   │ TRUE    │ FALSE   │ TRUE    │ TRUE    │
│ UNKNOWN │ FALSE   │ FALSE   │ UNKNOWN │ UNKNOWN │
│ UNKNOWN │ UNKNOWN │ UNKNOWN │ UNKNOWN │ UNKNOWN │
│ UNKNOWN │ TRUE    │ UNKNOWN │ TRUE    │ UNKNOWN │
│ TRUE    │ FALSE   │ FALSE   │ TRUE    │ FALSE   │
│ TRUE    │ UNKNOWN │ UNKNOWN │ TRUE    │ FALSE   │
│ TRUE    │ TRUE    │ TRUE    │ TRUE    │ FALSE   │
└─────────┴─────────┴─────────┴─────────┴─────────┘
┌───────┬────────────┬───────────┐
│ pairs │ and_is_min │ or_is_max │
├───────┼────────────┼───────────┤
│ 9     │ 9          │ 9         │
└───────┴────────────┴───────────┘

The first table matches the truth tables in PostgreSQL’s manual for its boolean type (PostgreSQL 18: logical operators). The second checks the rule in the introduction on all nine pairs: give FALSE, UNKNOWN and TRUE the ranks 0, 1 and 2, and the rank of p AND q is always the smaller of the two ranks, the rank of p OR q always the larger. Two consequences are worth remembering:

• A known operand can settle the answer. FALSE AND anything is FALSE, and TRUE OR anything is TRUE: FALSE is the lowest value and TRUE the highest.
• UNKNOWN spreads otherwise. TRUE AND UNKNOWN is UNKNOWN, and so are FALSE OR UNKNOWN and NOT UNKNOWN.

Comparisons are where UNKNOWN comes from. score > 40, score = 40 and score <> 40 are all UNKNOWN when score is NULL, and so is NULL = NULL: two missing values are not known to be equal. Arithmetic passes NULL on the same way, so score + 5 is NULL too.

Laws that stop holding

Two-valued logic has laws that SQL programmers lean on without noticing. Several of them fail as soon as a column holds NULL. Here are six test scores, two of them missing because those students were absent:

-- Six test scores; two students were absent, so their scores are unknown.
CREATE TABLE scores (roll INTEGER PRIMARY KEY, score INTEGER);
INSERT INTO scores VALUES (101, 72), (102, 35), (103, NULL), (104, 58), (105, NULL), (106, 40);

-- Conditions that two-valued logic would call always true. Each column counts the rows WHERE keeps.
SELECT
  (SELECT count(*) FROM scores) AS all_rows,
  (SELECT count(*) FROM scores WHERE score >= 40 OR NOT (score >= 40)) AS p_or_not_p,
  (SELECT count(*) FROM scores WHERE score = score) AS x_equals_x,
  (SELECT count(*) FROM scores WHERE score >= 40 OR score < 40) AS above_or_below;

-- A law that still holds: NOT (score >= 40) and score < 40 keep the same rows.
SELECT
  (SELECT group_concat(roll, ' ') FROM scores WHERE NOT (score >= 40)) AS not_at_least_40,
  (SELECT group_concat(roll, ' ') FROM scores WHERE score < 40) AS below_40;

┌──────────┬────────────┬────────────┬────────────────┐
│ all_rows │ p_or_not_p │ x_equals_x │ above_or_below │
├──────────┼────────────┼────────────┼────────────────┤
│ 6        │ 4          │ 4          │ 4              │
└──────────┴────────────┴────────────┴────────────────┘
┌─────────────────┬──────────┐
│ not_at_least_40 │ below_40 │
├─────────────────┼──────────┤
│ 102             │ 102      │
└─────────────────┴──────────┘

Each of the three conditions would keep all six rows in two-valued logic, and each keeps four:

• p OR NOT p is UNKNOWN when p is: the law of the excluded middle does not hold.
• score = score is UNKNOWN for a NULL score, so a value is not always equal to itself.
• score >= 40 OR score < 40 misses the same two rows: a condition and its opposite do not cover the table.

Other laws survive. NOT (score >= 40) and score < 40 keep the same rows, because both are UNKNOWN for the absent students, and De Morgan’s laws and double negation hold in three-valued logic as well. This is what limits an optimiser. It may replace NOT (score >= 40) with score < 40, but it may not replace score = score with TRUE: the equivalent condition is score IS NOT NULL.

The practical rule follows: when a query splits rows into groups by a condition and its negation, the rows where the condition is UNKNOWN form a third group, and someone has to decide where they belong.

Where UNKNOWN goes: each clause decides

A condition that can be UNKNOWN still has to produce a decision in the end, and SQL’s clauses resolve it in two opposite ways:

• WHERE, ON and HAVING: a TRUE condition keeps the row or group; FALSE and UNKNOWN drop it.
• CASE WHEN: TRUE takes this branch; FALSE and UNKNOWN move on to the next WHEN, or to ELSE.
• A CHECK constraint: FALSE rejects the row; TRUE and UNKNOWN both accept it.

WHERE keeps a row only when its condition is TRUE, and SQLite’s manual says rows are excluded when it is FALSE or NULL (SQLite: SELECT). A CHECK constraint rejects a row only when its condition is FALSE, so a NULL passes most checks: PostgreSQL’s manual says a check constraint is satisfied by TRUE or the null value (PostgreSQL 18: constraints). The same condition as a constraint and as a filter:

-- The same condition as a constraint and as a filter.
CREATE TABLE scores (roll INTEGER PRIMARY KEY, score INTEGER CHECK (score BETWEEN 0 AND 100));
INSERT INTO scores VALUES (101, 72);
INSERT INTO scores VALUES (102, NULL);
INSERT INTO scores VALUES (103, 120);

-- CHECK rejected only the row whose condition was FALSE; the UNKNOWN one is stored.
SELECT roll, quote(score) AS score FROM scores ORDER BY roll;

-- WHERE keeps only the rows whose condition is TRUE.
SELECT roll FROM scores WHERE score BETWEEN 0 AND 100;

-- IS NOT FALSE turns UNKNOWN into TRUE, so this WHERE decides the way CHECK did.
SELECT roll FROM scores WHERE (score BETWEEN 0 AND 100) IS NOT FALSE;

┌──────┬───────┐
│ roll │ score │
├──────┼───────┤
│ 101  │ 72    │
│ 102  │ NULL  │
└──────┴───────┘
┌──────┐
│ roll │
├──────┤
│ 101  │
└──────┘
┌──────┐
│ roll │
├──────┤
│ 101  │
│ 102  │
└──────┘

Runtime error near line 5: CHECK constraint failed: score BETWEEN 0 AND 100

The CHECK stored student 102 with no score, because NULL BETWEEN 0 AND 100 is UNKNOWN, and rejected 120, for which the condition is FALSE. The WHERE with the same condition then dropped student 102. If a score must be present, the constraint that says so is NOT NULL: a check on the range of a value never requires the value to exist.

When you want to choose the rule yourself, the Boolean tests make it explicit. cond IS TRUE is TRUE only for TRUE, cond IS NOT FALSE is TRUE for TRUE and UNKNOWN, and cond IS UNKNOWN picks out the third value. They never return UNKNOWN themselves (PostgreSQL 18: comparison operators). SQLite accepts IS TRUE, IS NOT TRUE, IS FALSE and IS NOT FALSE, but not IS UNKNOWN (SQLite: boolean expressions). The last query of the example uses IS NOT FALSE to make a WHERE decide the way the CHECK did, keeping student 102.

When two NULLs count as the same

NULL = NULL is UNKNOWN, yet SELECT DISTINCT returns one NULL, not two. SQL compares values in two different ways: equality with =, which follows three-valued logic, and distinctness, which is two-valued and treats two NULLs as not distinct. DISTINCT compares rows with IS DISTINCT FROM in SQLite’s words, and the compound operators consider NULL equal to NULL (SQLite: SELECT). Which constructs use which comparison:

-- Cities of two groups of students; some cities were never filled in.
CREATE TABLE a (city TEXT);
CREATE TABLE b (city TEXT);
INSERT INTO a VALUES ('Pune'), (NULL), (NULL);
INSERT INTO b VALUES ('Pune'), (NULL);

-- How many rows each construct returns: the first four treat the NULLs as equal, = does not.
SELECT 'SELECT DISTINCT' AS construct, count(*) AS result_rows FROM (SELECT DISTINCT city FROM a)
UNION ALL SELECT 'GROUP BY', count(*) FROM (SELECT city FROM a GROUP BY city)
UNION ALL SELECT 'UNION', count(*) FROM (SELECT city FROM a UNION SELECT city FROM b)
UNION ALL SELECT 'INTERSECT', count(*) FROM (SELECT city FROM a INTERSECT SELECT city FROM b)
UNION ALL SELECT 'JOIN ON =', count(*) FROM a JOIN b ON a.city = b.city
UNION ALL SELECT 'JOIN ON IS NOT DISTINCT FROM', count(*) FROM a JOIN b ON a.city IS NOT DISTINCT FROM b.city;

-- A UNIQUE column accepts any number of NULLs, but not the same value twice.
CREATE TABLE contacts (roll INTEGER, email TEXT UNIQUE);
INSERT INTO contacts VALUES (101, NULL);
INSERT INTO contacts VALUES (102, NULL);
INSERT INTO contacts VALUES (103, 'kabir@example.com');
INSERT INTO contacts VALUES (104, 'kabir@example.com');
SELECT roll, quote(email) AS email FROM contacts ORDER BY roll;

┌──────────────────────────────┬─────────────┐
│          construct           │ result_rows │
├──────────────────────────────┼─────────────┤
│ SELECT DISTINCT              │ 2           │
│ GROUP BY                     │ 2           │
│ UNION                        │ 2           │
│ INTERSECT                    │ 2           │
│ JOIN ON =                    │ 1           │
│ JOIN ON IS NOT DISTINCT FROM │ 3           │
└──────────────────────────────┴─────────────┘
┌──────┬─────────────────────┐
│ roll │        email        │
├──────┼─────────────────────┤
│ 101  │ NULL                │
│ 102  │ NULL                │
│ 103  │ 'kabir@example.com' │
└──────┴─────────────────────┘

Runtime error near line 20: UNIQUE constraint failed: contacts.email

Read the first table row by row. Table a holds Pune and two NULLs; table b holds Pune and one NULL.

• SELECT DISTINCT and GROUP BY return 2 rows: Pune and a single NULL. All NULLs fall into one group.
• UNION and INTERSECT return 2 rows too: INTERSECT finds the NULL row in both tables and keeps it.
• JOIN ON a.city = b.city returns 1 row, Pune with Pune. The NULLs never join, because = is UNKNOWN for them.
• JOIN ON a.city IS NOT DISTINCT FROM b.city returns 3 rows: Pune with Pune, and each of the two NULLs in a with the NULL in b. This operator compares the way DISTINCT does.

So INTERSECT and an inner join on = can disagree about the same two tables: the first keeps a row of NULLs, the second never does.

UNIQUE is the exception. A UNIQUE column accepts any number of NULLs in SQLite and, by default, in PostgreSQL: the second table shows two students without an email and the error from the repeated address. SQLite’s NULL page calls this split between UNIQUE and DISTINCT puzzling but keeps it for compatibility (SQLite: NULL handling). PostgreSQL 15 and later can make a unique constraint treat NULLs as equal with UNIQUE NULLS NOT DISTINCT (PostgreSQL 15 release notes), and its manual notes that the SQL standard leaves the default to each implementation (PostgreSQL 18: constraints).

NULLs inside lists: IN and NOT IN

x IN (a, b) is shorthand for x = a OR x = b, and x NOT IN (a, b) for x <> a AND x <> b (PostgreSQL 18: row and array comparisons). Expanding the list shows what a NULL in it does:

-- x NOT IN (a, b) means x <> a AND x <> b; with a NULL in the list one conjunct is always UNKNOWN.
CREATE TABLE tv (t INTEGER, name TEXT);
INSERT INTO tv VALUES (1, 'TRUE'), (0, 'FALSE'), (NULL, 'UNKNOWN');

SELECT e.expression, tv.name AS result
FROM (
  SELECT 1 AS k, '103 NOT IN (101, 102)' AS expression, 103 NOT IN (101, 102) AS v
  UNION ALL SELECT 2, '103 NOT IN (101, NULL)', 103 NOT IN (101, NULL)
  UNION ALL SELECT 3, '103 <> 101 AND 103 <> NULL', 103 <> 101 AND 103 <> NULL
  UNION ALL SELECT 4, '101 NOT IN (101, NULL)', 101 NOT IN (101, NULL)
  UNION ALL SELECT 5, 'NULL NOT IN (101, 102)', NULL NOT IN (101, 102)
  UNION ALL SELECT 6, 'NULL IN (101, 102)', NULL IN (101, 102)
) AS e
JOIN tv ON tv.t IS NOT DISTINCT FROM e.v
ORDER BY e.k;

┌────────────────────────────┬─────────┐
│         expression         │ result  │
├────────────────────────────┼─────────┤
│ 103 NOT IN (101, 102)      │ TRUE    │
│ 103 NOT IN (101, NULL)     │ UNKNOWN │
│ 103 <> 101 AND 103 <> NULL │ UNKNOWN │
│ 101 NOT IN (101, NULL)     │ FALSE   │
│ NULL NOT IN (101, 102)     │ UNKNOWN │
│ NULL IN (101, 102)         │ UNKNOWN │
└────────────────────────────┴─────────┘

• 103 NOT IN (101, NULL) expands to 103 <> 101 AND 103 <> NULL, which is TRUE AND UNKNOWN: UNKNOWN.
• 101 NOT IN (101, NULL) is FALSE, because 101 <> 101 is FALSE and FALSE decides an AND.

So with a NULL in the list, NOT IN is FALSE or UNKNOWN for every row and never TRUE: a WHERE x NOT IN (…) returns nothing at all. A NULL on the left makes both IN and NOT IN UNKNOWN for any list that is not empty. PostgreSQL’s manual states the same rules for IN and NOT IN with a subquery (PostgreSQL 18: subquery expressions), which is where the NULL usually comes from: a list of values read from a column. The subqueries lesson replaces it with NOT EXISTS.

Aggregates skip NULLs, and NULLs sort at one end

Aggregate functions sidestep three-valued logic: they skip NULL inputs, and only count(*) counts rows:

CREATE TABLE scores (roll INTEGER PRIMARY KEY, score INTEGER);
INSERT INTO scores VALUES (101, 72), (102, 35), (103, NULL), (104, 58), (105, NULL), (106, 40);

-- Aggregates skip NULLs, except count(*), which counts rows.
SELECT count(*) AS rows_, count(score) AS scores, sum(score) AS total,
       avg(score) AS average, sum(score) * 1.0 / count(*) AS per_student,
       avg(coalesce(score, 0)) AS absent_as_zero
FROM scores;

-- Over no rows at all: count is 0, the others are NULL.
SELECT count(*) AS rows_, count(score) AS scores, quote(sum(score)) AS total, quote(avg(score)) AS average,
       quote(max(score)) AS highest
FROM scores WHERE roll > 200;

┌───────┬────────┬───────┬─────────┬──────────────────┬──────────────────┐
│ rows_ │ scores │ total │ average │   per_student    │  absent_as_zero  │
├───────┼────────┼───────┼─────────┼──────────────────┼──────────────────┤
│ 6     │ 4      │ 205   │ 51.25   │ 34.1666666666667 │ 34.1666666666667 │
└───────┴────────┴───────┴─────────┴──────────────────┴──────────────────┘
┌───────┬────────┬───────┬─────────┬─────────┐
│ rows_ │ scores │ total │ average │ highest │
├───────┼────────┼───────┼─────────┼─────────┤
│ 0     │ 0      │ NULL  │ NULL    │ NULL    │
└───────┴────────┴───────┴─────────┴─────────┘

• count(*) is 6, count(score) is 4. The two absent students are rows, but not scores.
• avg(score) is 51.25, the total 205 divided by 4 scores, not by 6 students. Dividing by count(*) gives 34.17, which is the average if absent students count as zero; avg(coalesce(score, 0)) says that more clearly. Which one is right is a decision about the data, not about SQL.
• Over no rows, count returns 0 and sum, avg and max return NULL. PostgreSQL’s manual makes the same point about sum of no rows (PostgreSQL 18: aggregate functions), and SQLite adds total(), which returns 0.0 instead (SQLite: aggregate functions).

Sorting is the last place NULLs need a rule, and here the two engines differ. SQLite sorts NULL below every other value, so NULLs come first in ascending order (SQLite: SELECT); PostgreSQL sorts NULL as larger than every value, so they come last (PostgreSQL 18: sorting rows):

CREATE TABLE scores (roll INTEGER PRIMARY KEY, score INTEGER);
INSERT INTO scores VALUES (101, 72), (102, 35), (103, NULL), (104, 58);

-- SQLite sorts NULL below every other value; NULLS FIRST and NULLS LAST say where it goes.
SELECT
  (SELECT group_concat(coalesce(score, 'NULL'), ' ') FROM (SELECT score FROM scores ORDER BY score)) AS ascending,
  (SELECT group_concat(coalesce(score, 'NULL'), ' ') FROM (SELECT score FROM scores ORDER BY score DESC)) AS descending,
  (SELECT group_concat(coalesce(score, 'NULL'), ' ') FROM (SELECT score FROM scores ORDER BY score NULLS LAST)) AS asc_nulls_last;

┌───────────────┬───────────────┬────────────────┐
│   ascending   │  descending   │ asc_nulls_last │
├───────────────┼───────────────┼────────────────┤
│ NULL 35 58 72 │ 72 58 35 NULL │ 35 58 72 NULL  │
└───────────────┴───────────────┴────────────────┘

NULLS FIRST and NULLS LAST fix the position in both engines; SQLite has accepted them since version 3.30.0 (SQLite: release history). Write one of them whenever the position of missing values matters, such as a top-ten list where an unknown score must not come first.

Choosing the right tool

Each tool for NULLs answers a different question, and mixing them up causes most NULL bugs:

• IS NULL and IS NOT NULL ask whether a value is missing. They are always TRUE or FALSE.
• IS DISTINCT FROM and IS NOT DISTINCT FROM compare two values that may be missing, the way DISTINCT does: “has the email changed?” is old_email IS DISTINCT FROM new_email. SQLite has supported them since 3.39.0 and also spells them IS NOT and IS.
• COALESCE(x, default) replaces a missing value. It changes the question, as the zero-score average showed, so use it when you mean the default, not to make a NULL disappear.
• NOT NULL in the schema is the strongest fix: a value that cannot be missing never needs three-valued logic.

Interview questions

Fresher · A table has CHECK (score BETWEEN 0 AND 100). Does inserting a row with a NULL score succeed, and why does the same condition behave differently in a WHERE clause?

The insert succeeds. NULL BETWEEN 0 AND 100 is UNKNOWN, and a CHECK constraint rejects a row only when its condition is FALSE, so UNKNOWN passes. WHERE keeps a row only when its condition is TRUE, so the same row is filtered out of SELECT … WHERE score BETWEEN 0 AND 100. Constraints give the row the benefit of the doubt; queries do not. If a score must always be present, declare the column NOT NULL, or write CHECK (score IS NOT NULL AND score BETWEEN 0 AND 100). A good answer also names the general rule: three-valued logic gives UNKNOWN, and each clause decides what UNKNOWN means.

Key takeaways

• Comparisons with NULL are UNKNOWN. With FALSE < UNKNOWN < TRUE, AND is the minimum, OR the maximum, and NOT leaves UNKNOWN unchanged.
• p OR NOT p and x = x are not always TRUE; NOT (x >= a) still equals x < a.
• WHERE, ON, HAVING and CASE WHEN treat UNKNOWN as FALSE; CHECK treats it as TRUE, so a range check never requires a value: declare the column NOT NULL.
• DISTINCT, GROUP BY, UNION and INTERSECT treat NULLs as equal; = does not; UNIQUE allows many NULLs unless PostgreSQL’s NULLS NOT DISTINCT says otherwise.
• A NULL in a NOT IN list makes the condition FALSE or UNKNOWN for every row, so the query returns nothing.
• Aggregates skip NULLs, count(*) counts rows, sum of no rows is NULL, and SQLite sorts NULLs first while PostgreSQL sorts them last unless NULLS FIRST or NULLS LAST says otherwise.