EXISTS, IN и NOT IN в SQL: ловушка с NULL

Q: Как работает IN и в каких задачах он уместен?

IN хорош, когда у тебя есть простой плоский список значений и ты хочешь оставить строки, попадающие в него. Заведём данные для примеров.

Q: Почему NOT IN возвращает пустой результат из-за одного NULL?

Теперь перевернём задачу: найти клиентов без заказов. Наивное решение:

Q: Чем NOT EXISTS безопаснее и предсказуемее NOT IN?

NOT EXISTS не сравнивает значения между собой — он проверяет факт: вернул коррелированный подзапрос строки или нет. Никакого <> NULL внутри, а значит и ловушки трёхзначной логики нет. Тот же запрос «клиенты без заказов», но правильно:

Q: Как найти клиентов без заказов через semi-join?

То, что делают EXISTS и IN, оптимизатор внутри превращает в semi-join и anti-join — специальные виды соединений. - Semi-join — «верни строки левой таблицы, у которых есть пара справа, но каждую левую строку ровно один раз». Это EXISTS / IN. В отличие от обычного INNER JOIN, semi-join не дублирует левую строку, даже если справа несколько совпадений. - Anti-join — «верни строки левой таблицы, у которых нет пары справа». Это NOT EXISTS. Именно поэтому EXISTS часто чище обычного JOIN: при джойне cus

Коротко: IN и EXISTS проверяют одно и то же — «есть ли совпадение в подзапросе» — и на большинстве СУБД работают почти одинаково быстро. Разница взрывается на отрицании: NOT IN при единственном NULL в подзапросе молча возвращает пустой результат целиком, потому что сравнение с NULL даёт UNKNOWN, а не FALSE. NOT EXISTS этой ловушки лишён и ведёт себя предсказуемо. На собесе правильный ответ звучит так: «Для отрицания всегда беру NOT EXISTS, а NOT IN — только когда гарантированно нет NULL».

Дальше разберём, почему так происходит, на конкретных числах, с рабочим SQL и с проверкой в трёхзначной логике.

Чем принципиально отличаются EXISTS, IN и NOT IN?

Все три — про проверку принадлежности строки к какому-то множеству. Но механика разная.

IN (подзапрос) — материализует список значений и проверяет, входит ли текущее значение в этот список. Это сравнение по равенству: x IN (a, b, c) эквивалентно x = a OR x = b OR x = c.
EXISTS (подзапрос) — не возвращает значения вообще. Он проверяет только факт: вернул подзапрос хотя бы одну строку или нет. Как только найдена первая подходящая строка, СУБД останавливается (short-circuit).
NOT IN и NOT EXISTS — отрицания первых двух. И именно здесь прячется разница в поведении, которая ломает запросы в проде.

Ключевой момент, который надо держать в голове: SQL работает не в двузначной логике TRUE/FALSE, а в трёхзначной — TRUE, FALSE и UNKNOWN. Любое сравнение с NULL (кроме IS NULL) даёт UNKNOWN. А строки в результат попадают только когда условие WHERE равно TRUE. Если условие UNKNOWN — строка отбрасывается. Если базово не понимаешь, как ведёт себя NULL в сравнениях, стоит сначала пройти часть про NULL и COALESCE — без этого дальнейшее будет казаться магией.

Соберём отличия в таблицу:

Критерий	IN	EXISTS	NOT IN	NOT EXISTS
Что проверяет	значение в списке	наличие хоть одной строки	значения нет в списке	нет ни одной строки
Логика	сравнение по `=`	факт существования	сравнение по `<>`	факт отсутствия
Реакция на NULL в подзапросе	безопасно	безопасно	опасно: пустой результат	безопасно
Останов при первом совпадении	нет	да	нет	да
Коррелированный подзапрос	обычно нет	почти всегда да	обычно нет	почти всегда да

Отдельно про подзапросы и CTE — если этот синтаксис пока плавает, освежите подзапросы и CTE (WITH).

