Self-join — это обычный JOIN, в котором левая и правая таблицы — одна и та же реальная таблица, просто под разными алиасами. Никакой магии: то же декартово произведение, та же селекция. Просто R × R вместо R1 × R2.
Сценариев три:
- Пары строк одной таблицы (например, найти пары клиентов из одной страны).
- Комбинации для рекомендаций («кто что купил вместе»).
- Иерархии через self-referencing FK (manager_id, parent_id, replied_to_id) — это самый частый случай в реальных схемах.
Пары: найти строки в отношении к себе
Возьмём customers и найдём всех клиентов из одной страны. Это пары (c1, c2), у которых c1.country = c2.country. Без алиасов это написать невозможно — SQL не знает, какой country имеется в виду.
Все пары клиентов из одной страны. Алиасы a и b — обязательны.
Заметь подвох: в этом запросе есть пары вида (Аня, Аня). Потому что Аня = Аня по country, как и по любому атрибуту. Чтобы убрать самопары — добавь предикат a.id <> b.id.
И ещё подвох: пара (Аня, Борис) встретится дважды — как (a=Аня, b=Борис) и как (a=Борис, b=Аня). Если порядок не важен — пиши a.id < b.id (вместо <>), это даёт каждую пару ровно один раз.
Те же пары, но каждая ровно один раз и без самопар:
Этот приём — id < id для канонизации пар — используется везде: рекомендательные системы, графы, дедупликация.
Комбинации: что покупают вместе
Похожая задача — «какие пары товаров встречаются в одном заказе». Это тоже self-join, только по order_items.
Какие пары товаров чаще всего покупают в одном заказе:
Здесь self-join по order_items плюс предикат oi1.product_id < oi2.product_id устраняет и самопары (один товар сам с собой), и зеркальные дубли. Логика «коллабораторного фильтра» в одну строку.
Иерархия: manager_id
Теперь главное применение self-join’а в проде: таблицы с self-referencing FK. Самые частые имена — parent_id (категории, комментарии), manager_id (сотрудники), reply_to_id (треды), forwarded_from_id (письма).
Дальше работаем с employees из default+employees. Структура простая: у каждого сотрудника есть manager_id, который ссылается на другого сотрудника. У CEO manager_id равен NULL.
manager_id указывает на id того же отношения. CEO — корень дерева, у него manager_id NULL.
Каждый сотрудник + его непосредственный менеджер — это self-join по manager_id. Алиас e для сотрудника, m для менеджера:
Каждый сотрудник + имя его менеджера. LEFT JOIN, чтобы не потерять CEO.
LEFT JOIN критически важен. Анна — CEO, у неё manager_id IS NULL. INNER JOIN бы её выкинул, и мы получили бы 15 сотрудников вместо 16. Это классическая ошибка в orgchart-запросах.
Только один уровень — это важно
Self-join даёт ровно один шаг по иерархии: сотрудник + его прямой манагер. Если хочешь «сотрудник + менеджер менеджера», пиши ещё один self-join:
Сотрудник + менеджер + менеджер менеджера (3 уровня):
Это работает для фиксированной глубины. Но если ты хочешь «всех начальников до самого верха» — глубина заранее неизвестна. Самый глубокий сотрудник в нашей вселенной — Полина (Junior Backend), от неё до CEO — 4 уровня. У других — 3 или 2. Серия LEFT JOIN’ов фиксированной длины тут не работает.
Для обхода произвольной глубины существует рекурсивный CTE (WITH RECURSIVE). Это инструмент модуля 9, мы туда обязательно вернёмся. А пока запомни: self-join решает задачу «один шаг по иерархии» — для большего нужна рекурсия.
Подвох: NULL в self-referencing FK
CEO Анна имеет manager_id IS NULL. Если ты случайно напишешь INNER JOIN employees m ON m.id = e.manager_id, Анна пропадёт из результата. Это не «у CEO нет менеджера» — это «при INNER JOIN строка с NULL в join-колонке выбрасывается».
Правильно:
- Если нужны все сотрудники, включая корни —
LEFT JOIN. - Если нужны только сотрудники, у которых есть менеджер —
INNER JOIN(и Анна корректно исключится).
Сколько сотрудников непосредственно подчиняется каждому менеджеру:
LEFT JOIN тут нужен, чтобы и листья (Junior Backend Полина, у которой нет подчинённых) попали в первоначальный набор — но HAVING COUNT > 0 оставит только настоящих менеджеров. Если бы написали INNER JOIN, листьев бы тоже не было — что в данном случае было бы корректно, но менее общо.
Чек-лист
- Self-join — это обычный JOIN, где обе стороны — одна таблица под разными алиасами.
- Алиасы обязательны — без них SQL не различает «левую» и «правую» копии.
- Для пар без зеркальных дублей:
a.id < b.idв предикате. - Иерархии через self-referencing FK (manager_id, parent_id): один JOIN = один уровень.
- LEFT JOIN на корне дерева — критичен, иначе строка с NULL в FK теряется.
- Обход произвольной глубины — это
WITH RECURSIVE, модуль 9. Self-join тут не помогает.