NULL и трёхзначная логика

NULL — это «неизвестно», а не «пусто» или «ноль»

Единственная идея, на которой держится всё остальное: NULL — не значение, а отсутствие известного значения, «мы не знаем». Это не ноль, не пустая строка, не false. Два NULL не равны друг другу, потому что «неизвестно» нельзя подтвердить равным «неизвестно».

Значит, сравнения с NULL не возвращают TRUE или FALSE — они возвращают третье значение истины, UNKNOWN. 5 = NULL — это UNKNOWN. NULL = NULL — UNKNOWN. NULL <> 3 — UNKNOWN. SQL поэтому — трёхзначная логика: TRUE, FALSE и UNKNOWN.

Жёсткое правило, которое из этого следует: WHERE оставляет строку, только когда её предикат вычисляется в TRUE. UNKNOWN — это не TRUE, так что строка, чей предикат UNKNOWN, отбрасывается — ровно как при FALSE. Эта симметрия — источник почти всех NULL-сюрпризов.

-- users(id, email, deleted_at)  -- deleted_at равен NULL у активных пользователей
SELECT count(*) FROM users WHERE deleted_at = NULL;   -- всегда 0 строк!
SELECT count(*) FROM users WHERE deleted_at IS NULL;  -- корректная проверка

= NULL — никогда не та проверка, что тебе нужна. Чтобы спросить «это неизвестно?», нужно использовать специальные операторы IS NULL / IS NOT NULL, возвращающие настоящие булевы значения.

Трёхзначные таблицы истинности

Каждой связке нужно определить, что она делает, когда операнд UNKNOWN. Ключевые записи:

Две строки короткого замыкания важны на практике: TRUE OR <что угодно> — это TRUE, а FALSE AND <что угодно> — это FALSE, даже когда «что угодно» — UNKNOWN, потому что результат уже определён. Любая другая комбинация, касающаяся UNKNOWN, даёт UNKNOWN.

Ловушка NOT IN, расшифрованная

Теперь инцидент из вступления. x NOT IN (a, b, c) определяется как x <> a AND x <> b AND x <> c. Допустим, список — (1, 2, NULL), а x = 5:

5 <> 1  → TRUE
5 <> 2  → TRUE
5 <> NULL → UNKNOWN      -- потому что сравнение с NULL всегда UNKNOWN
TRUE AND TRUE AND UNKNOWN → UNKNOWN

Всё выражение схлопывается в UNKNOWN, и строка отбрасывается. Даже с одним NULL в списке NOT IN ни одна строка никогда не станет TRUE — запрос возвращает ноль строк, молча, для любого значения. Именно так один NULL в blocked_users обнулил фрод-фильтр.

Фиксы, в порядке предпочтения:

• Используй NOT EXISTS вместо NOT IN для подзапросов. NOT EXISTS NULL-безопасен (проверяет существование строки, а не равенство значений) и обычно планируется лучше как anti-join:

SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM blocked_users b WHERE b.user_id = u.id
);

• Или исключи NULL из подзапроса: ... NOT IN (SELECT user_id FROM blocked_users WHERE user_id IS NOT NULL).
• Ещё лучше — сделай колонку NOT NULL, чтобы баг был невозможен по построению.

Все три фикса устраняют одну и ту же корневую причину: как только NULL может попасть в сравнение, логика рушится. Ограничение NOT NULL — сильнейший вариант, оно делает невозможным само возникновение ситуации; NOT EXISTS — самая безопасная замена без изменения схемы.

IS DISTINCT FROM и NULL-aware инструментарий

Иногда тебе действительно нужно «эти два значения различны, считая NULL обычным сравниваемым значением?». Простой <> не может — он уходит в UNKNOWN на NULL. Postgres даёт IS DISTINCT FROM: он возвращает настоящий булев, считая два NULL не различными (равными), а NULL-против-значения — различными.

-- обнаружить изменённую колонку, даже если любая сторона может быть NULL:
SELECT * FROM staging s JOIN target t USING (id)
WHERE s.email IS DISTINCT FROM t.email;   -- TRUE/FALSE, никогда UNKNOWN

Повседневные помощники:

• COALESCE(a, b, …) возвращает первый не-NULL аргумент — твой инструмент значения по умолчанию: COALESCE(nickname, email, 'anon').
• NULLIF(a, b) возвращает NULL, когда a = b, иначе a — удобно обойти деление на ноль: total / NULLIF(qty, 0).

И два факта об агрегатах, на которых спотыкаются: агрегаты пропускают NULL. COUNT(col) считает только не-NULL значения col, тогда как COUNT(*) считает строки независимо. AVG(col) усредняет только по не-NULL значениям — так что AVG может отличаться от SUM/COUNT(*), когда есть NULL.