Сталкивался ли с Semi JOIN

Semi JOIN в SQL

Да, я сталкивался с Semi JOIN при работе с SQL-запросами через ORM (SQLAlchemy) и при оптимизации сложных запросов к базам данных. Это очень полезный паттерн для повышения производительности.

Что такое Semi JOIN

Semi JOIN — это тип объединения таблиц, который возвращает строки из левой таблицы, только если в правой таблице существуют соответствующие значения. Отличие от обычного INNER JOIN в том, что Semi JOIN не дублирует строки — каждая строка из левой таблицы вернётся максимум один раз.

Разница между INNER JOIN и Semi JOIN

-- INNER JOIN (может вернуть дубли если у одного пользователя много заказов)
SELECT users.id, users.name, COUNT(*) as order_count
FROM users
INNER JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

-- Semi JOIN (каждый пользователь вернётся один раз)
SELECT users.id, users.name
FROM users
WHERE EXISTS (
    SELECT 1 FROM orders WHERE orders.user_id = users.id
);

Реализация в SQL

Semi JOIN реализуется несколькими способами:

1. Using EXISTS (самый эффективный для больших наборов)

SELECT u.id, u.name, u.email
FROM users u
WHERE EXISTS (
    SELECT 1 
    FROM orders o 
    WHERE o.user_id = u.id
    AND o.created_at > '2024-01-01'
);

2. Using IN (проще для маленьких наборов)

SELECT u.id, u.name
FROM users u
WHERE u.id IN (
    SELECT DISTINCT user_id FROM orders
);

3. Using INNER JOIN с DISTINCT

SELECT DISTINCT u.id, u.name
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

Работа с Semi JOIN в Python через SQLAlchemy

from sqlalchemy import select, exists
from models import User, Order

# Способ 1: EXISTS (рекомендуется)
stmt = select(User).where(
    exists(
        select(1).select_from(Order)
        .where(Order.user_id == User.id)
    )
)
result = session.execute(stmt).scalars().all()

# Способ 2: IN с подзапросом
subquery = select(Order.user_id).distinct()
stmt = select(User).where(User.id.in_(subquery))
result = session.execute(stmt).scalars().all()

Когда использовать Semi JOIN

Когда Semi JOIN эффективнее:

• Нужно отфильтровать строки левой таблицы по наличию связанных данных
• Не нужны столбцы из правой таблицы
• Большой объём данных в правой таблице
• Один элемент левой таблицы связан с множеством элементов правой

Примеры сценариев:

• Найти всех пользователей, которые хоть раз совершили покупку
• Выбрать все категории, в которых есть активные товары
• Получить все проекты, в которых есть открытые задачи

Anti JOIN (противоположность Semi JOIN)

Есть также Anti JOIN — противоположность Semi JOIN. Он возвращает строки, для которых НЕ существуют соответствия:

-- Anti JOIN: пользователи без заказов
SELECT u.id, u.name
FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Производительность

Важный момент: для больших таблиц EXISTS обычно быстрее IN, потому что:

• EXISTS может прекратить поиск после первого совпадения
• IN требует построения полного списка значений

Это критично при оптимизации сложных запросов с большими наборами данных.