NULL в SQL: почему запрос отрабатывает и молча отдаёт неверное число

В SQL деление на ноль Postgres остановит с ошибкой. Приведение строки «abc» к числу тоже. А деление на NULL пройдёт, и запрос вернёт результат. Просто не тот, которого вы ждали.

Это главная особенность работы с отсутствующими значениями: ошибок нет, есть тихо неправильные числа. Разбираем, откуда они берутся в фильтрах, в арифметике, в агрегатах и в соединениях, и какими операторами это лечится.

Отправная точка одна: NULL — не значение, а отметка «неизвестно». Неизвестность распространяется дальше по всему выражению: через сравнения, арифметику, склейку строк, агрегаты, оконные функции и условия отбора. Результат при этом строго определён правилами языка, он просто может не совпасть с тем, что вы имели в виду.

Три логических исхода вместо двух

Начнём с простого случая. В таблице клиентов у одного из них не заполнен телефон, и это прямо видно глазами. Запрос находит ноль строк:

			SELECT name FROM customers WHERE phone = NULL;   -- 0 строк
		

Условие phone = NULL спрашивает: «равно ли это неизвестное значение тому неизвестному значению?» Ответить на такое нельзя, поэтому для каждой строки возвращается неизвестность, включая ту самую строку с пустым телефоном. А отбор оставляет только строки, где условие истинно. Неизвестность не проходит ровно так же, как не прошла бы ложь.

Правило действует единообразно: NULL = NULL тоже не истина, а неизвестность. Отсюда и главный вывод — оператор равенства нельзя «подправить», чтобы он здесь заработал. Он не почти прав, он отвечает на другой вопрос.

В Postgres это проверяется одной строкой. Что вернёт такое выражение?

			SELECT (NULL = NULL) = (NULL != NULL);   -- NULL
		

Оба внутренних сравнения неизвестны, поэтому внешнее сравнивает неизвестность с неизвестностью и тоже даёт неизвестность. Операторы сравнения возвращают NULL, если хотя бы одна сторона неизвестна. Именно поэтому в языке есть отдельный оператор проверки на отсутствие: узнать, равны ли две неизвестности, нельзя, а узнать, является ли значение неизвестным, можно.

Как ведут себя И, ИЛИ и НЕ

Логические связки тоже работают в трёх значениях, и запомнить стоит два исключения:

			NULL = NULL              -- NULL
TRUE  OR  NULL           -- true    ИЛИ спасает истина справа
FALSE OR  NULL           -- NULL
TRUE  AND NULL           -- NULL
FALSE AND NULL           -- false   И спасает ложь справа
NOT NULL::boolean        -- NULL
NULL::boolean IS UNKNOWN -- true
		

То есть ИЛИ всё ещё истинно, если истинна вторая сторона, а И всё ещё ложно, если ложна вторая сторона. Всё остальное с участием неизвестности схлопывается в неизвестность.

Последствия неочевидны. Вот запрос, который отбрасывает строку, которую вы почти наверняка хотели оставить:

			CREATE TABLE flags (id int, active boolean);
INSERT INTO flags VALUES (1, true), (2, false), (3, NULL);

-- Вернутся только строки 1 и 2. Третья неизвестна, поэтому отфильтрована.
SELECT * FROM flags WHERE active OR NOT active;
		

В обычной двузначной логике «активно или не активно» — тавтология, утверждение, истинное при любом значении. В SQL это не так.

Операторы, которые никогда не возвращают неизвестность

Лечится это выходом из трёхзначной логики. Сначала решите, что NULL означает в вашей предметной области, а потом скажите это явно:

			SELECT * FROM flags WHERE COALESCE(active, false);
SELECT * FROM flags WHERE active IS NOT TRUE;         -- ложь и неизвестность
SELECT * FROM flags WHERE active IS UNKNOWN;          -- только неизвестность
SELECT * FROM flags WHERE active IS DISTINCT FROM true;
		

Проверки IS TRUE, IS NOT TRUE, IS FALSE, IS NOT FALSE и IS UNKNOWN никогда не возвращают неизвестность, поэтому строку не потеряют.

Аналог того же для равенства — IS NOT DISTINCT FROM. Обычное равенство спрашивает, известно ли, что значения совпадают. Этот оператор спрашивает, одно ли это значение, обращаясь с отсутствием как с полноценным состоянием. Две неизвестности неразличимы, поэтому предикат истинен.

