Semi и anti join

Semi-join: «есть ли совпадение?» — да/нет, без размножения

Часто тебе не нужны совпавшие строки из другой таблицы; ты хочешь лишь знать, существует ли совпадение. «Пользователи, сделавшие хотя бы один заказ». Обычный JOIN отвечает плохо: если у пользователя 50 заказов, join выдаёт этого пользователя 50 раз, и приходится привинчивать DISTINCT, чтобы отменить ущерб. Это fan-out, а за ним уборка.

Semi-join — правильный инструмент: он возвращает каждую подходящую левую строку ровно один раз, независимо от того, сколько у неё совпадений справа. SQL пишет это как EXISTS или IN:

-- Пользователи с хотя бы одним заказом — каждый один раз, без дублей
SELECT u.id, u.name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- Эквивалентный semi-join через IN
SELECT u.id, u.name
FROM users u
WHERE u.id IN (SELECT user_id FROM orders);

EXISTS прекращает сканировать внутреннюю сторону, как только находит первую совпадающую строку — это короткозамкнутый булев, а не подсчёт. Поэтому semi-join обычно дешевле, чем JOIN ... DISTINCT: он никогда не строит размноженный промежуточный результат лишь для того, чтобы снова его схлопнуть. Планировщик даже показывает это как отдельный узел Semi Join в EXPLAIN (урок join-algorithms трека databases разбирает, как он исполняется).

Anti-join: «нет совпадения» — дополнение

Зеркало semi-join — это anti-join: вернуть левые строки, у которых нет совпадения. «Пользователи, которые никогда не заказывали». Пишется как NOT EXISTS или NOT IN:

-- Пользователи, никогда не заказывавшие (anti-join через NOT EXISTS)
SELECT u.id, u.name
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Это то же множество, что и паттерн LEFT JOIN ... WHERE o.id IS NULL из прошлого урока, и планировщик часто исполняет их одинаково (узел Anti Join). NOT EXISTS — самый ясный способ это выразить и иммунен к NULL-ловушке ниже.

Ловушка NOT IN + NULL — причина существования этого урока

NOT IN выглядит естественным anti-join, и он работает нормально — пока подзапрос не вернёт хотя бы один NULL. Тогда он молча возвращает ноль строк, ровно баг прода из хука.

-- ОПАСНО: если у любого заказа user_id равен NULL, это вернёт НИЧЕГО
SELECT u.id FROM users u
WHERE u.id NOT IN (SELECT user_id FROM orders);

Причина — трёхзначная логика. x NOT IN (a, b, NULL) разворачивается в x <> a AND x <> b AND x <> NULL. Последнее сравнение x <> NULL это UNKNOWN, никогда не истинно, поэтому весь AND не может стать истинным — в лучшем случае это UNKNOWN, что WHERE отбрасывает. Один NULL в списке делает предикат невыполнимым для каждой строки. Ошибки нет; результат просто схлопывается.

У NOT EXISTS этого изъяна нет: он спрашивает «есть ли коррелированная совпадающая строка?» на каждую левую строку, и NULL справа просто не совпадает, что и есть корректное поведение anti-join. Производственное правило: никогда не пиши NOT IN против подзапроса, чья колонка может быть NULL — используй NOT EXISTS. (Обычный IN безопасен; ловушка специфична для NOT IN.) Если NOT IN обязателен, защити его через WHERE user_id IS NOT NULL внутри подзапроса — но NOT EXISTS проще и быстрее.