NULL в SQL: IS NULL, COALESCE и типичные ловушки

Коротко

NULL — это не ноль и не пустая строка. Это «значение неизвестно или отсутствует». Раз значение неизвестно, SQL не может сказать, равно ли оно чему-либо — даже другому NULL. Поэтому:

SELECT * FROM users WHERE phone = NULL;   -- всегда пусто
SELECT * FROM users WHERE phone IS NULL;  -- правильно

Для проверок — IS NULL и IS NOT NULL, для подстановки — COALESCE, для защиты от пропусков — ограничение NOT NULL.

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

У условий в SQL три исхода: TRUE, FALSE и UNKNOWN. Любое сравнение с NULL (=, <>, >, <) даёт UNKNOWN. WHERE оставляет только строки, где условие TRUE, поэтому строки с UNKNOWN молча пропадают.

Типичный сюрприз: WHERE status <> 'blocked' не вернёт строки, где status равен NULL. Если они нужны, скажите это явно: WHERE status <> 'blocked' OR status IS NULL. В PostgreSQL и ряде других СУБД есть IS DISTINCT FROM, который сравнивает NULL как обычное значение.

NULL в агрегатах

• COUNT(*) считает строки. COUNT(column) считает только не-NULL значения. Разные цифры в одном отчёте часто объясняются именно этим.
• SUM и AVG игнорируют NULL. AVG(discount) усредняет только заказы, где скидка заполнена, а не все заказы.
• Если все значения NULL (или строк нет), SUM вернёт NULL, а не 0.

SELECT
  COUNT(*)                    AS orders,
  COUNT(discount)             AS orders_with_discount,
  AVG(discount)               AS avg_among_discounted,
  AVG(COALESCE(discount, 0))  AS avg_across_all_orders,
  COALESCE(SUM(discount), 0)  AS total_discount
FROM orders;

Какой AVG верный, зависит от бизнес-вопроса. Решайте это явно, а не оставляйте на волю NULL.

NULL в JOIN

• Ключи со значением NULL никогда не совпадают при соединении, даже NULL с NULL. Строки без manager_id ни к чему не присоединятся.
• LEFT JOIN заполняет отсутствующие колонки правой таблицы значением NULL. Если затем отфильтровать по колонке правой таблицы в WHERE (WHERE p.status = 'paid'), эти строки отбросятся, и LEFT JOIN незаметно превратится во внутреннее соединение. Чтобы сохранить все левые строки, переносите такие условия в ON.

Ловушка NOT IN

SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

Если в orders есть хотя бы один customer_id со значением NULL, запрос вернёт ноль строк: id <> NULL даёт UNKNOWN для каждого клиента. Используйте NOT EXISTS — он работает предсказуемо:

SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

NULL и ограничение UNIQUE

В большинстве СУБД (PostgreSQL, MySQL, SQLite) уникальная колонка может содержать много NULL, потому что NULL не считаются равными. SQL Server допускает в уникальном ограничении только один NULL. Если нужно «не больше одной строки без значения», используйте NOT NULL с умолчанием или специальную опцию СУБД (в свежих версиях PostgreSQL есть NULLS NOT DISTINCT).

Инструменты, которые чинят отчёты

• COALESCE(a, b, c) возвращает первое не-NULL значение: COALESCE(nickname, first_name, 'Гость').
• NULLIF(a, b) возвращает NULL, если a равно b. Классика — защита от деления на ноль: revenue / NULLIF(visits, 0).
• NOT NULL с DEFAULT в схеме: is_active BOOLEAN NOT NULL DEFAULT true, qty INTEGER NOT NULL DEFAULT 0. Лучшая обработка NULL — не хранить его там, где он не имеет смысла.
• Помните про строки: по стандарту 'a' || NULL даёт NULL, CONCAT в MySQL возвращает NULL, если любой аргумент NULL, а Oracle считает пустую строку равной NULL.
• Сортировка тоже различается: одни СУБД ставят NULL в начало, другие в конец. Где поддерживается, указывайте NULLS FIRST / NULLS LAST.

Чек-лист для новой таблицы

• Разрешайте NULL только там, где «неизвестно» — реальное состояние.
• Флагам, счётчикам и суммам задавайте NOT NULL с умолчанием.
• В отчётах для каждой метрики решите, что значит NULL: 0, «исключить» или «нет данных».
• Заменяйте NOT IN (подзапрос) на NOT EXISTS.