NOT EXISTS vs NOT IN: the NULL Trap and Anti-Joins

Найти строки, у которых нет совпадения в другой таблице, можно тремя способами: NOT IN, NOT EXISTS и LEFT JOIN ... IS NULL. Логически они близки, но NOT IN прячет коварную ловушку с NULL, которая в один тихий день обнуляет ваш результат. Разберём различия на схеме users, orders, employees.

Задача: пользователи без заказов

Классический «анти-join»: нужны те, кто ни разу не оформил заказ. Интуитивный вариант на NOT IN:

SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (SELECT user_id FROM orders);

Пока в orders.user_id нет NULL, всё работает. Но user_id может быть nullable (гостевой заказ, мягкое удаление), и тогда запрос ломается — без ошибки, без предупреждения.

Ловушка NULL в NOT IN

NOT IN (...) разворачивается в цепочку id <> v1 AND id <> v2 AND .... Если среди значений есть NULL, сравнение id <> NULL даёт не TRUE/FALSE, а UNKNOWN. В трёхзначной логике TRUE AND UNKNOWN = UNKNOWN, и строка отбрасывается. Итог: один NULL в подзапросе обнуляет весь результат.

-- orders has one row with user_id IS NULL.
-- This returns NOTHING, even though some users have no orders:
SELECT u.id, u.email
FROM users u
WHERE u.id NOT IN (SELECT user_id FROM orders);

• Это поведение по стандарту SQL, не баг конкретной СУБД.
• NULL IN (...) и NULL NOT IN (...) тоже никогда не дают TRUE.
• Одинаково ведут себя PostgreSQL, MySQL и ClickHouse.

Костыль — отфильтровать NULL вручную: WHERE u.id NOT IN (SELECT user_id FROM orders WHERE user_id IS NOT NULL). Работает, но об этом легко забыть, и про колонки в составном ключе тем более.

NOT EXISTS: безопасно по умолчанию

NOT EXISTS проверяет наличие хотя бы одной строки и оперирует обычной двухзначной логикой существования, поэтому NULL его не сбивает:

SELECT u.id, u.email
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.user_id = u.id
);

Строка с o.user_id IS NULL просто не совпадёт по o.user_id = u.id и тихо отсеется — никакого схлопывания результата. SELECT 1 внутри — идиома: список select в EXISTS не вычисляется, важен только факт наличия строки.

Связанные подзапросы хорошо ложатся на составные условия:

-- Employees with no reports (nobody lists them as manager_id):
SELECT e.id, e.name, e.dept
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM employees s
    WHERE s.manager_id = e.id
);

LEFT JOIN ... IS NULL

Третий вариант — внешнее соединение с фильтром по несоответствию. Тоже NULL-безопасен в условии анти-join:

SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.user_id IS NULL;

• Проверяйте на IS NULL именно столбец из соединения (o.user_id), а не любой nullable столбец orders.
• При связи «один ко многим» соединение размножает строки, поэтому потом часто нужен DISTINCT или GROUP BY — лишняя работа. NOT EXISTS такой проблемы не создаёт.

Производительность и анти-join планы

Современный PostgreSQL умеет выполнять и NOT EXISTS, и LEFT JOIN ... IS NULL как настоящий anti join (Hash Anti Join / Merge Anti Join в EXPLAIN) — он останавливается на первом совпадении. А вот NOT IN с nullable-столбцом планировщик не имеет права превратить в анти-join из-за семантики NULL, и часто получается более тяжёлый план.

• В PostgreSQL по NULL-безопасности и скорости предпочитайте NOT EXISTS.
• MySQL 8 тоже эффективно оптимизирует NOT EXISTS; исторически NOT IN бывал заметно медленнее.
• В ClickHouse удобнее LEFT ANTI JOIN — явный анти-join без подзапроса.
• Под коррелированное условие в NOT EXISTS держите индекс (например, на orders(user_id)).

Вывод: сделайте NOT EXISTS выбором по умолчанию для «строк без совпадения». NOT IN оставьте для статических списков без NULL (status NOT IN ('paid','shipped')), а LEFT JOIN ... IS NULL берите, когда из несовпавшей стороны всё равно нужны столбцы.

Перед ревью такого запроса проверьте два факта: может ли правая колонка быть NULL и есть ли индекс под условие сопоставления. Если оба ответа хорошие, анти-join будет и корректным, и предсказуемым по плану.