SQL IS NULL и IS NOT NULL

Урок 2.4 · Время чтения: ~6 мин · Задачи для практики ↓

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

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

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

SELECT rental_id, rental_date, return_date
FROM rental
WHERE return_date = NULL;

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

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

IS NULL

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

SELECT rental_id, rental_date, return_date
FROM rental
WHERE return_date IS NULL;

Результат: 183 строки - аренды, фильмы по которым ещё на руках.

IS NULL сочетается с другими условиями, как любое сравнение:

SELECT rental_id, rental_date, return_date
FROM rental
WHERE customer_id = 554 AND return_date IS NULL;

Результат: одна строка, аренда 14098 от 2005-08-21: этот фильм всё ещё у клиента 554.

IS NOT NULL

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

SELECT rental_id, rental_date, return_date
FROM rental
WHERE return_date IS NOT NULL;

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

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

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

SELECT rental_id, return_date
FROM rental
WHERE return_date >= '2005-08-01';

Результат: 8207 строк.

SELECT rental_id, return_date
FROM rental
WHERE NOT (return_date >= '2005-08-01');

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

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

SELECT rental_id, return_date
FROM rental
WHERE return_date >= '2005-08-01' OR return_date IS NULL;

Результат: 8390 строк = 8207 + 183.

Ловушка NOT IN

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

SELECT title, language_id
FROM film
WHERE language_id NOT IN (2, 3);

Результат: 1000 строк - все фильмы.

SELECT title, language_id
FROM film
WHERE language_id NOT IN (2, NULL);

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

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

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

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

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

Результат: 4 аренды; у аренды 14098 показано «not returned». Без имени столбец называется самим выражением.

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

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

• Любое сравнение с NULL (=, <>, >, <) даёт UNKNOWN, и WHERE отбрасывает такие строки.
• IS NULL находит строки без значения, IS NOT NULL - строки со значением; обе проверки сочетаются с AND и OR.
• Условие и его 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 для каждой строки со значением.

Можно ли сочетать IS NULL с AND и OR?

Да, как любое другое условие: WHERE customer_id = 554 AND return_date IS NULL находит фильмы, которые всё ещё у клиента, а OR return_date IS 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 отбрасываются и условием, и его отрицанием.