Принято считать, что оператор NOT IN в SQL устроен так же прямолинейно, как вычёркивание по списку: если нужного номера нет среди значений, строка проходит фильтр. С фиксированным набором констант вроде status NOT IN ('draft', 'archived') эта интуиция не подводит. Но стоит передать в скобки подзапрос к таблице, где хотя бы в одной строке встретился маркер NULL, как логика рушится: база без единой ошибки в логах возвращает пустую выборку.
Я обратил внимание на разбор этой ошибки в канале SQL Portal. Допустим, нужно выгрузить из таблицы техники безопасности safety имена сотрудников (employee_name), чьи идентификаторы id не встречаются в столбце потерянных дней days_lost. В базе сотни подходящих записей, но одна ячейка NULL в days_lost полностью обнуляет отчёт. Почему один неизвестный элемент перечёркивает выборку и как защитить запрос от пустых значений?
Как трехзначная логика превращает отрицание в неизвестность
Причина кроется в том, как реляционные СУБД разворачивают проверку множества. В обычных языках булева алгебра двухзначна: условие либо истинно (TRUE), либо ложно (FALSE). Но стандарт SQL со времён SQL:1992 опирается на трёхзначную логику (Three-Valued Logic), где есть третье состояние — UNKNOWN (неизвестность), а маркер NULL означает «значение отсутствует».
Когда ты пишешь id NOT IN (SELECT days_lost FROM safety), движок базы — как указано в документации PostgreSQL — переводит конструкцию в цепочку неравенств id <> ALL (...), соединённых через AND: id <> s_1 AND id <> s_2 AND ... AND id <> NULL.
Сравнить число с NULL — всё равно что спросить, правда ли человек за запертой дверью не выше 180 см. Ответить ни «да», ни «нет» нельзя, поэтому проверка id <> NULL даёт UNKNOWN. В цепочке AND одно неизвестное звено заражает всё выражение: TRUE AND UNKNOWN превращается в UNKNOWN. Поскольку фильтр WHERE пропускает только строки с результатом TRUE (об этом мы говорили в обзоре архитектуры PostgreSQL и логики NULL), база отбрасывает каждую запись.
Фильтрация через IS NOT NULL и переход на двухзначный NOT EXISTS
Чтобы спасти отчёт по таблице safety, нужно либо отсечь пустые ячейки в подзапросе, либо сменить принцип проверки. Сравним уязвимый поиск сотрудников (employee_name), чей id не числится в days_lost, с двумя рабочими решениями:
-- 1. Ловушка: 0 строк при любом NULL в days_lost
SELECT employee_name FROM safety
WHERE id NOT IN (SELECT days_lost FROM safety);
-- 2. Явная фильтрация NULL внутри подзапроса
SELECT employee_name FROM safety
WHERE id NOT IN (
SELECT days_lost FROM safety WHERE days_lost IS NOT NULL
);
-- 3. Надежный паттерн через NOT EXISTS
SELECT s1.employee_name FROM safety s1
WHERE NOT EXISTS (
SELECT 1 FROM safety s2 WHERE s2.days_lost = s1.id
);
Во втором варианте фильтр WHERE days_lost IS NOT NULL убирает пустые строки на входе: в цепочку неравенств попадают только числа, и список сотрудников возвращается на экран. Но здесь остаётся человеческий фактор: если при доработке отчёта кто-то обратится через NOT IN к другой колонке без ограничения NOT NULL и забудет про внутреннее условие, дашборд снова молча опустеет.
Поэтому в продакшене я предпочитаю третий вариант с NOT EXISTS. Предикат EXISTS по стандарту строго двухзначен: он проверяет лишь наличие строки с равенством s2.days_lost = s1.id. Строки с NULL эту проверку не проходят, подзапрос их игнорирует, а внешний NOT EXISTS отдаёт чистое TRUE. Состояние UNKNOWN исчезает из фильтра, и отчёт выдаёт всех нужных сотрудников.
Как оптимизатор строит анти-джойны и почему NOT EXISTS быстрее
Переход на NOT EXISTS помогает и оптимизатору СУБД. Поиск записей одной таблицы, которых нет в другой, выполняется через анти-соединение (Anti Join). Для колонок без ограничения NOT NULL оператор NOT IN связывает планировщику руки: ради трёхзначной логики база использует Null-Aware Anti Join или вложенные циклы (Nested Loops), проверяя строки на NULL. На крупных таблицах это мешает индексам и замедляет запрос с линейного времени O(N + M) до квадратичного O(N * M).
С конструкцией NOT EXISTS оптимизатор сразу строит быстрый Hash Anti Join или Merge Anti Join по индексам — почему такие предикаты выигрывают на объёмах, мы разбирали в памятке про 10 правил быстрого SQL. Перед отправкой в продакшен любого запроса с NOT IN задай себе вопрос: гарантирует ли схема таблицы ограничением NOT NULL, что внутри скобок не окажется пустого значения? Если там стоит подзапрос к живой таблице — смело меняй его на NOT EXISTS.


