Nulls, Three-Valued Logic, and the Constraints They Break

SQL’s null is not a value. It is a marker for the absence of a value, and the language’s treatment of it is a deliberate departure from the relational model, which has no such marker at all: in Codd’s original formulation every attribute of every tuple holds an element of its domain, and “unknown” is not an element of any domain. The departure was made to represent incomplete information, and it brought a second departure with it — a third truth value — whose propagation rules decide the outcome of a large class of queries that look correct and are not.

Three truth values and the propagation table

A predicate in SQL evaluates to TRUE, FALSE, or UNKNOWN. The third value arises whenever a null participates in a comparison: x = NULL is not true and not false, it is unknown, and this holds even when x is itself null. The correct test for a null is IS NULL, which is a predicate over the null-ness of the operand rather than a comparison of values.

The boolean connectives follow Kleene’s three-valued logic:

AND	TRUE	FALSE	UNKNOWN
TRUE	TRUE	FALSE	UNKNOWN
FALSE	FALSE	FALSE	FALSE
UNKNOWN	UNKNOWN	FALSE	UNKNOWN

OR	TRUE	FALSE	UNKNOWN
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	UNKNOWN
UNKNOWN	TRUE	UNKNOWN	UNKNOWN

NOT UNKNOWN is UNKNOWN.

Two properties of these tables drive everything else. First, FALSE AND UNKNOWN is FALSE — a false conjunct is enough to make the conjunction false, regardless of the other operand. Second, TRUE OR UNKNOWN is TRUE — a true disjunct is enough. These are the cases where the third value gets absorbed, and they are why some queries with nulls behave exactly as intuition suggests while structurally similar ones do not.

WHERE accepts only rows whose predicate is TRUE. UNKNOWN is treated as a rejection, identically to FALSE. This is the single most consequential rule in the system: it means a null in a filtered column silently removes rows, with no error and no warning.

-- Rows where commission is null are excluded by both predicates,
-- so these two queries return the same rows.
SELECT id FROM sales WHERE commission > 1000;
SELECT id FROM sales WHERE commission <= 1000;

The second query is not the complement of the first. Their union omits every row where commission IS NULL, and the sum of their counts is less than the table’s row count. Any application logic that partitions a set with > and <= and assumes the partitions cover the table is wrong in the presence of nulls.

Where the absorption rule produces surprises

NOT IN with a subquery is the classic case, and it is worth deriving rather than memorizing. x NOT IN (SELECT y FROM t) expands to NOT (x = y1 OR x = y2 OR … ) over the rows of the subquery. If any yi is null, then x = yi is UNKNOWN for every x. By the OR table, the disjunction is TRUE if any comparison is true, and UNKNOWN otherwise. Taking NOT of UNKNOWN gives UNKNOWN, which WHERE rejects.

The result: if the subquery returns even one null, NOT IN returns no rows at all — for any outer value, including values that are definitely not in the list. The predicate is not “no match”; it is “no match and no unknown,” and a single null makes the second condition unsatisfiable.

-- Returns nothing if any customer.region_id is NULL.
SELECT c.id
FROM customer AS c
WHERE c.region_id NOT IN (SELECT r.id FROM region AS r);

-- Returns the intended result regardless of nulls in region.id.
SELECT c.id
FROM customer AS c
WHERE NOT EXISTS (
  SELECT 1 FROM region AS r WHERE r.id = c.region_id
);

NOT EXISTS evaluates the correlated subquery per outer row and asks whether any row satisfied the inner predicate. A null region_id makes the inner predicate UNKNOWN for every region, so the subquery returns no rows, EXISTS is false, and NOT EXISTS is true. The outer row is returned. The two formulations differ exactly on the rows whose key is null, and the difference is a semantic decision, not an optimization.

EXISTS is also immune to the projection problem: it tests for the presence of a row, not the value of a column, so SELECT NULL inside an EXISTS subquery is fine, whereas SELECT NULL inside an IN subquery poisons the result.

Aggregates, grouping, and the null that is not a value

Aggregates other than COUNT(*) ignore nulls. SUM, AVG, MIN, and MAX skip null inputs; COUNT(x) counts non-null values of x; COUNT(*) counts rows. This produces three consequences that are easy to get wrong.

AVG(x) is SUM(x) / COUNT(x), not SUM(x) / COUNT(*). A column with a hundred rows, ninety of which are null, yields the mean of the ten non-null values, not a value diluted by ninety zeroes. Whether that is the intended semantics depends on what the null means: if it means “zero” the answer is wrong, and if it means “not measured” the answer is right. The schema cannot distinguish the two, and this ambiguity is the strongest argument for avoiding nullable columns whose null means zero.

SUM over an empty set or an all-null set returns NULL, not 0. The sum of no numbers is zero mathematically, but SQL reports the absence of any value to sum. Coalescing with COALESCE(SUM(x), 0) is the standard fix, and forgetting it produces nulls propagating into application arithmetic.

GROUP BY treats nulls as equal to each other. All rows with a null in the grouping column form one group, even though NULL = NULL is UNKNOWN. The grouping operation uses a notion of “not distinct” rather than equality, and DISTINCT in a SELECT list does the same: nulls collapse into a single distinct value. This is consistent with the treatment of nulls in UNIQUE constraints, discussed below, and inconsistent with the treatment of nulls in WHERE, which is a real seam in the language.

The ordering of nulls is implementation-defined but every major system has converged on a convention: nulls sort either first or last depending on the system and on an explicit NULLS FIRST / NULLS LAST clause, which the standard defines. Code that relies on nulls sorting as the smallest value is relying on a default that differs between systems.

Constraints that a null satisfies

The integrity constraints of the relational model are predicates that every tuple must satisfy. In SQL, a CHECK constraint is satisfied when its predicate evaluates to TRUE or UNKNOWN. A null therefore passes any check that mentions it.

CREATE TABLE shipment (
  id        bigint PRIMARY KEY,
  weight_kg numeric CHECK (weight_kg > 0)
);

An insert with weight_kg = NULL succeeds. The predicate NULL > 0 is UNKNOWN, and UNKNOWN is not a violation. To reject nulls as well as non-positive values, the column needs NOT NULL in addition — two constraints for one intent, and the second is easy to omit because the first looks like it covers the case.

UNIQUE constraints permit multiple nulls in the implementations that treat two nulls as not equal, and reject them in the implementations that treat them as equal. PostgreSQL and SQLite allow any number of rows with a null in a unique column; systems that follow the other convention allow only one. The behaviour is worth checking per system, because it changes whether a “unique email” column actually prevents a thousand rows with no email.

PRIMARY KEY implies NOT NULL in the standard, so the primary key cannot contain nulls. This is the one place where the language’s null handling aligns with the model, and it is the reason a surrogate key is often preferable to a natural key that might be unknown: the surrogate is always present, so the primary key constraint is always satisfiable.

Foreign keys have their own null rule, governed by the match type: under MATCH SIMPLE, any null in a composite key exempts the row from the reference requirement. That behaviour and its consequences are described in referential integrity and the semantics of foreign key actions. The pattern is consistent: a null is exempt from constraints rather than subject to them, because a constraint over an unknown value cannot be evaluated.

The two competing readings of a null

The literature distinguishes two interpretations, and the distinction is not academic — it determines whether a given schema is correct.

Under the value unknown reading, a null means a value exists in the domain but is not recorded. A birth date not yet supplied, a measurement not yet taken. Under this reading, NULL = NULL should be TRUE — the unknown value is the same unknown value — and the language’s UNKNOWN is a conservative approximation of a comparison whose answer is not known to the system. Aggregates that skip nulls are computing over the known subset, which is a legitimate but partial answer.

Under the value not applicable reading, a null means no value exists for this attribute in this row. A person’s maiden name for a person who has none. Under this reading, comparing nulls is meaningless and skipping them in aggregates is correct, because the attribute does not apply.

SQL implements a single semantics that conflates both, and the conflation is why null handling is the most common source of silent query bugs. The practical discipline is to avoid nullable columns except where the “not applicable” reading is intended, and to use NOT NULL plus a sentinel or a separate relation where the “unknown” reading is intended. A separate relation for optional attributes — an attribute-value design taken to its conclusion — is the sixth normal form decomposition discussed in sixth normal form and attribute-level decomposition.

The deeper point is that the relational model has no null because it does not need one. Incomplete information is represented by the absence of a tuple, not by a marker inside one. A relation recording what is known about employees contains a tuple for each employee whose salary is known, and no tuple for an employee whose salary is not — and the query “employees with salary above 100,000” then returns exactly the employees for whom the answer is known. The SQL null exists because a table has a fixed degree and every row must have a value in every column, a constraint the model does not impose. That structural difference is the root of the entire three-valued apparatus, and it is why the model’s treatment of missing data, described in relations as sets and duplicate elimination, looks nothing like SQL’s.