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

NULL означает «значение неизвестно». Это не ноль, не пустая строка и не false. Любая операция с неизвестным даёт неизвестное, и на этом одном правиле держатся все ловушки урока. В данных инженера NULL встречается постоянно: необязательное поле, строка без пары после LEFT JOIN, значение, которое источник не прислал.

Три значения вместо двух

Сравнение в SQL возвращает не два значения, а три: TRUE, FALSE и NULL.

SELECT NULL = NULL   AS eq,      -- NULL
       NULL <> 1     AS ne,      -- NULL
       1 + NULL      AS plus,    -- NULL
       'a' || NULL   AS concat,  -- NULL
       NULL AND false AS and_f,  -- false
       NULL OR true   AS or_t;   -- true

Два неизвестных значения не равны друг другу: мы не знаем, что в них лежит. Логические операторы работают по здравому смыслу: NULL AND false даёт false, потому что при любом неизвестном результат всё равно ложен. NULL OR true по той же причине даёт true. Во всех остальных случаях неизвестность сохраняется.

Главное правило фильтрации: WHERE оставляет только строки, где условие равно TRUE. Строки с FALSE и строки с NULL отбрасываются одинаково, без ошибок и предупреждений.

Фильтр «не равно» теряет строки

В таблице заказов 8000 строк. Промокод SALE15 стоит у 381 заказа. Сколько заказов с другим промокодом или без него?

SELECT count(*) FROM orders WHERE promo_code <> 'SALE15';   -- 1183

Ожидали 7619, получили 1183. У 6436 заказов промокода нет, сравнение NULL <> 'SALE15' дало NULL, и фильтр их выбросил. Арифметика сходится: 381 + 1183 + 6436 = 8000.

Сравнение, которое считает NULL обычным значением, записывается так:

SELECT count(*) FROM orders WHERE promo_code IS DISTINCT FROM 'SALE15';   -- 7619

IS DISTINCT FROM и парный ему IS NOT DISTINCT FROM всегда возвращают TRUE или FALSE. Два NULL для них равны. Проверки на сам NULL пишутся только через IS NULL и IS NOT NULL: условие promo_code = NULL не истинно никогда.

NOT IN и один NULL в подзапросе

Сколько клиентов ни разу не появлялись в событиях сайта?

SELECT count(*) FROM customers
WHERE customer_id NOT IN (SELECT user_id FROM events);   -- 0

Ноль. На самом деле таких клиентов 154. В events.user_id есть NULL у анонимных посетителей, а x NOT IN (1, 2, NULL) разворачивается в x <> 1 AND x <> 2 AND x <> NULL. Последнее сравнение даёт NULL, всё выражение не может стать TRUE ни для одной строки.

SELECT count(*) FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM events e WHERE e.user_id = c.customer_id);   -- 154

NOT EXISTS проверяет, нашлась ли хоть одна строка, и от NULL не зависит. Это самая частая причина «пустого отчёта» в сверках: вчера в колонке не было NULL и NOT IN работал, сегодня один NULL приехал, и запрос молча вернул ноль строк. Для поиска отсутствующего используйте NOT EXISTS.

Агрегаты пропускают NULL

SELECT count(*)                   AS all_rows,     -- 8000
       count(total_amount)        AS with_amount,  -- 7916
       count(promo_code)          AS with_promo,   -- 1564
       count(DISTINCT promo_code) AS promo_kinds   -- 4
FROM orders;

count(*) считает строки, count(колонка) считает строки, где колонка не NULL. Остальные агрегаты тоже пропускают NULL, и это влияет на смысл:

SELECT round(avg(total_amount), 2)              AS avg_known,  -- 26800.00
       round(avg(coalesce(total_amount, 0)), 2) AS avg_zero    -- 26518.60
FROM orders;

Первая цифра отвечает на вопрос «средний чек среди заказов с известной суммой». Вторая считает неизвестную сумму нулём, что почти всегда неверно. Правильной по умолчанию нет ни одной: решение зависит от того, что NULL означает в ваших данных, и его надо принимать явно.

Ещё одна особенность: sum по пустому набору возвращает NULL, а не ноль. Если витрина должна показывать ноль за день без платежей, пишите coalesce(sum(amount), 0).

NULL в арифметике съедает выручку

Скидка в order_items.discount хранится долей, а NULL означает «скидки нет». Считаем выручку:

SELECT round(sum(quantity * unit_price * (1 - discount)), 2)              AS wrong,   --  57 648 643.86
       round(sum(quantity * unit_price * (1 - coalesce(discount, 0))), 2) AS correct  -- 213 267 572.47
FROM order_items;

Скидки нет у 12 638 позиций из 17 983. У каждой из них 1 - NULL дало NULL, вся строка стала NULL, и sum её пропустил. Выручка занижена почти в четыре раза, при этом запрос отработал без единого предупреждения. То же самое происходит при склейке строк: last_name || ' ' || middle_name превратит всё ФИО в NULL, если отчества нет.

Сортировка, группировка и JOIN

Сортировка. PostgreSQL считает NULL больше любого значения. При ORDER BY ... DESC они оказываются первыми:

SELECT order_id, total_amount FROM orders ORDER BY total_amount DESC LIMIT 5;
-- пять строк, и во всех total_amount IS NULL

«Топ-5 самых дорогих заказов» состоит из заказов без суммы. Исправляется явным указанием: ORDER BY total_amount DESC NULLS LAST. В других базах умолчания другие, поэтому в важных запросах пишите NULLS FIRST или NULLS LAST всегда.

Группировка. GROUP BY, DISTINCT и UNION считают все NULL одинаковыми. В разбивке клиентов по городам будет отдельная группа NULL на 46 человек. Это удобно: потерянные данные видно, а не спрятано.

JOIN. Условие соединения работает как обычное сравнение, поэтому NULL не соединяется ни с чем, даже с другим NULL:

SELECT count(*) FROM customers c JOIN cities ci ON ci.city = c.city;   -- 1454 из 1500

46 клиентов без города выпали из результата. Подробно потерянные строки разбираем в блоке про JOIN.

Сравнение старого и нового значения

Типичная задача загрузки: найти строки, которые изменились. Сравнение через <> пропускает переходы из значения в NULL и обратно. В истории клиентов смена города через <> находится 544 раза, а через IS DISTINCT FROM уже 562: 18 раз город сменился на «не указан».

WHERE new.city IS DISTINCT FROM old.city

В любом сравнении версий, в MERGE и в построении историчных таблиц используйте IS DISTINCT FROM. Альтернатива вроде coalesce(a, '') <> coalesce(b, '') работает, пока в данных не появится настоящая пустая строка.

Практика

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