Самосоединение (self-join)

Self-join: как соединить таблицу саму с собой для иерархий и поиска пар.

Self-join — это JOIN таблицы с самой собой. Чтобы различать «две копии», им дают разные алиасы.

Зачем нужно

Если в таблице есть ссылка на строку той же таблицы (сотрудник → его руководитель, категория → родитель), достать связанную строку можно только соединив таблицу саму с собой.

Иерархия: сотрудник и его руководитель

В таблице employees колонка manager_id ссылается на id другого сотрудника. Соединим таблицу с собой: одна копия — сотрудник (e), вторая — его руководитель (m).

CREATE TABLE employees (
    id         INTEGER PRIMARY KEY,
    name       TEXT,
    manager_id INTEGER     -- ссылка на id другого сотрудника
);
INSERT INTO employees (id, name, manager_id) VALUES
    (1, 'Директор', NULL),
    (2, 'Аня',  1),
    (3, 'Борис',1),
    (4, 'Вера', 2);

SELECT e.name AS sotrudnik, m.name AS rukovoditel
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.id;

Вывод:

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

У каждого сотрудника подставилось имя его руководителя. У Директора руководителя нет — взяли LEFT JOIN, чтобы он не выпал, и в колонке rukovoditel у него NULL.

Поиск пар без повторов

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

CREATE TABLE players (id INTEGER PRIMARY KEY, name TEXT, city TEXT);
INSERT INTO players (name, city) VALUES
    ('Аня','Москва'), ('Борис','Москва'), ('Вера','Казань'), ('Гена','Москва');

-- Все пары игроков из одного города, без повторов и без пары с собой
SELECT a.name AS igrok1, b.name AS igrok2, a.city
FROM players a
JOIN players b ON a.city = b.city AND a.id < b.id
ORDER BY a.city, a.name, b.name;

Вывод:

Аня|Борис|Москва
Аня|Гена|Москва
Борис|Гена|Москва

Условие a.id < b.id делает три вещи сразу: убирает пару игрока с самим собой, убирает зеркальный дубль (Аня–Борис и Борис–Аня — это одна пара) и фиксирует порядок внутри пары.

Итог

Self-join — соединение таблицы с собой; обязательно разные алиасы для двух «копий».
Иерархии (сотрудник→руководитель) разворачиваются через self-join; берите LEFT JOIN, чтобы вершина не выпала.
Для пар без повторов добавляйте a.id < b.id.