Что такое Semi JOIN в PostgreSQL?

Semi JOIN в PostgreSQL

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

Принцип работы Semi JOIN

PostgreSQL не имеет явного синтаксиса SEMI JOIN, но его поведение можно смоделировать несколькими способами:

SELECT * FROM users
WHERE user_id IN (SELECT user_id FROM orders);

SELECT u.* FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);

Почему это важно

Semi JOIN отличается от INNER JOIN — может вернуть дубликаты.

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

Если у пользователя 3 заказа, он появится 3 раза. Semi JOIN вернёт каждого пользователя один раз.

Примеры с Python и SQLAlchemy

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, select
from sqlalchemy.orm import declarative_base, Session
from sqlalchemy import exists

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    user_id = Column(Integer, primary_key=True)
    name = Column(String(100))

class Order(Base):
    __tablename__ = 'orders'
    order_id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.user_id'))
    total = Column(Integer)

engine = create_engine('postgresql://user:password@localhost/mydb')

with Session(engine) as session:
    # EXISTS (предпочтительно)
    semi_join_query = select(User).where(
        exists(select(1).select_from(Order).where(Order.user_id == User.user_id))
    )
    users_with_orders = session.execute(semi_join_query).scalars().all()
    
    for user in users_with_orders:
        print(f"{user.name}")

Реальные сценарии

Покупатели, совершившие покупку в последний месяц:

from sqlalchemy import func, text
from datetime import datetime, timedelta

last_month = datetime.utcnow() - timedelta(days=30)

query = select(User).where(
    exists(
        select(1).select_from(Order).where(
            (Order.user_id == User.user_id) &
            (Order.created_at >= last_month)
        )
    )
)

result = session.execute(query).scalars().all()

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

# Пользователи БЕЗ заказов
anti_join_query = select(User).where(
    ~exists(select(1).select_from(Order).where(Order.user_id == User.user_id))
)

users_without_orders = session.execute(anti_join_query).scalars().all()

Semi JOIN vs другие типы JOIN

Оптимизация: EXISTS vs IN

import time

def benchmark_semi_join():
    with Session(engine) as session:
        # EXISTS (обычно быстрее)
        start = time.time()
        result1 = session.execute(
            select(User).where(
                exists(select(1).select_from(Order).where(Order.user_id == User.user_id))
            )
        ).scalars().all()
        exists_time = time.time() - start
        
        # IN
        start = time.time()
        subquery = select(Order.user_id).distinct()
        result2 = session.execute(
            select(User).where(User.user_id.in_(subquery))
        ).scalars().all()
        in_time = time.time() - start
        
        print(f"EXISTS: {exists_time:.4f}s")
        print(f"IN: {in_time:.4f}s")

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

1. Проверка существования — нужна только информация о том, что запись существует
2. Избежание дубликатов — когда в правой таблице может быть много совпадений
3. Производительность — Semi JOIN часто быстрее INNER JOIN с DISTINCT
4. Читаемость — EXISTS явно показывает намерение проверки существования

Semi JOIN — это мощный инструмент для написания эффективных SQL-запросов, особенно при работе с большими наборами данных.