Урок 2.4 · Время чтения: ~6 мин

IS NULL и IS NOT NULL — единственный надёжный способ отбирать пропущенные значения в SQL. В этом уроке вы увидите, почему WHERE column = NULL ничего не находит, как найти строки со значением и без него и как NULL незаметно выбрасывает строки из условий с NOT, <> и NOT IN. Примеры используют таблицу rental базы Sakila: в ней return_date равен NULL у фильмов, которые ещё не вернули.

SQL IS NULL и IS NOT NULL

Почему = NULL ничего не находит

NULL означает «значение неизвестно». Любое сравнение с неизвестным значением тоже неизвестно: return_date = NULL — не истина и не ложь, а UNKNOWN. WHERE оставляет только строки, где условие равно TRUE, поэтому запрос ничего не возвращает:

SELECT COUNT(*) AS not_returned FROM rental WHERE return_date = NULL;

Результат: 0, хотя у 183 аренд нет даты возврата.

То же самое происходит с <>, >, < и любым другим оператором сравнения.

IS NULL

IS NULL — специальная проверка, которая равна TRUE, когда значения нет:

SELECT COUNT(*) AS not_returned FROM rental WHERE return_date IS NULL;

Результат: 183.

SELECT rental_id, rental_date, customer_id
FROM rental
WHERE return_date IS NULL
ORDER BY rental_date
LIMIT 3;

Результат: аренды 14098 (клиент 554), 13421 (клиент 366) и 15542 (клиент 111) — самые давние фильмы, которые всё ещё на руках.

IS NOT NULL

IS NOT NULL — обратная проверка: TRUE, когда в столбце есть значение.

SELECT COUNT(*) AS returned FROM rental WHERE return_date IS NOT NULL;

Результат: 15861. Вместе со 183 арендами без даты возврата это все 16044 строки.

NULL выпадает и из условия, и из его NOT

Кажется естественным, что условие и его NOT делят таблицу на две части. С NULL это не так:

SELECT
    (SELECT COUNT(*) FROM rental WHERE return_date >= '2005-08-01') AS after_august,
    (SELECT COUNT(*) FROM rental WHERE NOT (return_date >= '2005-08-01')) AS before_august,
    (SELECT COUNT(*) FROM rental) AS total;

Результат: 8207 + 7654 = 15861, а не 16044. Для 183 строк без даты возврата оба условия равны UNKNOWN, а NOT UNKNOWN — тоже UNKNOWN.

Чтобы сохранить эти строки, запросите их явно через OR ... IS NULL:

SELECT COUNT(*) AS late_or_unknown
FROM rental
WHERE return_date >= '2005-08-01' OR return_date IS NULL;

Результат: 8390 = 8207 + 183.

Ловушка NOT IN

NOT IN — это цепочка сравнений <>, соединённых через AND. Если в списке есть NULL, одно из них всегда равно UNKNOWN, и ни одна строка не проходит:

SELECT COUNT(*) AS films FROM film WHERE language_id NOT IN (2, 3);

Результат: 1000.

SELECT COUNT(*) AS films FROM film WHERE language_id NOT IN (2, NULL);

Результат: 0 — language_id <> NULL равно UNKNOWN для каждого фильма.

Вручную NULL в список никто не пишет, но список часто берётся из столбца, и одного NULL там достаточно, чтобы результат опустел. Перед NOT IN убедитесь, что значения не могут быть NULL.

Значение вместо NULL

COALESCE возвращает первый из своих аргументов, который не равен NULL. Это удобно, чтобы показать в результате понятное значение:

SELECT rental_id, rental_date, COALESCE(return_date, 'not returned') AS returned_at
FROM rental
WHERE customer_id = 554 AND rental_date >= '2005-08-21'
ORDER BY rental_date;

Результат: 4 аренды; у первой, 14098, показано «not returned».

Это MySQL. В PostgreSQL и SQL Server оба аргумента должны быть одного типа, поэтому там дату сначала переводят в текст. COALESCE и другие функции разбираются в модуле о функциях.

Ключевые выводы:

• Любое сравнение с NULL (=, <>, >, <) даёт UNKNOWN, и WHERE отбрасывает такие строки.
• IS NULL находит строки без значения, IS NOT NULL — строки со значением.
• Условие и его NOT вместе не покрывают строки, где столбец равен NULL; чтобы их сохранить, добавьте OR column IS NULL.
• NOT IN не возвращает ни одной строки, если в списке есть NULL.
• COALESCE(column, value) показывает значение-замену вместо NULL.

Часто задаваемые вопросы

Почему WHERE column = NULL не возвращает строк?

Сравнение с NULL даёт UNKNOWN, а не TRUE, а WHERE оставляет только строки с TRUE. Используйте WHERE column IS NULL.

IS NOT NULL — это то же самое, что NOT (column = NULL)?

Нет. column = NULL равно UNKNOWN, NOT UNKNOWN — тоже UNKNOWN, поэтому NOT (column = NULL) тоже ничего не находит. IS NOT NULL равно TRUE для каждой строки со значением.

Считает ли COUNT(column) значения NULL?

Нет. COUNT(*) считает строки, а COUNT(column) — только значения столбца, не равные NULL. Агрегатные функции разбираются в отдельном модуле.

Вопросы на собеседовании

Почему в SQL пишут IS NULL, а не = NULL?

NULL означает неизвестное значение, и сравнение чего угодно с неизвестным даёт UNKNOWN, а не TRUE или FALSE. WHERE оставляет только строки, где условие равно TRUE, поэтому = NULL никогда не срабатывает. IS NULL — отдельная проверка, которая возвращает TRUE или FALSE.

Почему NOT IN может вернуть пустой результат?

x NOT IN (a, b) означает x <> a AND x <> b. Если в списке есть NULL, x <> NULL равно UNKNOWN для каждой строки, всё выражение с AND не может быть TRUE, и строк нет. Так бывает, когда список берётся из подзапроса, в столбце которого есть NULL.

Как сохранить строки с NULL в фильтре?

Добавьте случай NULL явно: WHERE return_date >= '2005-08-01' OR return_date IS NULL. Иначе строки с NULL отбрасываются и условием, и его отрицанием.