SEMI-JOIN и ANTI-JOIN

В мире SQL-запросов часто возникают ситуации, когда нам нужно найти записи, которые существуют (или не существуют) в связанной таблице. Именно для таких случаев предназначены SEMI-JOIN и ANTI-JOIN — логические концепции, которые часто остаются в тени INNER JOIN и LEFT JOIN.

SEMI-JOIN

SEMI-JOIN возвращает строки из первой таблицы, для которых существует хотя бы одно соответствие во второй таблице.
Ключевая особенность: каждая строка из первой таблицы возвращается только один раз, даже если во второй таблице есть несколько совпадений.

ANTI-JOIN

ANTI-JOIN — противоположность SEMI-JOIN. Он возвращает строки из первой таблицы, для которых нет соответствий во второй таблице.

EXISTS и NOT EXISTS

SEMI-JOIN и ANTI-JOIN — это логические операции, а не SQL-операторы. В PostgreSQL они обычно реализуются (через переписывание оптимизатором) с использованием конструкций EXISTS и NOT EXISTS: EXISTS — может быть преобразован в SEMI JOIN NOT EXISTS — может быть преобразован в ANTI JOIN

Пример 1: SEMI-JOIN с EXISTS

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

explain analyze
select customer_id, first_name, last_name
from customer c
where exists (
    select 1
    from rental r
    where r.customer_id = c.customer_id
);

Hash Join  (cost=364.03..418.25 rows=599 width=17) (actual time=1.906..2.026 rows=599 loops=1)

explain analyze
select distinct c.customer_id, c.first_name, c.last_name
from customer c
join rental r on c.customer_id = r.customer_id;

HashAggregate  (cost=526.66..532.65 rows=599 width=17) (actual time=4.750..4.784 rows=599 loops=1)

Так как EXISTS проверяет наличие и для срабатывания достаточно первого соответствия, то из таблицы customer изначально получаем уникальные значения без поиска всех соответствий для каждого значения, а при использовании JOIN от дублирования строк приходится избавляться через оператор DISTINCT.

Пример 2: ANTI-JOIN с NOT EXISTS

Задача: Найти всех клиентов, которые никогда не совершали аренд.
Сразу будем делать EXPLAIN ANALYZE для сравнения решений.

explain analyze
select customer_id, first_name, last_name
from customer c
where not exists (
    select 1
    from rental r
    where r.customer_id = c.customer_id
);

Hash Anti Join  (cost=510.99..560.72 rows=1 width=17) (actual time=1.672..1.672 rows=0 loops=1)

explain analyze
select c.customer_id, c.first_name, c.last_name
from customer c
left join rental r on c.customer_id = r.customer_id
where r.rental_id is null;

Hash Right Join  (cost=53.48..406.33 rows=1 width=17) (actual time=1.968..1.968 rows=0 loops=1)

В данном контексте по стоимости LEFT JOIN выигрывает, но по времени проигрывает.

Пример 3: Multiple EXISTS условия

Задача: Найти клиентов, которые арендовали фильмы категории "Action" И фильмы категории "Comedy".
Напишем несколько разных подходов, включая агрегацию.
Сразу будем делать EXPLAIN ANALYZE для сравнения решений.

explain analyze
select c.customer_id, c.first_name, c.last_name
from customer c
where exists (
    select 1
    from rental r
    join inventory i on r.inventory_id = i.inventory_id
    join film_category fc on i.film_id = fc.film_id
    join category cat on fc.category_id = cat.category_id
    where r.customer_id = c.customer_id
    and cat.name = 'Action'
)
and exists (
    select 1
    from rental r
    join inventory i on r.inventory_id = i.inventory_id
    join film_category fc on i.film_id = fc.film_id
    join category cat on fc.category_id = cat.category_id
    where r.customer_id = c.customer_id
    and cat.name = 'Comedy'
);

Hash Semi Join  (cost=565.18..629.14 rows=599 width=17) (actual time=2.062..2.233 rows=419 loops=1)

explain analyze
select c.customer_id, c.first_name, c.last_name
from customer c
join rental r on r.customer_id = c.customer_id
join inventory i on r.inventory_id = i.inventory_id
join film_category fc on i.film_id = fc.film_id
join category cat on fc.category_id = cat.category_id
where cat.name = 'Action'
intersect 
select c.customer_id, c.first_name, c.last_name
from customer c
join rental r on r.customer_id = c.customer_id
join inventory i on r.inventory_id = i.inventory_id
join film_category fc on i.film_id = fc.film_id
join category cat on fc.category_id = cat.category_id
where cat.name = 'Comedy';

HashSetOp Intersect  (cost=75.06..697.50 rows=599 width=224) (actual time=2.909..2.925 rows=419 loops=1)

explain analyze
select c.customer_id, c.first_name, c.last_name
from customer c
join rental r on r.customer_id = c.customer_id
join inventory i on r.inventory_id = i.inventory_id
join film_category fc on i.film_id = fc.film_id
join category cat on fc.category_id = cat.category_id 
where cat.name in ('Comedy', 'Action')
group by c.customer_id
having count(distinct cat.category_id) = 2

GroupAggregate  (cost=601.02..623.55 rows=37 width=17) (actual time=2.270..2.494 rows=419 loops=1)

В данном примере получаем незначительную разницу в стоимости и небольшую разницу по времени между EXISTS и AGGREGATE. Вариант с INTERSECT часто проигрывает по производительности, так как требует операций над множествами, которые могут включать сортировку или хеширование промежуточных результатов

Заключение

Понимание, когда использовать EXISTS/NOT EXISTS вместо различных типов JOIN, позволяет писать более эффективные и читаемые запросы. Баланс между производительностью и читаемостью — ключ к написанию качественного SQL-кода. EXISTS и JOIN нельзя считать взаимозаменяемыми конструкциями. JOIN используется для построения результирующего набора данных, тогда как EXISTS — это предикат существования. Сравнение их производительности корректно только в частном случае, когда JOIN используется исключительно для проверки наличия связей без извлечения данных.

Соответственно EXISTS/NOT EXISTS можно использовать, когда:

• требуется проверка существования записей, то есть не нужно выводить данные из проверяемых таблиц
• нужно избегать дубликатов
• происходит работа с большими таблицами, особенно если есть индексы
• применяются сложные условия, где важен только факт существования

Помните: нет универсального "лучшего" подхода — выбор между EXISTS и JOIN зависит от конкретной задачи, структуры данных и требований к производительности. Всегда проверяйте план запроса для вашей конкретной базы данных.