Как работает IN и в каких задачах он уместен?

IN хорош, когда у тебя есть простой плоский список значений и ты хочешь оставить строки, попадающие в него. Заведём данные для примеров.

CREATE TABLE customers (
    id   INT PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT,      -- может быть NULL для «сиротских» заказов
    amount      NUMERIC
);

INSERT INTO customers VALUES
    (1, 'Анна'), (2, 'Борис'), (3, 'Вера'), (4, 'Глеб');

INSERT INTO orders VALUES
    (100, 1, 2500),
    (101, 1, 900),
    (102, 2, 4300),
    (103, NULL, 1500);   -- заказ без привязки к клиенту

Задача: найти клиентов, у которых есть хотя бы один заказ.

SELECT id, name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);

Подзапрос вернёт список {1, 1, 2, NULL}. Клиенты 1 и 2 в нём есть — они попадут в результат. Вера и Глеб не попадут. Обратите внимание: NULL в списке здесь никак не мешает — при *позитивной* проверке IN строка проходит, если нашлось хоть одно точное равенство, а лишние NULL просто игнорируются как «непонятно».

IN уместен когда:

список короткий и известен заранее: WHERE status IN ('paid', 'refunded');
подзапрос возвращает небольшое множество без дублей и без NULL;
вам нужна читаемость, а не выжимание последних миллисекунд.

Потренировать такие запросы вживую можно в SQL-тренажёре — там подзапросы выполняются на настоящем PostgreSQL прямо в браузере.

Почему NOT IN возвращает пустой результат из-за одного NULL?

Теперь перевернём задачу: найти клиентов без заказов. Наивное решение:

SELECT id, name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

Ожидание: вернутся Вера (3) и Глеб (4). Реальность: результат пустой. Ни одной строки. И это не баг СУБД — это стандарт SQL.

Разберём по шагам. Подзапрос вернул {1, 1, 2, NULL}. Выражение id NOT IN (1, 1, 2, NULL) СУБД разворачивает так:

id <> 1 AND id <> 2 AND id <> NULL

Возьмём Глеба, id = 4:

4 <> 1 → TRUE
4 <> 2 → TRUE
4 <> NULL → UNKNOWN (сравнение с NULL никогда не бывает TRUE или FALSE)

Итог: TRUE AND TRUE AND UNKNOWN = UNKNOWN. А строка попадает в результат только при TRUE. UNKNOWN — не TRUE, поэтому Глеб выбывает. Ровно та же арифметика убивает и Веру, и вообще любую строку. Один-единственный NULL в подзапросе гарантированно обнуляет весь NOT IN.

Почему 4 <> NULL не TRUE? Потому что NULL означает «значение неизвестно». СУБД честно отвечает: «я не знаю, равен ли 4 неизвестному значению, значит не знаю и обратного». Это логично, но на практике превращается в тихую, ничем не сигнализируемую потерю данных. Запрос не падает, не выдаёт warning — просто отдаёт неверный (пустой или урезанный) ответ. Такие баги живут в отчётах месяцами. Это классика из подборки 10 антипаттернов SQL.

Проверить трёхзначную логику руками можно так:

SELECT
    (4 <> 1)    AS cmp1,   -- true
    (4 <> 2)    AS cmp2,   -- true
    (4 <> NULL) AS cmp3,   -- NULL (в выводе пусто)
    (4 NOT IN (1, 2, NULL)) AS result;  -- NULL, а не true

Чем NOT EXISTS безопаснее и предсказуемее NOT IN?

NOT EXISTS не сравнивает значения между собой — он проверяет факт: вернул коррелированный подзапрос строки или нет. Никакого <> NULL внутри, а значит и ловушки трёхзначной логики нет.

Тот же запрос «клиенты без заказов», но правильно:

SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
);

