re:re:actable
Урок 073 минуты чтения

Что такое semi join и anti join

На этой странице
Кратко

Semi join возвращает строки левой таблицы, у которых есть хотя бы одна пара справа, — при этом каждая левая строка попадает один раз, а столбцы правой не возвращаются. Anti join — строки, у которых пары нет. В SQL отдельного ключевого слова нет: semi join пишут через EXISTS или IN, anti join — через NOT EXISTS или LEFT JOIN … WHERE right.id IS NULL. Планировщик PostgreSQL распознаёт эти формы и выполняет их как Semi Join / Anti Join (видно в EXPLAIN). Главная ловушка — NOT IN с подзапросом, где может встретиться NULL: результат становится пустым.

Вопрос

Что такое semi join и anti join и как их записать?

Semi join

-- пользователи, у которых есть оплаченный заказ
SELECT u.id, u.email
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.status = 'paid'
);
 
-- через IN
SELECT id, email FROM users
WHERE id IN (SELECT user_id FROM orders WHERE status = 'paid');

В отличие от JOIN, пользователь с пятью оплаченными заказами вернётся один раз, и не нужен DISTINCT.

Anti join

-- пользователи без заказов
SELECT u.id, u.email
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);
 
-- через LEFT JOIN
SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Ловушка NOT IN и NULL

-- если хотя бы один orders.user_id IS NULL — результат ПУСТОЙ
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders);

id NOT IN (1, 2, NULL) = id <> 1 AND id <> 2 AND id <> NULL → ... AND NULL → никогда не true. Используйте NOT EXISTS — он корректно работает с NULL, и планировщик лучше его оптимизирует.

Что выбрать

ЗадачаРекомендуемая форма
Есть хотя бы одна параEXISTS (или IN по NOT NULL столбцу)
Нет ни одной парыNOT EXISTS
Короткий список константIN (1, 2, 3) / = ANY($1)
Нужны столбцы правой таблицыОбычный JOIN

Подробнее про выбор — EXISTS и IN.

Практические примеры

-- товары, которые ни разу не покупали
SELECT p.* FROM products p
WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.id);
 
-- брошенные корзины: есть корзина, но нет заказа за последние сутки
SELECT c.* FROM carts c
WHERE c.updated_at < now() - interval '1 day'
  AND NOT EXISTS (SELECT 1 FROM orders o WHERE o.cart_id = c.id);

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

  • NOT IN с подзапросом по nullable столбцу.
  • JOIN + DISTINCT вместо EXISTS.
  • LEFT JOIN ... WHERE o.nullable_col IS NULL — проверять нужно NOT NULL столбец (PK).
  • Отсутствие индекса по столбцу связи в подзапросе.

Что важно помнить

  • Semi join — «есть пара», без размножения строк.
  • Anti join — «нет пары»; безопасная форма — NOT EXISTS.
  • NOT IN + NULL = пустой результат.

Мини-шпаргалка

  • WHERE EXISTS (SELECT 1 ...).
  • WHERE NOT EXISTS (SELECT 1 ...).
  • LEFT JOIN ... WHERE r.id IS NULL.
  • Избегать NOT IN (subquery).

Документация

PostgreSQL: Subquery Expressions.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам