В 01-02 был тизер: NULL — это не «пусто», а «неизвестно». Здесь — расплата. В приложении Brew есть запрос «покажи напитки, которых сейчас нет в стоп-листе»: ... WHERE id NOT IN (SELECT drink_id FROM unavailable). Однажды он начал возвращать ноль напитков — будто недоступно всё меню. Ни ошибки, ни предупреждения. Причина: в таблицу unavailable затесался один NULL (внешний фид, LEFT JOIN, ручная правка — неважно), и этого хватило, чтобы NOT IN молча «выключил» весь результат.

Цель юнита — понять, почему так, и больше в это не попадать. Корень — трёхзначная логика: сравнение с NULL даёт не true/false, а NULL (UNKNOWN). А WHERE пропускает строку, только если условие именно true; NULL для него — то же, что false.

Трёхзначная логика: сравнение с NULL → NULL

NULL означает «неизвестно», поэтому любое сравнение с ним возвращает «неизвестно»: 1 = NULL — это не false, это NULL. NULL = NULL — тоже NULL (два неизвестных не обязаны быть равны). Поэтому проверять на NULL оператором = бессмысленно — для этого есть IS NULL / IS NOT NULL.

Для WHERE/CHECK/ON важно: они пропускают строку, только если предикат — true. Предикат NULL (UNKNOWN) трактуется как «не прошёл», ровно как false. Отсюда все ловушки.

Ловушка NOT IN + NULL

x IN (a, b, c) разворачивается в x = a OR x = b OR x = c. x NOT IN (a, b, c) — это его отрицание: x <> a AND x <> b AND x <> c. Теперь подставь NULL в список: x NOT IN (4, NULL) = x <> 4 AND x <> NULL. Сравнение x <> NULL — это NULL. А что-угодно AND NULL:

• если x = 4: false AND NULL = false → строка не проходит (это даже правильно);
• если x <> 4: true AND NULL = NULL → строка не проходит, хотя должна бы!

Итог: как только в списке NOT IN есть NULL, предикат не может стать true ни для одной строки — запрос возвращает пусто. Это не редкость и не «кривые данные»: подзапрос по nullable-колонке легко притаскивает NULL.

Лечение — NOT EXISTS: он спрашивает «существует ли совпадающая строка», и работает на уровне «есть/нет», а не на сравнении с NULL. Строка unavailable с NULL ни с каким drinks.id не совпадёт (u.drink_id = d.id при NULL даёт NULL → не совпадение), поэтому никого лишнего не исключит. NOT EXISTS (или <> ALL (... WHERE col IS NOT NULL)) — это правильный «NOT IN, устойчивый к NULL».

Три инструмента для работы с NULL

• COALESCE(a, b, c, ...) — первое не-NULL из списка. Классика — значение по умолчанию: COALESCE(nickname, name, 'аноним').
• NULLIF(a, b) — NULL, если a = b, иначе a. Частый приём — защита от деления на ноль: x / NULLIF(y, 0) вернёт NULL вместо ошибки, когда y = 0.
• IS DISTINCT FROM / IS NOT DISTINCT FROM — NULL-безопасные «не равно»/«равно». В отличие от =/<>, они трактуют NULL как обычное значение: NULL IS NOT DISTINCT FROM NULL = true, 1 IS DISTINCT FROM NULL = true.

Ловушка NOT IN и инструменты NULL

Вот почему один NULL в списке обнуляет ответ — раскладка по шагам:

WHERE id NOT IN (SELECT drink_id FROM unavailable)      -- список = {4, NULL}
 
  id NOT IN (4, NULL)
        │  раскрывается в отрицание IN
        ▼
  id <> 4  AND  id <> NULL  ←── id <> NULL ВСЕГДА = NULL (сравнение с «неизвестно»)
        │
        ├─ id = 4  :  false AND NULL = false  → не проходит (и это верно)
        └─ id <> 4 :  true  AND NULL = NULL   → НЕ проходит, хотя должна бы!
        │
        ▼
  ни одна строка не может стать true  →  результат пуст

Лечится переходом на NOT EXISTS (работает на «есть/нет», а не на сравнении с NULL) или явным WHERE col IS NOT NULL в подзапросе. А вот сводка инструментов для работы с NULL:

Что показывает наш код

NullLogic собирает четыре факта на литералах:

