SELF JOIN: соединение таблицы с собой

Все предыдущие виды JOIN соединяли разные таблицы. Но иногда нужно соединить таблицу саму с собой — сравнить строки внутри одной таблицы или обработать иерархические данные. Для этого используют SELF JOIN — не отдельный SQL-оператор, а применение любого JOIN к одной таблице дважды.

Зачем соединять таблицу с собой

Классическая задача — организационная иерархия. Каждый сотрудник имеет менеджера, который сам является сотрудником в той же таблице:

employees:
id | name      | manager_id | department
---+-----------+------------+-----------
1  | Директор  | NULL       | Топ-менеджмент
2  | Анна      | 1          | Разработка
3  | Борис     | 2          | Разработка
4  | Вера      | 2          | Разработка
5  | Гриша     | 1          | HR

manager_id — внешний ключ, ссылающийся на id в той же таблице. Директор не имеет менеджера (NULL). Анна подчиняется директору, Борис и Вера — Анне.

Синтаксис SELF JOIN

Поскольку в запросе появляется одна таблица дважды, нужно дать ей два разных псевдонима:

SELECT e.name    AS employee,
       m.name    AS manager
FROM   employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY m.name NULLS LAST, e.name;

Здесь e — «сотрудник», m — «менеджер». Обе ссылаются на одну таблицу employees. LEFT JOIN нужен, чтобы директор (с manager_id = NULL) тоже попал в результат.

Результат:

employee  | manager
----------+-----------
Анна      | Директор
Гриша     | Директор
Борис     | Анна
Вера      | Анна
Директор  | NULL

Сравнение строк внутри таблицы

Второй сценарий для SELF JOIN — сравнение строк между собой. Например, найти сотрудников из одного отдела с разными зарплатами:

SELECT a.name AS emp1,
       b.name AS emp2,
       a.salary AS salary1,
       b.salary AS salary2
FROM   employees a
JOIN   employees b ON a.department = b.department
                  AND a.id < b.id    -- избегаем дублей и самосравнения
WHERE  ABS(a.salary - b.salary) > 20000;

a.id < b.id — важное условие. Без него каждая пара появится дважды (a,b и b,a), и каждый сотрудник сравнится сам с собой. a.id < b.id гарантирует, что каждая уникальная пара появится только раз.

Проверь себя: что вернёт запрос, если заменить a.id < b.id на a.id != b.id?

Иерархия: найти всех подчинённых менеджера

Можно использовать SELF JOIN для поиска прямых подчинённых конкретного менеджера:

-- Прямые подчинённые менеджера с id=2 (Анна)
SELECT e.id, e.name, e.department
FROM   employees e
JOIN   employees m ON e.manager_id = m.id
WHERE  m.id = 2;

Результат:

id | name  | department
---+-------+-----------
3  | Борис | Разработка
4  | Вера  | Разработка

Это только прямые подчинённые — один уровень вниз по иерархии. Для полного обхода дерева (все уровни) нужны рекурсивные запросы — более продвинутая тема.

Практический пример: найти сотрудников с тем же менеджером

Задача: найти «коллег» — пары сотрудников, у которых один менеджер:

SELECT a.name AS colleague1,
       b.name AS colleague2,
       m.name AS shared_manager
FROM   employees a
JOIN   employees b ON a.manager_id = b.manager_id
                  AND a.id < b.id
JOIN   employees m ON a.manager_id = m.id
ORDER BY m.name, a.name;

Здесь три экземпляра одной таблицы: a и b — пара коллег, m — их общий менеджер.

Типичные ошибки в SELF JOIN

1. Забыть псевдоним — таблица упоминается дважды без разграничения:

-- Ошибка: СУБД не знает, из какого экземпляра читать
SELECT name, name FROM employees JOIN employees ON ...;
-- ERROR: table name "employees" specified more than once

Решение: всегда давайте псевдоним обоим экземплярам.

2. Не добавить условие a.id < b.id при сравнении пар:

Без этого условия каждая пара дублируется, и каждый сотрудник сравнивается сам с собой:

-- Плохо: Борис/Вера и Вера/Борис появятся оба, и Борис/Борис тоже
SELECT a.name, b.name FROM employees a JOIN employees b ON a.department = b.department;

-- Правильно: только уникальные пары
SELECT a.name, b.name FROM employees a JOIN employees b
ON a.department = b.department AND a.id < b.id;

3. INNER JOIN вместо LEFT JOIN для корневых узлов:

