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

Вопросы про NULL на собеседовании похожи на минное поле: запрос выглядит правильно, а строки молча пропадают. Теорию трёхзначной логики ты прошёл в уроке про NULL и целостность, здесь собраны формулировки, которые реально задают, и короткие ответы на них. В прошлом уроке NULL уже повёл себя по-разному в DISTINCT и в count. Примеры на учебной базе блока: у Светы нет города, у Анны, Глеба и Егора нет руководителя, у Жанны нет отдела.

Как спрашивают

Что вернёт SELECT NULL = NULL? Как найти строки, где значение не заполнено? Почему запрос с NOT IN вернул пустой результат?

Короткий ответ

NULL означает «значение неизвестно», а не ноль и не пустую строку. Любое обычное сравнение с NULL даёт не true и не false, а NULL, то есть «неизвестно», и WHERE такую строку отбрасывает. Проверять пустоту нужно через IS NULL, заменять значение по умолчанию через COALESCE, а сравнивать с учётом NULL через IS DISTINCT FROM.

Разбор: анкета с прочерком

Представь две анкеты, в обеих графа «Город» осталась пустой. Живут ли эти люди в одном городе? Ты не знаешь: может быть, да, может быть, нет. Честный ответ «неизвестно». SQL отвечает так же:

SELECT NULL = NULL AS eq, 1 + NULL AS plus, 'a' || NULL AS concat;

eq   | plus | concat
-----+------+-------
NULL | NULL | NULL

Сравнение, арифметика и склейка строк с неизвестным дают неизвестное. Это правило заразности: NULL внутри выражения делает NULL всё выражение. Исключения есть только у логики: NULL AND false равно false, а NULL OR true равно true, потому что ответ уже не зависит от неизвестной части.

Вопрос 1: найди незаполненное

SELECT name FROM customers WHERE city = NULL;   -- пусто
SELECT name FROM customers WHERE city IS NULL;  -- Света

Первый запрос ничего не вернёт: для Светы city = NULL даёт «неизвестно», а WHERE оставляет только строки с true. Оператор IS NULL всегда отвечает true или false.

Вопрос 2: «не равно» теряет строки

Задача: все клиенты не из Казани.

SELECT name FROM customers WHERE city <> 'Казань';

name
-----
Павел

Света пропала. Её город неизвестен, значит, «не Казань» тоже неизвестно. Если по смыслу задачи Света должна попасть в ответ, это надо сказать явно. Два способа:

SELECT name FROM customers WHERE city <> 'Казань' OR city IS NULL;
SELECT name FROM customers WHERE city IS DISTINCT FROM 'Казань';

Оба вернут Павла и Свету. IS DISTINCT FROM работает как <>, но считает NULL обычным значением и никогда не отвечает «неизвестно». Он есть в стандарте SQL и в PostgreSQL.