EXISTS и NOT EXISTS в SQL: проверка наличия строк

EXISTS отвечает на один вопрос: нашлась ли хоть одна строка. Он не сравнивает значения и не возвращает данные, а даёт true или false. Отсюда его главное свойство: строки внешнего запроса не размножаются, сколько бы совпадений ни нашлось справа. Всё дальше снято на PostgreSQL 16.11.

Данные те же, что в остальных статьях справочника:

users

orders

У Ани три заказа, у Веры ни одного. На этих двух краях видно всё.

SQL / 01koddo

EXISTS отвечает один раз на каждую внешнюю строку

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

Подзапрос в скобках выполняется для каждой строки внешнего запроса, и строка остаётся, если он что-то вернул:

SELECT u.name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)
ORDER BY u.id;

Три клиента из четырёх, Вера отсеялась. Аня при этом встречается один раз, хотя заказов у неё три: EXISTS останавливается на первом же совпадении и дальше не смотрит.

Сравни с соединением, которое отвечает на тот же вопрос иначе:

SELECT u.name FROM users u JOIN orders o ON o.user_id = u.id ORDER BY u.id, o.id;

Шесть строк вместо трёх. JOIN строит пары, поэтому клиент повторяется столько раз, сколько у него заказов, и список приходится чистить через DISTINCT. Эта развилка подробно разобрана в статье про DISTINCT, где показано, почему заглушить размножение постфактум дороже, чем не создавать его.

Что писать внутри EXISTS

В простом подзапросе ниже SELECT 1 и SELECT * равнозначны: база проверяет наличие строки и не вычисляет выражения списка выборки:

SELECT
  (SELECT count(*) FROM users u WHERE EXISTS (SELECT 1   FROM orders o WHERE o.user_id = u.id)) AS c_one,
  (SELECT count(*) FROM users u WHERE EXISTS (SELECT *   FROM orders o WHERE o.user_id = u.id)) AS c_star,
  (SELECT count(*) FROM users u WHERE EXISTS (SELECT 1/0 FROM orders o WHERE o.user_id = u.id)) AS c_div0;

Третья колонка показывает, что в этом запросе деление на ноль не выполнилось. Единицу пишут по традиции: так читателю виднее, что значения из подзапроса не нужны. Но переносить это правило на любой подзапрос нельзя. Например, EXISTS (SELECT 1 INTERSECT SELECT 2) возвращает false, а EXISTS (SELECT 1/0 INTERSECT SELECT 1) завершается ошибкой: для пересечения важны сами значения.

Связь с внешним запросом — несущая часть

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

SELECT u.name FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.status = 'paid')
ORDER BY u.id;

Все четверо, включая Веру без единого заказа. Подзапрос без ссылки на u.id отвечает на вопрос «оплаченные заказы вообще существуют», а он истинный всегда, поэтому фильтр перестал фильтровать. Ошибки нет, отчёт неверен.

Правило: внутри EXISTS почти всегда должно быть условие вида o.внешний_ключ = u.первичный_ключ. Без него подзапрос перестаёт быть коррелированным и вырождается в константу.

NOT EXISTS: найти тех, у кого ничего нет

Отрицание отвечает на обратный вопрос:

SELECT u.name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id)
ORDER BY u.id;

Условия внутри подзапроса сужают проверку, а не выборку. «Клиенты без единого крупного заказа» — это NOT EXISTS с порогом внутри:

SELECT u.name FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 2000)
ORDER BY u.id;

Аня выпала: у неё есть заказ на 2400. Остальные трое остались, включая Веру, у которой заказов нет вовсе. Это важная деталь: NOT EXISTS считает «нет подходящих» и «нет никаких» одинаково истинными.

Главный аргумент в пользу NOT EXISTS — устойчивость к пропускам. Похожий по смыслу NOT IN возвращает пустой результат, если в подзапросе найдётся хоть один NULL, и разобрано это в статье про NULL и трёхзначную логику. У NOT EXISTS такой болезни нет: он не сравнивает значения, а проверяет наличие строк.

EXISTS в SELECT как готовый флаг

EXISTS возвращает логическое значение, поэтому годится не только для фильтра:

SELECT u.name,
       EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.status = 'paid') AS has_paid
FROM users u
ORDER BY u.id;

В таблице выше true и false подписаны читаемо, psql печатает t и f. Такой флаг удобнее, чем count(*) > 0 через соединение с группировкой: он не требует ни JOIN, ни GROUP BY, и не портит остальные колонки размножением строк.

Semi join и anti join: что показывает план

У EXISTS есть собственное имя в теории соединений. Посмотрим план на объёме: 200 тысяч клиентов и 800 тысяч заказов.

EXPLAIN (COSTS OFF)
SELECT count(*) FROM big_users u WHERE EXISTS (SELECT 1 FROM big_orders o WHERE o.user_id = u.id);

 Finalize Aggregate
   ->  Gather
         Workers Planned: 1
         ->  Partial Aggregate
               ->  Parallel Hash Semi Join
                     Hash Cond: (u.id = o.user_id)

