NULL в SQL: IS NULL, COALESCE и частые ошибки

NULL в SQL означает отсутствие значения. Это не ноль и не пустая строка. Обычное сравнение с NULL, включая NULL = NULL, возвращает UNKNOWN, поэтому в WHERE используют IS NULL или IS NOT NULL. Запрос WHERE email = NULL синтаксически верен, но не возвращает строк.

Дальше всё на одном наборе данных, результаты сняты с PostgreSQL 16.11. Таблицы ниже подписаны читаемо: пустое значение обозначено словом NULL, логические записаны как true и false. Сам psql печатает на их месте пробел, t и f. Пробел вместо NULL и есть первая причина, по которой пропуск путают с пустой строкой.

users с колонкой email, заполненной по-разному нарочно: у Бориса значения нет, у Веры записана пустая строка.

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

Заказов у Веры нет вовсе: этот факт понадобится в разделе про NOT IN.

SQL / 01koddo

WHERE пропускает только true

Чем NULL отличается от нуля и пустой строки

Ноль и пустая строка — это записанные значения. NULL означает, что значения нет. Разницу видно сразу, если спросить у трёх колонок одно и то же:

SELECT id, name,
       email = ''       AS is_empty,
       email IS NULL    AS is_null,
       length(email)    AS len
FROM users
ORDER BY id;

Строка Бориса отвечает NULL даже на вопрос «пустая ли строка». У Веры пустая строка длиной 0: значение записано, просто в нём ноль символов. length(NULL) тоже даёт NULL, потому что длину неизвестного текста посчитать нельзя.

Практический вывод для схемы: пустая строка и NULL в одной колонке — это два способа сказать «email не указан», и любой отчёт по такой колонке придётся чинить дважды. Выбирают что-то одно и закрепляют ограничением.

Отдельно про Oracle: по его документации пустая строка и NULL там одно и то же, '' при вставке превращается в NULL. Код, перенесённый из Oracle в PostgreSQL или обратно, ломается ровно на этом месте.

Как проверить NULL в SQL: IS NULL и IS NOT NULL

Потому что NULL не равен ничему, включая самого себя. Сравнение с ним не даёт ни истины, ни лжи, оно даёт третий результат, UNKNOWN. А WHERE оставляет только те строки, где условие истинно.

SELECT id, name, email FROM users WHERE email = NULL;

(0 rows)

Ошибки нет, предупреждения нет, результат пустой. Правильно проверяют оператором IS NULL:

SELECT id, name, email FROM users WHERE email IS NULL ORDER BY id;

Обратная проверка IS NOT NULL даёт три строки, и Вера среди них: её пустая строка — записанное значение.

SELECT id, name, email FROM users WHERE email IS NOT NULL ORDER BY id;

Если нужны «клиенты без почты» в человеческом смысле, оба случая ловят одним условием: WHERE nullif(email, '') IS NULL. Функция nullif(a, b) возвращает NULL, когда аргументы равны, и первый аргумент во всех остальных случаях. То есть превращает пустую строку в пропуск и сводит два состояния к одному.

Трёхзначная логика: TRUE, FALSE и UNKNOWN

В SQL у логического выражения три исхода, а не два. Любая операция, в которую попал NULL, возвращает UNKNOWN, кроме случаев, когда результат определён и без неизвестного операнда:

false AND NULL даёт false, потому что ложь слева решает исход независимо от того, что справа. По той же причине true OR NULL даёт true. Остальные строки неизвестны.

Дальше работает одно правило, из которого растут почти все ошибки с NULL: WHERE пропускает строку, только если условие истинно. UNKNOWN для него равен отказу. Отсюда неочевидное поведение отрицания:

SELECT id, name, email FROM users
WHERE NOT (email = 'anya@example.com')
ORDER BY id;

Запрос «все, кроме Ани» вернул двоих из трёх оставшихся. Борис выпал: его email = 'anya@example.com' дал UNKNOWN, отрицание оставило UNKNOWN, и WHERE строку отбросил. То же самое происходит с обычным неравенством:

SELECT id, discount FROM orders WHERE discount <> 300 ORDER BY id;

Одна строка вместо пяти. Четыре заказа без скидки в ответ не попали, хотя их скидка точно не равна 300. Лечится либо явным добором OR discount IS NULL, либо оператором IS DISTINCT FROM, который сравнивает с учётом NULL и никогда не возвращает UNKNOWN:

SELECT id, discount FROM orders WHERE discount IS DISTINCT FROM 300 ORDER BY id;

Пять строк, как и ожидалось. Зеркальная форма IS NOT DISTINCT FROM работает как равенство, при котором NULL равен NULL.

Почему amount - discount пустеет

Арифметика с неизвестным даёт неизвестное. Одна пустая ячейка опустошает весь результат, и это не ноль, а снова NULL:

SELECT id, amount, discount, amount - discount AS to_pay
FROM orders
ORDER BY id;

Четыре заказа к оплате потеряли сумму. С точки зрения базы всё честно: если скидка неизвестна, то и итог неизвестен. С точки зрения отчёта это дыра, и закрывает её coalesce: функция возвращает первый аргумент, который не пустой.