Где это пригодится:
Соединение по условию равенства отбрасывает строки, где ключ отсутствует с обеих сторон. Если два отсутствующих ключа должны считаться совпадением, замените равенство на IS NOT DISTINCT FROM. То же касается проверок уникальности и условий отбора, где «отсутствует» должно означать «отсутствует», а не «неизвестно».

Ловушка NOT IN, из-за которой пропадает вся выдача

Это самый дорогой из всех эффектов, потому что он не сужает результат, а стирает его целиком. Операторы IN и NOT IN разворачиваются в цепочки сравнений, а NOT IN — в цепочку через И:

			x NOT IN (1, 2, NULL)
  ≡ x <> 1 AND x <> 2 AND x <> NULL

x = 1:  ложь AND истина AND неизвестность          → ложь
x = 5:  истина AND истина AND неизвестность        → неизвестность
		

Разберём оба исхода. Если значение совпало с одним из перечисленных, срабатывает то самое правило «И спасает ложь справа», и выражение честно ложно. Если не совпало ни с одним, множитель со сравнением с пустым значением делает всё выражение неизвестным.

Для условия отбора разницы между этими исходами нет: оно оставляет только истину, а ложь и неизвестность отбрасывает одинаково. Поэтому итог один — если в списке или в подзапросе есть хотя бы одно отсутствующее значение, NOT IN не вернёт ни одной строки. Никакой ошибки при этом не будет.

			CREATE TABLE products (id int, name text);
INSERT INTO products VALUES (1, 'widget'), (2, 'gadget'), (3, 'gizmo');

CREATE TABLE discontinued (product_id int);
INSERT INTO discontinued VALUES (2), (NULL);

-- Пусто. Товары 1 и 3 не сняты с производства,
-- но NOT IN не может этого доказать.
SELECT name FROM products
WHERE id NOT IN (SELECT product_id FROM discontinued);
		

Обычный IN ведёт себя мягче, поскольку разворачивается в цепочку через ИЛИ: совпадение остаётся истиной независимо от того, что ещё есть в списке. В простом условии отбора пустое значение в списке вообще ничего не меняет: несовпавшая строка отсеется что при неизвестности, что при лжи. Разница вылезает там, где результат сравнения используется дальше: под отрицанием, в выражении CASE или в ограничении целостности.

Надёжная замена — NOT EXISTS, который работает через равенство внутри условия отбора и потому никогда не трактует сравнение с отсутствующим значением как совпадение. Тот же смысл выражает антисоединение, которое вдобавок часто даёт лучший план:

			SELECT p.name
FROM products p
LEFT JOIN discontinued d ON d.product_id = p.id
WHERE d.product_id IS NULL;
		

Вычистить отсутствующие значения из подзапроса тоже можно, но это помогает, только если вы уверены, что их следует игнорировать. Вариант с NOT EXISTS делает это намерение явным, а не подразумеваемым.

Арифметика: одно значение NULL обнуляет строку

Любая арифметическая операция с участием отсутствующего значения даёт отсутствующее значение. Сложение, умножение, деление, модуль, возведение в степень — результат один.

В отчётах это выглядит как пустые ячейки, а в обновлениях данных как стёртые колонки. Классический пример, который каждый когда-нибудь писал:

			UPDATE orders
SET total = quantity * unit_price;   -- total станет NULL,
                                     -- если пуст хотя бы один множитель
		

Подставлять значение по умолчанию нужно в той точке, где вам известно правило предметной области, а не механически везде. В разборе Кристофера Уинслетта из Crunchy Data приводится удачная иллюстрация: отсутствующее количество почти всегда означает ноль, отсутствующая ставка налога тоже, а вот отсутствующая цена нулём не является и должна оставаться неизвестной, пока её кто-нибудь не заполнит.

Агрегаты и оконные функции считают не то, что кажется

Здесь отсутствующие значения ведут себя иначе, чем везде: агрегаты их пропускают. Функция COUNT(*) считает строки, а COUNT(колонка) — только непустые значения; SUM, AVG, MIN и MAX пропускают пустые входы.

			product_id | rows | rated | avg_rating | sum_rating
-----------+------+-------+------------+-----------
         1 |    3 |     2 |     3.0000 |          6
         2 |    2 |     0 |            |
		

У товара с двумя незаполненными оценками среднее и сумма пусты, а число строк равно двум. Отсюда практическое правило, которое экономит часы разбирательств с аналитикой: среднее — это сумма, делённая на количество непустых значений, а не на количество строк. Смешивать SUM(x) / COUNT(*) и ожидать совпадения со средним нельзя.