Узел называется Semi Join — полусоединение. Оно ведёт себя как обычное соединение, но берёт из правой таблицы не строки, а только ответ «пара нашлась» и переходит к следующей левой строке. Отсюда и отсутствие дублей: правая сторона не попадает в результат вовсе.

У отрицания свой узел:

EXPLAIN (COSTS OFF)
SELECT count(*) FROM big_users u WHERE NOT EXISTS (SELECT 1 FROM big_orders o WHERE o.user_id = u.id);

 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial Aggregate
               ->  Parallel Hash Right Anti Join
                     Hash Cond: (o.user_id = u.id)

Anti Join — антисоединение, ровно та операция, которую в статье про LEFT JOIN собирают вручную через LEFT JOIN и IS NULL. Планировщик умеет её сам, и писать NOT EXISTS короче, чем строить антиджойн руками.

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

Ответ неудобный для заучивания, зато проверяемый. Возьмём три формы одного вопроса «сколько клиентов сделали хоть один заказ» и сравним планы.

EXISTS и IN дают дословно одинаковый план, вплоть до имени узла:

EXPLAIN (COSTS OFF)
SELECT count(*) FROM big_users u WHERE u.id IN (SELECT o.user_id FROM big_orders o);

 Finalize Aggregate
   ->  Gather
         Workers Planned: 1
         ->  Partial Aggregate
               ->  Parallel Hash Semi Join
                     Hash Cond: (u.id = o.user_id)

Планировщик приводит обе записи к одному полусоединению, поэтому спор «EXISTS быстрее IN» на современном PostgreSQL беспредметен.

Замеры на тех же 200 тысячах и 800 тысячах строк, по три прогона на тёплом кеше:

Результат ломает расхожее правило. JOIN с DISTINCT оказался быстрее остальных, потому что планировщик выбрал для него проходы по индексам с обеих сторон, а полусоединению досталось параллельное чтение таблиц целиком. На других данных и других индексах расклад будет другим.

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

На чём ловят на собеседовании

«SELECT * внутри EXISTS читает все колонки». В простом подзапросе выше список выборки не вычисляется. Это не общее правило для любой конструкции внутри EXISTS: у подзапросов с INTERSECT значения влияют на результат.

«EXISTS вернёт столько строк, сколько совпадений». Он вообще не возвращает строк, только true или false для каждой строки внешнего запроса. Размножает строки JOIN, а не EXISTS.

«NOT EXISTS и NOT IN взаимозаменяемы». Для замены проверки отсутствия равного значения нужно исключить NULL и слева, и в подзапросе. Если слева NULL, а справа непустой набор без NULL, NOT IN вернёт UNKNOWN; NOT EXISTS с обычным равенством вернёт true. Один NULL справа тоже способен отсеять все строки в WHERE ... NOT IN (...), тогда как NOT EXISTS не считает его совпадением.

«EXISTS всегда быстрее IN». На одном и том же вопросе PostgreSQL строит для них один план. Разница появляется на других формулировках, а не на выборе ключевого слова.

«EXISTS можно писать без связи с внешней таблицей». Синтаксически можно, по смыслу почти никогда: без корреляции условие становится константой и пропускает все строки.

Частые вопросы

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

IN сравнивает значение со списком, EXISTS проверяет наличие строк. Практическая разница в двух местах: IN ломается на NULL в отрицании, а EXISTS умеет проверять условие сразу по нескольким колонкам, потому что внутри у него полноценный WHERE.

Что означает ошибка «relation does not exist»

Она к оператору EXISTS отношения не имеет. Так база сообщает, что таблицы с таким именем нет, и все шесть причин разобраны в отдельной статье:

ERROR:  relation "zakazy" does not exist
LINE 1: SELECT * FROM zakazy;
                      ^

Чаще всего дело в опечатке, в другой схеме или в кавычках: имя, созданное как "Orders", ищется только в кавычках, потому что без них PostgreSQL приводит его к нижнему регистру.

Можно ли использовать EXISTS в HAVING и в CHECK

Да, EXISTS возвращает логическое значение и работает везде, где ожидается условие. Исключение — ограничение CHECK в таблице: подзапросы в нём запрещены стандартом, потому что проверка обязана зависеть только от самой строки.

Как проверить, что заказов ровно ноль, а не просто мало

Это и есть NOT EXISTS. Считать count(*) = 0 через соединение с группировкой дороже: база посчитает все строки, чтобы потом сравнить итог с нулём, тогда как NOT EXISTS останавливается на первой же найденной.

Где потренироваться

В пути «SQL для собеседований» на Koddo EXISTS идёт вместе с антиджойнами и дублями: найти клиентов без заказов, отсеять записи без пары, убрать размножение строк перед агрегатом. Проверить себя можно на задаче про повторные отклики, где сначала надо понять, какие строки вообще имеют пару, а уже потом считать.