SELECT
    ((NULL = NULL) IS NULL)            AS eq_is_null,         -- (=) с NULL → NULL, не true
    (NULL IS NOT DISTINCT FROM NULL)   AS is_not_distinct,    -- NULL-безопасное равенство
    (NULLIF(100, 100) IS NULL)         AS nullif_eq_is_null,  -- NULLIF(a,a) → NULL
    COALESCE(NULL::int, NULL, 42)      AS coalesce_val;       -- первое не-NULL

А ловушку показываем на данных: список unavailable = {4, NULL} и один вопрос «сколько напитков доступно» двумя способами:

-- ловушка:   ... WHERE id NOT IN (SELECT drink_id FROM unavailable)
-- правильно: ... WHERE NOT EXISTS (SELECT 1 FROM unavailable u WHERE u.drink_id = d.id)

Запуск

Подними песочницу (из корня репозитория) и накати схему Brew + таблицу юнита:

docker compose up -d
make lecture L=03-crud-fluency/03-06-null-semantics-reckoning T=db-reset
make lecture L=03-crud-fluency/03-06-null-semantics-reckoning

(T=run — значение по умолчанию. Изнутри каталога юнита это make db-reset, make run.)

Вывод:

1) Трёхзначная логика NULL и инструменты:
   (NULL = NULL) IS NULL            = true   (= с NULL даёт NULL, не true)
   NULL IS NOT DISTINCT FROM NULL   = true   (NULL-безопасное равенство)
   NULLIF(100, 100) IS NULL         = true   (NULLIF → NULL, когда равны)
   COALESCE(NULL, NULL, 42)         = 42     (первое не-NULL)
 
2) Список недоступных напитков unavailable = {4, NULL} (NULL затесался по ошибке).
 
3) «Сколько напитков доступно?» — два способа:
   NOT IN (...)      → 0   ← ловушка: NULL в списке обнулил ответ
   NOT EXISTS (...)  → 4   ← правильно (5 напитков минус колд брю #4)

Тот же вопрос, те же данные — два разных ответа. NOT IN со списком, где есть NULL, вернул 0 (всё меню «недоступно»), а NOT EXISTS — честные 4 (пять напитков минус колд брю). Один NULL в источнике — и NOT IN молча соврал.

Заборчик

Лучшая защита от ловушки — не допускать NULL там, где он не нужен: NOT NULL на колонке (модуль 02) делает её невозможной в принципе. Что мы упростили:

• Ловушка NOT IN — самая известная, но не единственная. Трёхзначная логика всплывает везде, где есть nullable-колонки: WHERE, JOIN ... ON, CHECK, агрегаты (count(col) пропускает NULL, count(*) — нет, см. 01-02 и далее 04-03), DISTINCT (считает все NULL одинаковыми, в отличие от =).
• Стандартная гигиена в проде: ставь NOT NULL, где значение обязано быть; в подзапросах для NOT IN либо переходи на NOT EXISTS, либо явно отфильтруй WHERE col IS NOT NULL; для сравнения значений, которые могут быть NULL, бери IS DISTINCT FROM, а не <>.
• NULL — это «неизвестно», а не «ноль» и не «пустая строка». Смешивать их — отдельный источник багов.

Что забрать с собой

• NULL — это «неизвестно»: сравнение с ним (=, <>, <) даёт NULL (UNKNOWN), а не true/false. Проверяй через IS NULL.
• WHERE/JOIN ON/CHECK пропускают строку только при true; предикат NULL для них — как false.
• NOT IN (подзапрос с NULL) никогда не вернёт true → молча отдаёт пусто. Используй NOT EXISTS (или фильтруй NULL в подзапросе).
• COALESCE — значение по умолчанию; NULLIF(a,b) — NULL при равенстве (защита от деления на ноль); IS [NOT] DISTINCT FROM — NULL-безопасные сравнения.
• Где значение обязано быть — ставь NOT NULL: лучшая ловушка та, которую нельзя зарядить.

Это финал модуля 03 — «CRUD-беглость». Дальше — модуль 04 «Запросы по таблицам»: связываем данные JOIN'ами, агрегируем через GROUP BY/HAVING, берём «последнее на клиента» через DISTINCT ON, и снова встречаем ловушку NOT IN + NULL — теперь уже как часть выбора между EXISTS и IN.