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

Коротко

• IN проверяет, входит ли значение в список или в результат подзапроса. EXISTS проверяет, вернул ли подзапрос хотя бы одну строку.
• Оба оператора не размножают строки внешней таблицы. JOIN размножает: пользователь с тремя платежами превращается в три строки.
• Для вопроса «есть ли связанная запись» IN и EXISTS дают одинаковый ответ, и PostgreSQL строит для них одинаковый план.
• NOT IN возвращает ноль строк, если в списке есть хотя бы один NULL. NOT EXISTS от NULL не зависит.
• Для «нет связанной записи» пишите NOT EXISTS или LEFT JOIN ... IS NULL по ключу соединения.

Как работает IN в SQL

x in (a, b, c) — это короткая запись для x = a or x = b or x = c. Список может быть перечислен вручную или получен подзапросом, который возвращает одну колонку. Условие истинно, если нашлось хотя бы одно совпадение.

Со списком всё просто: channel in ('paid_search', 'partner') возвращает 1829 пользователей из этих двух каналов. С подзапросом IN превращается в фильтр «есть в другой таблице»: user_id in (select user_id from payments) — это 912 пользователей, которые хоть раз платили. В подзапросе 1251 строка и повторы, но на ответ они не влияют: IN проверяет принадлежность множеству.

В PostgreSQL и DuckDB есть равносильная запись user_id = any (select user_id from payments). Она тоже возвращает 912 и удобна в PostgreSQL, когда список приходит массивом-параметром.

select count(*) from users
where channel in ('paid_search', 'partner');       -- 1829

select count(*) from users
where user_id in (select user_id from payments);   -- 912

Как работает EXISTS

EXISTS — оператор проверки существования: exists (подзапрос) истинно, если подзапрос вернул хотя бы одну строку, и ложно, если не вернул ни одной. Подзапрос обычно коррелированный: он ссылается на строку внешнего запроса, например p.user_id = u.user_id. Что именно выбирает подзапрос, неважно: select 1, select null и select * дают одинаковый результат.

Главное преимущество EXISTS перед IN — в подзапросе можно проверять любые условия на пару строк, а не только равенство одной колонки. Например, «сколько участников эксперимента заплатили после показа». Условие зависит и от даты платежа, и от даты показа, поэтому через IN его не записать без лишних конструкций.

Здесь видно, почему «конверсия» в названии колонки не заменяет вопроса о деньгах. Вариант checklist дал больше конверсий в целевое действие эксперимента (467 против 306), а заплативших после показа в нём меньше (288 против 326). Это не вывод об эксперименте — для него нужна проверка значимости, — а напоминание, что EXISTS отвечает ровно на тот вопрос, который вы в него записали.

select
  e.variant,
  count(*) as exposed,
  count(*) filter (where e.converted) as converted,
  count(*) filter (where exists (
    select 1
    from payments as p
    where p.user_id = e.user_id
      and p.paid_at >= cast(e.exposed_at as date)
  )) as paid_after
from experiment_exposures as e
group by e.variant
order by e.variant;

Чем IN и EXISTS отличаются от JOIN

IN и EXISTS — это полусоединение (semi-join): из внешней таблицы остаются строки, у которых нашлась пара, и каждая остаётся ровно один раз. JOIN — обычное соединение: строка повторяется столько раз, сколько пар нашлось.

На вопросе «сколько событий сделали платящие пользователи» разница заметна сразу. Через IN получается 8091 событие. Через JOIN с платежами — 11 667: каждое событие пользователя с двумя платежами посчитано дважды, с тремя — трижды. Ошибка в 44% выглядит как правдоподобное число, и её не видно, пока не сравните с другим способом.

Когда из второй таблицы нужны колонки — сумма, тариф, дата, — нужен JOIN, и зерно придётся контролировать отдельно. Когда нужен только факт наличия, IN или EXISTS честнее: число строк внешней таблицы не меняется ни на одном шаге. Подробнее о том, как соединения меняют зерно отчёта, — в статье про JOIN без потерь и дублей.

NOT EXISTS: кто ни разу не платил

NOT EXISTS отвечает на обратный вопрос: у каких строк нет ни одной пары. Пользователей без платежей 3701. Тот же подсчёт по каналам через FILTER показывает, где неплатящих больше всего в абсолютных числах.

Такой запрос — anti-join: из внешней таблицы остаются строки, для которых подзапрос пуст. PostgreSQL так его и выполняет, узлом Hash Anti Join.

select
  u.channel,
  count(*) as users,
  count(*) filter (where not exists (
    select 1 from payments as p
    where p.user_id = u.user_id
  )) as never_paid
from users as u
group by u.channel
order by u.channel;

Почему NOT IN с NULL возвращает ноль строк

Вернёмся к задаче из введения. Список «кто выгружал» собран так: select case when event_name = 'export_completed' then user_id end from events. Для экспортов CASE возвращает user_id, для всех остальных событий — NULL, потому что ветки ELSE нет. В списке 35 341 значение, из них 988 настоящих и 34 353 NULL.

Условие x not in (a, b, null) раскрывается в x <> a and x <> b and x <> null. Сравнение с NULL даёт не «ложь», а «неизвестно». Если x не совпал ни с одним настоящим значением, всё выражение тоже неизвестно, а WHERE пропускает только истину. Если совпал — выражение ложно. Истинным оно не становится никогда, поэтому NOT IN возвращает 0 строк.

