Why Does NULL = NULL Fail?

Because NULL does not represent a value — it represents the absence of one. Asking whether two absences are equal is asking a question the database cannot answer. If I do not know your age and you do not know mine, we cannot conclude that we are the same age. SQL models that with a third truth value: besides TRUE and FALSE there is UNKNOWN, and a comparison involving NULL produces UNKNOWN.

This is where SQL differs from most programming languages. In JavaScript a missing value compares equal to another missing value; in Python None equals None. SQL deliberately refuses that, because a NULL in a stored column usually means "not recorded", and treating two unrecorded values as equal would silently make rows match that should not.

Three things follow from UNKNOWN. WHERE keeps only rows where the predicate is TRUE, so rows with UNKNOWN are dropped. NOT UNKNOWN is still UNKNOWN, so negating a comparison against NULL does not rescue it. And an aggregate such as COUNT counts only non-NULL values, which is why COUNT(column) can be smaller than COUNT(*).

The mental model that makes this click: think of the column as holding a set of possible values rather than one value. A NULL means that set is empty. Empty set equals empty set? There is nothing to compare, so the answer is unknown rather than true.

The naive a = b drops every row where either side is NULL, because the comparison is UNKNOWN rather than TRUE. The standard fix is to compare the two IS NULL flags and, when both are present, compare the values.

CREATE TABLE people (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  preferred_name VARCHAR(120)
);

INSERT INTO people (id, name, preferred_name) VALUES
  (1, 'Ada Lovelace', 'Ada'),
  (2, 'Grace Hopper', 'Grace'),
  (3, 'Alan Turing',  'Alan'),
  (4, 'Joan Clarke',  NULL);

-- Naive equality silently loses the NULL row.
SELECT COUNT(*) AS naive_matches
FROM people WHERE preferred_name = name;

-- NULL-safe equality: counts all four rows.
SELECT COUNT(*) AS null_safe_matches
FROM people
WHERE (preferred_name = name)
   OR (preferred_name IS NULL AND name IS NULL);

The naive count is 3 and the null-safe count is 4. The fourth row is the one where preferred_name is NULL: with plain equality it is dropped, and with the NULL-safe form it is correctly counted as a match, because both sides are missing.

There is a neater way to write the same logic, and it is the one to reach for in practice. The IS operator is a null-safe comparison: it treats two NULLs as equal and a NULL against a value as unequal, so preferred_name IS name returns the same 4 rows with far less to get wrong. The catch is portability — IS with a value on the right is SQLite and PostgreSQL syntax, and MySQL does not support it there. If the code has to run on MySQL, use the explicit two-branch form above, or compare with COALESCE against a sentinel that cannot occur in the data.

There is no single universal answer, because the right behaviour depends on what "equal" should mean for a missing value. Work through the semantics before reaching for syntax.

First, decide the intent. If two missing values should count as equal, you want null-safe equality. If a missing value should never match anything — not even another missing value — you want strict equality where NULL rows are excluded on purpose, and you should write that explicitly rather than letting it happen by accident. If the columns are conceptually the same field copied into two places, you probably want to compare only rows where both are present and report mismatches, because a missing value on both sides is not evidence that they agree.

Second, pick the technique. The explicit form is (a = b) OR (a IS NULL AND b IS NULL). The compact form in SQLite and PostgreSQL is a IS b. The form that also lets you handle several columns at once is IS NOT DISTINCT FROM, which PostgreSQL supports and which compares two rows including their NULLs — it is the natural operator for comparing composite values.

Third, guard the performance. IS NOT DISTINCT FROM and COALESCE(a, sentinel) = COALESCE(b, sentinel) are not indexable in most engines, because the index stores the raw column value while the query compares a computed expression. If the comparison runs on a large table, a stored generated column, or an expression index on the same computed value, keeps the lookup on the index.

Fourth, remember the reverse trap. A predicate that looks like it compares values may actually be filtering on NULL: WHERE old_email = new_email quietly drops every row where either was never recorded, which is usually the opposite of what a migration check wants. Write the NULL case out loud in the query, and the intent survives review.