Как NULL ведёт себя в сравнениях, агрегатах и JOIN?

NULL — не значение, а его отсутствие. Один этот сдвиг в голове снимает половину «необъяснимых» багов в отчётах, где строки таинственно пропадают или суммы не сходятся.

Как рассуждать

Замени в уме слово NULL на «неизвестно» и перечитай запрос. length > 100, если длина неизвестна, — это «неизвестно > 100», то есть неизвестно. Не TRUE и не FALSE, а третье состояние — UNKNOWN. Отсюда трёхзначная логика.

Дальше всё выводится:

• Сравнение с NULL всегда UNKNOWN, поэтому x = NULL не работает — только IS NULL.
• WHERE пропускает строку, только если условие TRUE. UNKNOWN — не TRUE, значит строка отваливается.
• Агрегаты sum/avg/count(col) игнорируют NULL, а count(*) считает строки целиком.
• В JOIN два NULL не равны друг другу — пары не будет.

Эталонный ответ

NULL означает «значение неизвестно». Ключевые следствия по областям.

Сравнения. NULL = NULL даёт не TRUE, а UNKNOWN. Поэтому проверка на пропуск — только IS NULL / IS NOT NULL:

SELECT count(*) FROM rental WHERE return_date IS NULL;  -- не возвращённые: 183

Агрегаты. avg(col) делит сумму на число НЕпустых значений, а не на все строки. Если считаешь среднее и часть значений NULL — среднее будет по имеющимся. count(return_date) посчитает только возвраты, count(*) — все аренды. Расхождение этих двух чисел — как раз количество NULL.

JOIN. Строки, где ключ соединения NULL, не склеятся: условие a.key = b.key при NULL — UNKNOWN. Поэтому нельзя джойнить по «пустым» полям в надежде, что «пусто совпадёт с пусто».

CASE и фильтры. NOT IN (подзапрос со NULL) — коварная штука: один NULL в списке делает весь результат UNKNOWN, и запрос молча возвращает пусто. Лечится NOT EXISTS или отсевом NULL в подзапросе.

Сильный ответ добавит про инструменты нормализации: COALESCE(x, 0) подставит значение вместо NULL (например, чтобы фильмы без аренд считались с нулевой выручкой), а NULLIF(a, b) вернёт NULL при равенстве — классика для защиты от деления на ноль.

Частые ошибки

• Пишут WHERE col = NULL и не понимают, почему пусто. Только IS NULL.
• Думают, что NULL — это 0 или ''. Ноль участвует в арифметике, а NULL + 5 = NULL.
• Забывают ловушку NOT IN со NULL: результат схлопывается в пусто без единой ошибки.
• Ждут, что NULL совпадёт при соединении, и теряют строки. Это же — причина сюрпризов при фильтре правой таблицы после LEFT JOIN.
• Путают count(*) и count(col) при подсчёте — и получают разные числа, не понимая, что виноваты пропуски.