Что такое 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).