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

Задача «найди сотрудников, которые получают больше своего руководителя» входит в любой сборник вопросов по SQL. Новичка она пугает: таблица одна, а сравнивать нужно две строки из неё. Решение называется самосоединением, и после этого урока оно станет для тебя обычным JOIN. В прошлом уроке мы разобрали, как условие меняет LEFT JOIN, здесь оно тоже пригодится. Данные берём из учебной базы блока.

Как спрашивают

В таблице employees есть manager_id, ссылка на руководителя в той же таблице. Выведи сотрудников, которые получают больше своего руководителя.

Варианты: «выведи каждого сотрудника с именем руководителя», «найди пары сотрудников из одного отдела».

Короткий ответ

Нужно соединить таблицу с ней же, дав двум копиям разные псевдонимы: employees e для сотрудника и employees m для руководителя. Условие m.id = e.manager_id склеивает сотрудника с его руководителем, после чего их колонки можно сравнивать в одной строке: WHERE e.salary > m.salary. Для пар внутри одной группы добавляют a.id < b.id, чтобы не получить строку с самим собой и пары в двух порядках.

Разбор: две копии одного списка

Представь, что у тебя распечатан список сотрудников, где у каждого написан номер руководителя. Чтобы найти имя руководителя Бориса, ты берёшь вторую копию того же списка, находишь в ней строку с номером 1 и читаешь имя: Анна. Таблица одна, а копий в голове две, с разными ролями.

Самосоединение делает то же. Псевдонимы здесь обязательны: без них PostgreSQL не поймёт, о какой копии речь, и запрос FROM employees JOIN employees упадёт с ошибкой table name "employees" specified more than once.

Ссылка таблицы на саму себя, как manager_id, встречается всюду, где есть иерархия: руководители, категории товаров, комментарии с ответами.

Сотрудник и руководитель

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

employee | manager
---------+--------
Борис    | Анна
Вера     | Анна
Дина     | Глеб
Жанна    | Анна

Четыре строки, хотя сотрудников семь. Анна, Глеб и Егор руководителя не имеют, manager_id у них NULL, и внутреннее соединение их теряет. Чтобы увидеть всех, нужен LEFT JOIN, как в уроке про виды соединений: тогда у Анны, Глеба и Егора в колонке руководителя будет NULL.

Сравнение с руководителем

Раз сотрудник и руководитель оказались в одной строке, их можно сравнивать:

SELECT e.name AS employee, m.name AS manager, m.salary - e.salary AS gap
FROM employees e
JOIN employees m ON m.id = e.manager_id
ORDER BY gap DESC, e.id;

employee | manager | gap
---------+---------+-------
Жанна    | Анна    | 120000
Борис    | Анна    |  50000
Вера     | Анна    |  50000
Дина     | Глеб    |  50000

А вот классический запрос из вопроса:

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;

На учебной базе он вернёт пустой результат: все руководители получают больше подчинённых. Пустой ответ тоже ответ, и на собеседовании его стоит проверить, а не бросаться переписывать запрос. Проверь сам: подними Дине зарплату внутри транзакции и откати её.

BEGIN;
UPDATE employees SET salary = 210000 WHERE name = 'Дина';
-- запрос выше вернёт: Дина | 210000 | Глеб | 200000
ROLLBACK;

Пары внутри отдела

Вторая частая задача: все пары коллег из одного отдела.

SELECT a.name AS first, b.name AS second, d.name AS department
FROM employees a
JOIN employees b ON a.department_id = b.department_id AND a.id < b.id
JOIN departments d ON d.id = a.department_id
ORDER BY a.id, b.id;

first | second | department
------+--------+-----------
Анна  | Борис  | Разработка
Анна  | Вера   | Разработка
Борис | Вера   | Разработка
Глеб  | Дина   | Продажи

Условие a.id < b.id решает две проблемы сразу. Без него каждый сотрудник образует пару сам с собой, а каждая настоящая пара появляется дважды: «Анна, Борис» и «Борис, Анна». Без этого условия запрос вернёт 14 строк, с a.id <> b.id 8 строк, а с a.id < b.id ровно 4 уникальные пары.