Положительный IN на том же списке работает правильно и находит 988 пользователей: x = a or x = null становится истинным, как только совпало хоть одно значение. Поэтому такие ошибки живут долго: половина запросов с этим списком возвращает верные числа.

NOT EXISTS с условием на тип события находит 3625 пользователей без экспорта. Он не сравнивает значения, а проверяет, нашлась ли строка, и NULL ему не мешает.

-- список с NULL: CASE без ELSE
select count(*) from users
where user_id not in (
  select case when event_name = 'export_completed' then user_id end
  from events
);                                             -- 0

select count(*) from users
where user_id in (
  select case when event_name = 'export_completed' then user_id end
  from events
);                                             -- 988

select count(*) from users as u
where not exists (
  select 1 from events as e
  where e.user_id = u.user_id
    and e.event_name = 'export_completed'
);                                             -- 3625

NULL слева и NULL в ручном списке

Ловушка срабатывает и без подзапроса. Если список собирается из параметров дашборда и один параметр оказался пустым, получится country not in ('RU', null) — ноль строк. Без NULL country not in ('RU') возвращает 1843 пользователя.

Заметьте: 1843, а не 1898, хотя не из России 4613 − 2715 = 1898 человек. Разница — 55 пользователей без страны. Для них null <> 'RU' тоже неизвестно, и NOT IN их отбрасывает. Если такие люди должны попасть в выборку, условие нужно дописать: country not in ('RU') or country is null. NOT EXISTS при NULL слева ведёт себя так же, как при отсутствии пары: строку сохраняет. Какой ответ правильный, решает задача, а не синтаксис.

Anti-join через LEFT JOIN ... IS NULL

Третий способ найти строки без пары — LEFT JOIN и проверка, что справа ничего не нашлось. users left join payments ... where p.user_id is null тоже возвращает 3701. Способ удобен, когда рядом в том же запросе уже есть LEFT JOIN к этой таблице.

Важно, по какой колонке проверять NULL. Если это колонка из условия соединения (p.user_id), PostgreSQL распознаёт anti-join и строит тот же Hash Anti Join, что и для NOT EXISTS. Если это другая колонка, например p.payment_id, план другой: полное соединение, а затем фильтр. На учебной базе ответ совпал, потому что payment_id не бывает пустым. Если бы в платежах были строки с пустым payment_id, пользователи с такими платежами ошибочно попали бы в «неплатящих».

Что быстрее: IN, EXISTS или JOIN

Для положительной проверки в PostgreSQL 14 разницы нет: IN и EXISTS на учебной базе превратились в один и тот же план — Hash Semi Join. JOIN дал Hash Join, который отдаёт строку на каждую найденную пару. Выбирайте то, что точнее выражает вопрос.

Для отрицания разница есть. NOT EXISTS стал Hash Anti Join. NOT IN — фильтром NOT (hashed SubPlan 1): PostgreSQL не может превратить его в anti-join, потому что из-за NULL у NOT IN другая семантика. Хэшированный подзапрос работает, пока список помещается в память (work_mem). Мы проверили, что будет, если не поместится: на ноутбуке с work_mem = 64kB проверка 4613 пользователей по 35 341 событию через NOT IN перешла к построчному перебору и заняла 3074 мс, а NOT EXISTS — 13 мс. При стандартных 4MB оба варианта уложились в 3–4 мс. Цифры иллюстративные, но направление понятное: на больших таблицах NOT IN рискует стать очень медленным.

DuckDB в песочнице EXPLAIN не показывает, поэтому про его планы здесь ничего не утверждаем. Проверяйте на своей базе через EXPLAIN ANALYZE.

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

Одна ошибка с EXISTS стоит отдельного абзаца, потому что запрос с ней выполняется без предупреждений. Если в подзапросе написать where p.user_id = user_id без алиаса справа, база найдёт user_id в ближайшей области видимости — в самой таблице payments. Условие превращается в p.user_id = p.user_id, оно истинно для любого платежа, и EXISTS пропускает всех: 4613 «плательщиков» вместо 912. В коррелированных подзапросах всегда пишите алиас у обеих сторон.

• NOT IN по подзапросу, в котором может быть NULL: CASE без ELSE, LEFT JOIN, необязательная колонка.
• JOIN вместо EXISTS там, где нужен только факт наличия: строки размножаются.
• EXISTS без условия на время там, где оно нужно: «платил когда-либо» вместо «платил после показа».
• Незаквалифицированная колонка внутри EXISTS: условие сравнивает таблицу саму с собой.
• LEFT JOIN ... IS NULL по колонке, которая бывает пустой и справа.
• Потерянные строки с NULL слева в NOT IN, когда их нужно было оставить.

Чеклист: IN, EXISTS или JOIN

• Нужны колонки из второй таблицы — JOIN, и проверка зерна после него.
• Нужен только факт наличия — IN или EXISTS. Условие сложнее равенства ключа — EXISTS.
• Нужен факт отсутствия — NOT EXISTS или LEFT JOIN ... IS NULL по ключу соединения.
• NOT IN — только по списку, где NULL исключён явно (where колонка is not null), и с комментарием.
• У обеих сторон условия в коррелированном подзапросе стоит алиас таблицы.
• Результат сверен с альтернативным способом хотя бы один раз.

Проверьте на учебной базе

В песочнице SQL-курса выполните три запроса из раздела про NOT IN и убедитесь, что получаются 0, 988 и 3625. Затем добавьте в CASE ветку else null явно, потом замените её фильтром where event_name = 'export_completed' в подзапросе и посмотрите, при каком варианте NOT IN начинает работать.