Почему NULL в SQL нельзя сравнивать как обычное значение

Небольшой пример: три строки, два известных значения

Представьте три остатка на счетах: 100, 0 и NULL. Счетов три, но известны только два остатка. На третьем счёте может быть ноль, долг или положительная сумма — в данных ответа нет. Поэтому отсутствие значения нельзя автоматически приравнивать к отсутствию денег.

Пример ниже использует VALUES и не требует создания таблицы. Он даёт total_rows = 3, known_balances = 2 и mean_known = 50. Если заменить пропуск нулём, mean_with_zero станет примерно 33,33: изменится знаменатель среднего. Это уже изменение смысла отчёта, а не просто оформление пустой ячейки.

WITH balances(balance) AS (
  VALUES (100::numeric), (0::numeric), (NULL::numeric)
)
SELECT COUNT(*) AS total_rows,
       COUNT(balance) AS known_balances,
       AVG(balance) AS mean_known,
       AVG(COALESCE(balance, 0)) AS mean_with_zero
FROM balances;

Найти пропуски

Неизвестный остаток не обязательно равен нулю. Сохраняйте это различие. В PostgreSQL оператор IS NOT DISTINCT FROM позволяет сравнивать значения, считая два NULL равными.

SELECT * FROM accounts WHERE balance IS NULL;
SELECT a IS NOT DISTINCT FROM b;

Посчитать нужные строки

COUNT(*) считает строки, а COUNT(balance) — непустые значения balance. COALESCE(balance, 0) заменяет пропуск нулём. Такая замена допустима, только если соответствует смыслу данных.

Почему условие неожиданно теряет строки

Для этих же данных WHERE balance <> 0 оставит только 100. Для нуля условие ложно, а для NULL результат неизвестен. Если нужны и ненулевые остатки, и пропуски, условие должно быть явным: balance <> 0 OR balance IS NULL.

Отрицание не возвращает пропущенные значения: NOT(balance = 0) для NULL тоже даёт неизвестный результат. Поэтому сначала сформулируйте, что делать с пропусками, а затем переводите это правило в SQL. Проверяйте не только найденные строки, но и те, которые исчезли из результата.

Ловушка NULL внутри NOT IN

Допустим, нужно исключить идентификатор 2, но в список исключений попал NULL. Даже выражение 1 NOT IN (2, NULL) даст неизвестный результат: SQL не может подтвердить, что 1 отличается от каждого элемента. В WHERE такая строка не пройдёт.

Для подзапроса с исключениями рассмотрите NOT EXISTS с понятным условием соответствия. Он проверяет отсутствие подходящей строки и не переносит NULL из списка значений. Но поведение отсутствующих идентификаторов всё равно нужно определить отдельно: замена конструкции не заменяет правило предметной области.

SELECT 1 NOT IN (2, NULL) AS result;

Откуда берутся пропуски после LEFT JOIN

LEFT JOIN сохраняет строки слева и подставляет NULL в поля справа, если соответствия нет. Последующий фильтр по правому полю в WHERE может удалить эти строки. Если нужны все счета, но справа только активные записи, ограничение правой таблицы обычно следует поместить в ON.

Перед COALESCE выясните причину пропуска: данные не собрали, поле неприменимо или запись не нашлась при соединении? Это разные ситуации, которым могут понадобиться разные обозначения. Практический порядок: посчитать строки, известные значения и пропуски; проверить соединения; только после этого выбирать замену для отчёта.