Самосоединение

Учимся соединять таблицу саму с собой для иерархических данных.

Самосоединение (self-join) — это JOIN таблицы с самой собой; разные роли одной таблицы различают псевдонимами.

Когда нужно соединять таблицу с собой

Иногда строки таблицы ссылаются на другие строки той же таблицы. Самый частый случай — иерархия: у сотрудника есть руководитель, который тоже сотрудник. Тогда столбец manager_id указывает на id другой строки этой же таблицы employees.

Пример: сотрудники и руководители

Чтобы рядом с сотрудником показать имя его руководителя, мы соединяем employees с employees, дав им разные псевдонимы: e — сам сотрудник, m — его менеджер. Берём LEFT JOIN, чтобы директор без руководителя тоже попал в результат.

CREATE TABLE employees (
    id         INTEGER PRIMARY KEY,
    name       TEXT,
    manager_id INTEGER   -- ссылка на employees.id
);

INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Директор', NULL),
    (2, 'Аня',      1),
    (3, 'Борис',    1),
    (4, 'Вера',     2);

SELECT e.name AS сотрудник, m.name AS руководитель
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id;

Вывод:

Директор|
Аня|Директор
Борис|Директор
Вера|Аня

Каждая строка соединяется со строкой своего менеджера. У директора manager_id равен NULL, поэтому пара справа отсутствует и LEFT JOIN подставил пустое значение.

Почему обязательны псевдонимы

Без псевдонимов база не поняла бы, к какому «экземпляру» таблицы относится столбец: id сотрудника или id менеджера. Псевдонимы e и m делают это однозначным. По сути мы работаем с двумя «копиями» одной таблицы, и каждая нужна для своей роли.

Другие применения

Самосоединение пригодится не только для иерархий. Им можно, например, находить пары строк по условию: товары из одной категории с близкой ценой, сообщения и ответы на них, события «до» и «после». Главная идея всегда одна — сопоставить строки таблицы между собой.

Итог

• Самосоединение — это JOIN таблицы с самой собой через разные псевдонимы.
• Классический случай — иерархия: сотрудник ↔ руководитель в одной таблице.
• LEFT JOIN при этом сохраняет строки без пары (например, директора без менеджера).