Оговорка про типы тоже важна: если сумма и счётчик целочисленные, деление усечёт дробную часть. Шесть, делённое на два, случайно даст ровно три, а оценки 5, 1 и 2 дадут при целочисленном делении двойку вместо 2,67.

Где ломаются оконные функции

Суммирование и усреднение в окне пропускают отсутствующие значения так же, как при группировке, а ROW_NUMBER считает строку в любом случае. Расхождение начинается у функций, которые смотрят в конкретную позицию окна: LAG, LEAD, FIRST_VALUE, LAST_VALUE и NTH_VALUE.

Такая функция возвращает то, что лежит в запрошенной позиции. Если там пусто, результат пуст. Ближайшее реальное значение она не ищет, и на рядах измерений с пропущенным отсчётом это даёт разрывы там, где ожидалась непрерывность.

Проверка чужого запроса, включая сгенерированный

Всё описанное складывается в одну проблему: запрос, который отработал, прошёл только проверку грамматики. База ловит опечатку в имени таблицы. Она не ловит суммирование не той колонки, соединение, задваивающее строки, и фильтр, поставленный не на том этапе. Каждая из этих ошибок — синтаксически корректный SQL.

Со сгенерированными запросами есть отдельная сложность: они беглые. Псевдонимы аккуратные, форматирование чистое, форма выглядит как работа внимательного человека. Беглость читается как правильность, хотя это разные вещи.

Проверка первая: посчитайте строки до того, как поверите сумме

Задача звучала как «чистая выручка по завершённым заказам». Полученный запрос:

			SELECT SUM(o.amount) - SUM(COALESCE(r.refund_amount, 0)) AS net_revenue
FROM orders o
LEFT JOIN refunds r ON r.order_id = o.order_id
WHERE o.status = 'completed';
		

Он отрабатывает и возвращает 1830. Правильный ответ 1330, и это легко проверить руками: валовая сумма одиннадцати завершённых заказов равна 1605, возвраты составляют 275.

Виновато соединение. Два заказа возвращались частями, по две записи на каждый, поэтому одиннадцать строк превращаются в тринадцать, а сумма по заказам считает эти два заказа дважды: 2105 вместо 1605. Лишние 500 — это ровно стоимость задвоенных заказов. Явление называется размножением строк: соединение множит строки всякий раз, когда ключ на другой стороне встречается больше одного раза.

Проверка стоит двух запросов:

			SELECT COUNT(*) FROM orders WHERE status = 'completed';        -- 11

SELECT COUNT(*)
FROM orders o LEFT JOIN refunds r ON r.order_id = o.order_id
WHERE o.status = 'completed';                                  -- 13
		

Одно это сравнение всё решает. Второе число выросло, значит, соединение размножило строки, и любая сумма или среднее по колонкам левой таблицы под подозрением. Число совпало — соединение безопасно, идём дальше.

Проверка вторая: ищите отсутствующие значения в каждом фильтре

Следующая просьба звучала как «та же выручка, но без служебных аккаунтов», и запрос получился такой:

			SELECT SUM(amount)
FROM orders
WHERE status = 'completed'
  AND customer_id NOT IN (SELECT customer_id FROM staff_accounts);
		

Он возвращает пустоту, полученную из нуля строк. Не меньшее число, а вообще ничего. Механизм вы уже знаете: в справочнике служебных аккаунтов есть одна строка с незаполненным идентификатором, а справочники в реальной жизни такими и бывают. Одна такая строка бесшумно опустошает результат целиком.

Что забрать с собой

Правила про отсутствующие значения не сложны, они просто действуют не там, где их ждут. Из фильтра строка пропадает молча, арифметика превращает результат в пустоту, соединение по равенству теряет пары без ключа, а среднее делит на другой знаменатель. Ошибки при этом нигде не возникает, и потому такие дефекты живут в отчётах годами.

Самый надёжный способ не разбираться со всем этим каждый раз — не хранить отсутствующие значения там, где они не нужны. Если колонка обязана иметь значение, скажите это в схеме. Ограничение NOT NULL выглядит формальностью ровно до первого расследования, почему выручка в отчёте оказалась на пятьсот единиц больше настоящей.

Материалы разбора: почему сравнение с NULL никогда не срабатывает, поведение NULL в вычислениях Postgres и проверка сгенерированного SQL до того, как поверить числу.

Откройте последний отчёт, который вы отдавали наружу, и сравните в нём количество строк до и после соединений. Проверка занимает пять минут и иногда меняет цифру, на которую уже сослались в переписке.