Если нужно видеть узлы без «родителей» (например, директора с manager_id = NULL), используйте LEFT JOIN:

-- INNER JOIN: директор не появится (нет совпадения)
SELECT e.name, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.id;

-- LEFT JOIN: директор появится с NULL в колонке manager
SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

Пример с многоуровневой иерархией: два уровня через двойной SELF JOIN

Если нужно получить не только менеджера, но и менеджера менеджера (два уровня вверх), используют два SELF JOIN подряд:

SELECT e.name    AS employee,
       m.name    AS manager,
       gm.name   AS grand_manager
FROM   employees e
LEFT JOIN employees m  ON e.manager_id  = m.id
LEFT JOIN employees gm ON m.manager_id  = gm.id
ORDER BY gm.name NULLS LAST, m.name NULLS LAST, e.name;

Три псевдонима: e — сотрудник, m — его менеджер, gm — менеджер менеджера (grand manager). LEFT JOIN нужен дважды, чтобы сотрудники верхних уровней иерархии не исчезали из результата.

Результат для нашей таблицы:

employee  | manager   | grand_manager
----------+-----------+--------------
Борис     | Анна      | Директор
Вера      | Анна      | Директор
Анна      | Директор  | NULL
Гриша     | Директор  | NULL
Директор  | NULL      | NULL

На трёх уровнях иерархии — три экземпляра одной таблицы. Это работает, но становится громоздким при большей глубине.

Краткий итог

SELF JOIN — это JOIN таблицы самой с собой, не отдельный оператор SQL
Требует двух псевдонимов для одной таблицы: FROM employees a JOIN employees b
Применяется: иерархические данные (менеджер-подчинённый), сравнение строк внутри таблицы
Условие a.id < b.id предотвращает дублирование пар и самосравнение
Корневые узлы (без пары) требуют LEFT JOIN вместо INNER JOIN

Ментальная модель: таблица как «список участников»

Полезная аналогия для SELF JOIN — представьте таблицу как список людей. SELF JOIN — это как попросить каждого человека из списка пожать руку каждому другому (или только одному конкретному). a.id < b.id — это «рукопожатие только один раз, не самому себе».

Для иерархии (менеджер-подчинённый) представьте, что список сотрудников — это и «список работников», и «справочник менеджеров». Вы соединяете «работника» со «справочником менеджеров» по manager_id = id.

SELF JOIN vs другие подходы

SELF JOIN — не единственный способ работать с иерархиями. Для небольших глубин (1-2 уровня) он прекрасно подходит. Для произвольно глубоких деревьев (например, категории товаров с неизвестной глубиной вложенности) нужны рекурсивные запросы — более продвинутая техника, которая появится в поздних модулях.

Для задач сравнения строк внутри таблицы SELF JOIN иногда можно заменить оконными функциями — они вычисляют значения «соседних» строк без явного соединения. Но для базовых задач SELF JOIN проще и понятнее.

Реальный пример: найти «дорогие» и «дешёвые» варианты одного товара

Допустим, есть таблица цен на один и тот же товар от разных поставщиков. SELF JOIN позволяет найти поставщика с максимальной и минимальной ценой:

SELECT cheap.supplier AS cheapest_supplier,
       cheap.price    AS min_price,
       expensive.supplier AS priciest_supplier,
       expensive.price    AS max_price,
       expensive.price - cheap.price AS price_diff
FROM   supplier_prices cheap
JOIN   supplier_prices expensive ON cheap.product_id = expensive.product_id
                                AND cheap.price < expensive.price
ORDER BY price_diff DESC;

Этот запрос для каждого продукта находит все пары (дешёвый поставщик, дорогой поставщик) и вычисляет разницу в цене. Бизнес-аналитик сразу видит, где можно сэкономить при выборе поставщика.

Когда задача требует SELF JOIN

Узнать, нужен ли SELF JOIN, просто: если в условии задачи звучит «сравни строки из одной таблицы» или «у каждой строки есть ссылка на другую строку в той же таблице» — это сигнал для SELF JOIN.

Примеры таких задач:

«Найди сотрудников, которые зарабатывают больше своего менеджера»
«Найди товары с одинаковой категорией, но разной ценой»
«Покажи все пары клиентов из одного города»

Что дальше

Вы изучили все виды JOIN. Завершающий урок модуля — многотабличные запросы и алиасы: как соединять три и более таблиц, поддерживать читаемость запроса и избегать типичных ловушек в сложных запросах.

SELF JOIN: соединение таблицы с собой