SELECT id, amount, discount, amount - coalesce(discount, 0) AS to_pay
FROM orders
ORDER BY id;

coalesce принимает сколько угодно аргументов и берёт первый непустой: coalesce(discount, promo, 0). Ставить его нужно осознанно: заменив пропуск на ноль, ты утверждаешь, что скидки не было. Если скидку просто не выгрузили, ноль станет ложью, которую потом никто не найдёт.

Со строками тот же эффект, но с развилкой. Оператор || заражает результат, а функция concat считает NULL пустой строкой:

SELECT id, name,
       name || ' <' || email || '>' AS contact,
       concat(name, ' <', email, '>') AS contact_concat
FROM users
ORDER BY id;

Строка Бориса исчезла целиком при склейке через ||, хотя имя у него есть. Так пропадают адреса в письмах и подписи в выгрузках: одно незаполненное поле съедает весь текст.

Как NULL ведёт себя в count, sum и avg

Агрегаты пропускают NULL, а не считают его нулём. Разница между «пропустить» и «взять за ноль» видна на среднем:

SELECT count(*)                    AS rows_total,
       count(discount)             AS with_discount,
       sum(discount)               AS sum_discount,
       round(avg(discount), 2)     AS avg_discount,
       round(avg(coalesce(discount, 0)), 2) AS avg_with_zero
FROM orders;

count(*) считает строки и в значения не заглядывает: шесть. count(discount) считает только заполненные: две. avg делит сумму 400 на количество непустых значений, то есть на 2, и даёт 200. Стоит подставить нули, и делитель становится 6, а средняя скидка падает до 66.67. Оба числа верны, вопрос в том, какой смысл нужен: «средняя скидка среди заказов со скидкой» или «средняя скидка на заказ».

Второй сюрприз даёт сумма по набору, где нет ни одного значения:

SELECT sum(discount) AS s, count(discount) AS c, coalesce(sum(discount), 0) AS s0
FROM orders WHERE status = 'cancelled';

sum по пустому набору возвращает NULL, а не ноль. Это самая частая причина пустых ячеек в отчётах. count в той же ситуации честно даёт 0, потому что считать строки можно всегда.

Тот же NULL рождается на соединении, даже когда в исходных таблицах его нет:

SELECT u.name, sum(o.amount) AS total, coalesce(sum(o.amount), 0) AS total0
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name
ORDER BY u.name;

У Веры заказов нет, LEFT JOIN подставил ей пустое значение, и сумма получилась пустой. У того же соединения есть и другие ловушки: фильтр в WHERE вместо ON и count(*), который засчитывает клиента без заказов за одного. Обе разобраны в статье про LEFT JOIN.

Почему NOT IN возвращает пустоту

Один пропуск в подзапросе стирает весь результат NOT IN. Возьмём сырую выгрузку заказов, в которой одна строка не сматчилась с клиентом:

SELECT name FROM users
WHERE id NOT IN (SELECT user_id FROM orders_import)
ORDER BY name;

(0 rows)

Ожидали Веру, получили ничего. NOT IN раскрывается в цепочку id <> 1 AND id <> 2 AND ... AND id <> NULL. Последнее сравнение всегда UNKNOWN, а UNKNOWN в цепочке через AND не даёт ей стать истинной ни для одной строки. Тот же список без NOT работает нормально:

SELECT name FROM users
WHERE id IN (SELECT user_id FROM orders_import)
ORDER BY name;

Разница в том, что IN соединяется через OR: одного истинного сравнения хватает, и UNKNOWN рядом ни на что не влияет. Безопасная замена для отрицания — NOT EXISTS. Он проверяет наличие строк, а не сравнивает значения:

SELECT u.name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders_import o WHERE o.user_id = u.id)
ORDER BY u.name;

Дело не в PostgreSQL. На MySQL 8.4.11 запрос SELECT 1 WHERE 1 NOT IN (2, NULL) тоже возвращает ноль строк, хотя единица явно не двойка. Так предписывает стандарт, и менять это поведение движки не станут.

Правило короткое: NOT IN с подзапросом безопасен только тогда, когда колонка объявлена NOT NULL. Во всех остальных случаях бери NOT EXISTS или антиджойн. Чем эти формы различаются по стоимости, разобрано в статье про подзапросы.

Куда попадают NULL при сортировке

В PostgreSQL пустые значения считаются больше любых заполненных, поэтому при ASC они уезжают в конец, а при DESC встают в начало. Остальные правила сортировки результата разобраны отдельно.

SELECT id, discount FROM orders ORDER BY discount;

SELECT id, discount FROM orders ORDER BY discount DESC;

Порядок строк внутри блока NULL здесь не гарантирован: значения равны, а второго ключа сортировки нет. Если такой блок попадает в выгрузку с постраничным выводом, строки начнут перескакивать между страницами, и лечится это добавлением id вторым ключом.

Место NULL задаётся явно, и от направления сортировки оно тогда не зависит:

SELECT id, discount FROM orders ORDER BY discount ASC NULLS FIRST;

Умолчание у баз разное, и разница зеркальная. Тот же набор скидок на MySQL 8.4.11 при ORDER BY discount выдал сначала четыре NULL, а потом 100 и 300, то есть ровно наоборот к PostgreSQL. Запрос при переезде не падает и ничего не сообщает, просто отчёт начинается с другого конца.

Сама запись NULLS FIRST переносится не везде. MySQL 8.4.11 на неё отвечает отказом:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'NULLS FIRST'

Что означает эта формулировка и почему опечатку надо искать перед указанным местом, разобрано в статье про ошибку синтаксиса SQL. Переносимый способ управлять порядком — сортировать сначала по признаку пустоты: ORDER BY discount IS NULL, discount кладёт NULL в конец, потому что false меньше true.

Где NULL всё-таки равен NULL

Правило «NULL не равен ничему» относится к сравнениям. Там, где база группирует значения, она считает все пропуски одинаковыми, иначе группировка по колонке с незаполненными значениями разваливалась бы на отдельные строки.

SELECT discount, count(*) AS cnt
FROM orders
GROUP BY discount
ORDER BY discount NULLS LAST;

Четыре пустые скидки собрались в одну группу. Так же ведёт себя DISTINCT: на этих данных он вернёт три значения, одно из которых пустое. Остальные тонкости группировки разобраны в статье про GROUP BY и HAVING: почему count(*) и count(колонка) расходятся и как подписать безымянную группу в отчёте.

Два ограничения работают наоборот и пропускают NULL мимо себя. Уникальный индекс не считает пустые значения дубликатами:

CREATE TABLE promo (code text UNIQUE);
INSERT INTO promo VALUES (NULL), (NULL), (NULL);

INSERT 0 3

Три пропуска в колонке с UNIQUE, и ни одной ошибки. По той же логике CHECK (v > 0) пропускает такую строку: условие даёт UNKNOWN, а ограничение отклоняет только явную ложь. Строку со значением -1 та же проверка отвергает сразу. Чтобы колонка действительно была заполнена, нужен NOT NULL, а не проверка значения.

На чём ловят на собеседовании

«CASE discount WHEN NULL THEN ... поймает пустые». Не поймает. Короткая форма CASE сравнивает через =, а сравнение с NULL истинным не бывает, поэтому ветка не сработает ни разу и все строки уйдут в ELSE. Проверять надо длинной формой, CASE WHEN discount IS NULL THEN ....

«count(*) и count(колонка) — одно и то же». Расходятся ровно на числе пустых значений: на нашей таблице 6 против 2.

«sum по пустому набору вернёт 0». Вернёт NULL. Ноль даст только count.

«Раз NULL не равен NULL, GROUP BY разложит пропуски по отдельным группам». Соберёт в одну. Сравнение и группировка живут по разным правилам.

«UNIQUE не даст записать два пустых значения». Даст сколько угодно. Единственность проверяется между заполненными значениями.

«Пустая строка и NULL — синонимы». В Oracle да, по его документации. В PostgreSQL 16.11 и MySQL 8.4.11 нет: '' IS NULL в обоих отвечает false.

Частые вопросы

Как заменить NULL на 0 в результате запроса

Функцией coalesce: coalesce(discount, 0) для колонки и coalesce(sum(amount), 0) для агрегата. Она возвращает первый непустой аргумент и работает во всех популярных базах. У MySQL есть ещё короткий IFNULL, у SQL Server свой ISNULL, но обе функции привязаны к своему движку, а coalesce переносится без правок.

Чем coalesce отличается от nullif

Направлением. coalesce убирает NULL, подставляя запасное значение. nullif делает NULL из значения: nullif(email, '') превращает пустую строку в пропуск. Вместе они приводят колонку к одному виду: coalesce(nullif(email, ''), 'не указан') покрывает и пропуск, и пустую строку.

Почему NULL = NULL не истина

Потому что NULL — это «неизвестно», а два неизвестных значения могут оказаться и равными, и разными. База отказывается угадывать и отвечает UNKNOWN. Если нужно сравнение, при котором пропуски считаются равными, используй IS NOT DISTINCT FROM.

Стоит ли запрещать NULL во всех колонках

Нет. NOT NULL уместен там, где отсутствие значения означает битую строку: идентификатор, дата создания, статус. Там, где пропуск — это факт («дата отмены у неотменённого заказа»), NULL честнее заглушки. Подменять его нулём или строкой 'нет' дороже. Такие метки потом участвуют в агрегатах и портят цифры молча. Как схема вообще приходит к таким колонкам, разобрано в статье про нормализацию.

Где потренироваться

В пути «SQL для аналитиков» на Koddo пропуски встречаются в паках про соединения и агрегаты: сначала находишь клиентов без заказов, потом закрываешь пустые суммы через coalesce. Проверить себя можно на задаче про повторные отклики: там нужно сначала отсеять записи без email, а уже потом считать группы, и порядок этих двух шагов решает ответ.