Часто запрос звучит не как «соедини две таблицы», а как «верни строки из A, для которых есть (или нет) пара в B». Тебе не нужны колонки из B — только сам факт существования или отсутствия пары. Это специальный класс соединений с собственным именем: semi-join и anti-join.

В SQL у них нет отдельного ключевого слова — они выражаются через несколько эквивалентных идиом. Этот урок — про то, какие они, в чём разница, и какую выбирать.

Semi-join: «у этой строки есть пара»

Semi-join возвращает строки из A, для которых существует хотя бы одна пара в B. Колонок B в результате нет.

Формально: $A \ltimes B = \{ a \in A \mid \exists b \in B : p(a, b) \}$

В SQL это пишется двумя способами: EXISTS (подзапрос) или IN (список).

Обрати внимание на SELECT 1 в подзапросе EXISTS. Что именно стоит после SELECT — не важно, потому что EXISTS смотрит только на «есть строка или нет». Можно писать SELECT *, SELECT 1, SELECT 'x' — всё одинаково. Идиоматичный стиль — SELECT 1.

Anti-join: «у этой строки нет пары»

Anti-join — зеркало semi-join’а. Возвращает строки из A, для которых нет пары в B.

Формально: $A \triangleright B = \{ a \in A \mid \neg \exists b \in B : p(a, b) \}$

В SQL три способа написать anti-join, и выбор между ними иногда влияет на корректность.

Способ 1: NOT EXISTS

Это рекомендованный способ. NOT EXISTS корректно работает с NULL и обычно даёт хороший план.

Способ 2: LEFT JOIN + IS NULL

Логика: LEFT JOIN сохраняет всех клиентов. Тех, у кого нет пары в orders, получают o.id IS NULL. Этих и отбираем.

Эта идиома работает корректно при условии, что выбираемая колонка справа гарантированно NOT NULL в реальных строках. o.id — это PRIMARY KEY, он не может быть NULL у реального заказа, значит IS NULL однозначно сигнализирует «пары не было». Если бы мы написали WHERE o.placed_at IS NULL и placed_at мог бы быть NULL у реального заказа — мы бы перепутали «нет пары» и «пара есть, но дата отсутствует». Бери всегда PRIMARY KEY правой таблицы для этой проверки.

Способ 3: EXCEPT

EXCEPT возвращает только колонки, явно указанные в SELECT, поэтому удобен, когда нужны только ключи. Если хочешь дополнительные колонки — приходится оборачивать в подзапрос: SELECT * FROM customers WHERE id IN (SELECT id FROM customers EXCEPT SELECT customer_id FROM orders).

Способ 4: NOT IN — ОПАСЕН

Многие пишут WHERE c.id NOT IN (SELECT customer_id FROM orders). Это работает, пока в подзапросе нет ни одного NULL. Если в orders.customer_id появится хоть один NULL — запрос внезапно вернёт пустой результат, потому что c.id NOT IN (..., NULL, ...) для любой c.id это NULL, а не TRUE.

Правило: никогда не используй NOT IN с подзапросом, если ты не уверен на 100%, что NULL там быть не может. И даже если уверен — лучше пиши NOT EXISTS. Это та же логика, но без NULL-ловушки.

Эквивалентность и выбор

Все четыре формы semi/anti-join’а логически делают одно и то же (с оговоркой про NULL в NOT IN). На современных PostgreSQL NOT EXISTS и LEFT JOIN + IS NULL оптимизатор обычно превращает в один и тот же план — anti-join. NOT IN оптимизируется хуже и опасен с NULL. EXCEPT требует совпадения схем.

Простая шпаргалка:

• EXISTS / IN — для semi-join’а, оба нормальные.
• NOT EXISTS — для anti-join’а, дефолт.
• LEFT JOIN + IS NULL — допустимо, особенно если уже идёт цепочка JOIN’ов.
• NOT IN — избегай.

Семантическое отличие от INNER JOIN

Очень частая ошибка: использовать INNER JOIN, чтобы «найти клиентов с заказами».

-- Плохо: дубликаты, если у клиента несколько заказов
SELECT c.id, c.full_name
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Этот запрос для клиента с 5 заказами вернёт 5 строк с одним и тем же c.id и c.full_name. Часто это не то, что нужно. Чтобы получить каждого клиента ровно один раз — DISTINCT или EXISTS. И EXISTS обычно лучше: он явно говорит «мне нужны только клиенты, неважно сколько у них заказов».

Правило большого пальца: если из правой таблицы тебе ничего не нужно, кроме факта существования — пиши EXISTS. Не пиши JOIN ... DISTINCT.

Чек-лист

• Semi-join = «есть пара»: EXISTS, IN. Колонки правой таблицы в результате не нужны.
• Anti-join = «нет пары»: NOT EXISTS (предпочтительно), LEFT JOIN + IS NULL, EXCEPT.
• NOT IN с подзапросом — опасно при NULL. Заменяй на NOT EXISTS.
• В LEFT JOIN + IS NULL проверяй колонку, которая в реальных строках точно NOT NULL (например, PRIMARY KEY).
• Если правая таблица в результате не нужна — пиши EXISTS/NOT EXISTS, а не JOIN + DISTINCT.