Здесь для каждого клиента подзапрос ищет его заказы. Для Веры и Глеба заказов нет → подзапрос пустой → NOT EXISTS = TRUE → они в результате. Заказ-сирота с customer_id = NULL вообще не участвует: условие o.customer_id = c.id для него даёт UNKNOWN, строка не матчится, и это ровно то поведение, которое нам нужно. Результат: Вера и Глеб — как и ожидалось.

Сравним три подхода на наших данных:

Запрос	Что вернёт	Корректно?
`NOT IN` с NULL в подзапросе	0 строк	Нет, тихая потеря
`NOT IN` + `WHERE customer_id IS NOT NULL`	Вера, Глеб	Да, но легко забыть
`NOT EXISTS`	Вера, Глеб	Да, устойчиво по умолчанию

Если очень хочется оставить NOT IN, его можно «вылечить», отфильтровав NULL в подзапросе:

SELECT id, name
FROM customers
WHERE id NOT IN (
    SELECT customer_id FROM orders
    WHERE customer_id IS NOT NULL
);

Работает. Но это костыль, который держится на памяти разработчика: стоит кому-то убрать WHERE customer_id IS NOT NULL при рефакторинге — и отчёт молча сломается. NOT EXISTS не требует такой дисциплины, поэтому в проде он предпочтительнее почти всегда.

Мнемоника для собеса: позитив — IN или EXISTS (без разницы), негатив — только NOT EXISTS.

Как найти клиентов без заказов через semi-join?

То, что делают EXISTS и IN, оптимизатор внутри превращает в semi-join и anti-join — специальные виды соединений.

Semi-join — «верни строки левой таблицы, у которых есть пара справа, но каждую левую строку ровно один раз». Это EXISTS / IN. В отличие от обычного INNER JOIN, semi-join не дублирует левую строку, даже если справа несколько совпадений.
Anti-join — «верни строки левой таблицы, у которых нет пары справа». Это NOT EXISTS.

Именно поэтому EXISTS часто чище обычного JOIN: при джойне customers с orders Анна с двумя заказами задвоилась бы, и пришлось бы городить DISTINCT. С EXISTS дублей нет по определению.

Есть и третий популярный способ найти «клиентов без заказов» — LEFT JOIN ... IS NULL (anti-join руками):

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;   -- совпадений справа не нашлось

Он тоже корректен и не страдает от NULL-ловушки, потому что проверяет IS NULL на ключе правой таблицы после соединения, а не сравнивает через NOT IN. Разбор всех видов соединений — в гайде про JOIN со всеми типами.

Три рабочих способа найти клиентов без заказов, все три верные:

NOT EXISTS — самый читаемый и устойчивый.
LEFT JOIN ... WHERE o.id IS NULL — привычен тем, кто мыслит джойнами.
NOT IN (... WHERE ключ IS NOT NULL) — можно, но с обязательным фильтром NULL.

На практике поиск «сущностей без связанной активности» — это скелет десятков аналитических задач: неактивные пользователи, товары без продаж, когорты, не совершившие повторную покупку. Отсюда прямая дорога к метрикам вроде churn rate (оттока) и retention D7 — их SQL по сути и есть anti-join активной и базовой таблиц.

Что быстрее на больших таблицах — EXISTS или IN?

Короткий ответ: на современных СУБД (PostgreSQL, современный MySQL, ClickHouse) IN и EXISTS в позитивной форме оптимизатор чаще всего сводит к одному и тому же плану — semi-join. Разница в скорости в большинстве случаев в пределах шума. Старое правило «EXISTS всегда быстрее IN» устарело: оно родом из времён, когда оптимизаторы не умели переписывать IN в semi-join и материализовали весь список.

Тем не менее ориентиры полезно держать:

EXISTS выигрывает, когда подзапрос возвращает много строк, а совпадение находится рано — он останавливается на первой же строке (short-circuit). Не нужно материализовать миллионы значений.
IN выигрывает или равен, когда подзапрос возвращает маленькое множество без дублей — оптимизатору дешевле построить хеш и проверять по нему.
Наличие индекса на столбце соединения (orders.customer_id) важнее выбора IN vs EXISTS. Без индекса любой из них деградирует до полного скана.
Для отрицания NOT EXISTS обычно не только безопаснее, но и быстрее NOT IN: NOT IN с потенциальными NULL мешает оптимизатору применить эффективный anti-join и заставляет его страховаться.

Практическое правило: не гадай, а смотри план. В PostgreSQL — EXPLAIN ANALYZE, и ищи в выводе узлы Hash Semi Join / Hash Anti Join. Как читать этот вывод, подробно разобрано в гайде по EXPLAIN ANALYZE.

EXPLAIN ANALYZE
SELECT c.id
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- в плане ждём: Hash Semi Join, а не Nested Loop по всей таблице

Быстро проверить масштаб проблемы можно и в Python — сгенерировать данные и сравнить корректность подходов, не поднимая базу:

import pandas as pd

customers = pd.DataFrame({"id": [1, 2, 3, 4]})
orders = pd.DataFrame({"customer_id": [1, 1, 2, None]})

have = set(orders["customer_id"].dropna())      # {1.0, 2.0}
# аналог NOT EXISTS / anti-join — корректно
no_orders = customers[~customers["id"].isin(have)]
print(no_orders["id"].tolist())   # [3, 4] — верно

Заметьте: pandas.isin игнорирует NaN в списке и ведёт себя как «правильный» NOT IN — ещё один повод помнить, что ловушка NOT IN специфична именно для SQL-семантики трёхзначной логики. Погонять такой код можно в Python-тренажёре.

Какой вариант выбрать и как объяснить на собеседовании?

Собери решение в одну карту — она же готовый ответ интервьюеру.

Нужны строки, у которых ЕСТЬ пара → EXISTS или IN, выбирай по читаемости. Разницы в скорости обычно нет.
Нужны строки, у которых НЕТ пары → NOT EXISTS по умолчанию. Это единственный вариант, который не ломается на NULL и хорошо оптимизируется.
NOT IN допустим только если поле в подзапросе объявлено NOT NULL или ты явно добавил WHERE ключ IS NOT NULL. Иначе — тихий пустой результат.
Дубли слева не нужны (например, клиент с 10 заказами должен встретиться один раз) → EXISTS/semi-join вместо INNER JOIN без DISTINCT.

Как это звучит на собесе, коротко и по делу:

«IN и EXISTS для позитивной проверки эквивалентны, оптимизатор сводит оба к semi-join. Принципиальная разница на отрицании: NOT IN при NULL в подзапросе возвращает пустой результат, потому что x <> NULL даёт UNKNOWN, а не FALSE, и вся конъюнкция обнуляется. Поэтому для антиджойна я беру NOT EXISTS — он безопасен по умолчанию и обычно строит более эффективный план».

Такой ответ показывает, что вы понимаете трёхзначную логику, а не заучили «EXISTS быстрее». Именно на этом уровне детализации отсекают junior от middle. Похожие ловушки — COUNT с NULL, LEFT JOIN с условием в WHERE вместо ON, UNION против UNION ALL — регулярно всплывают в подборке топ-50 вопросов по SQL на собеседовании.

Хочешь довести это до автоматизма — разбери подзапросы и семи-джойны на реальных задачах: первые 5 бесплатны, дальше открывается Pro-доступ ко всем 425 SQL-задачам. Если только начинаешь путь в SQL, лучше стартовать с системного курса SQL с нуля, а перед самим собесом — прогнать себя через AI мок-интервью, где виртуальный интервьюер задаёт вопросы голосом реального собеса и разбирает ответы.

Проверь себя прямо сейчас: без запуска кода скажи, что вернёт SELECT * FROM t WHERE x NOT IN (1, 2, NULL) для строки с x = 5. Если ответил «пусто / ни одной строки, потому что 5 <> NULL = UNKNOWN» — материал усвоен.