Условие

Таблица employees(emp_id, name, salary, manager_id) — у каждого сотрудника есть manager_id, ссылающийся на emp_id другого сотрудника (NULL для топа). Напишите запросы:

Сотрудники, чья зарплата выше, чем у их прямого менеджера.
Имя сотрудника + имя его менеджера (одной строкой).
Все подчинённые (прямо или транзитивно) сотрудника 'Vasya' — рекурсивный CTE.

Решение

1) Зарплата выше менеджера

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

Self-join: одна и та же таблица под двумя алиасами (e, m).

2) Сотрудник + менеджер (топы тоже должны попасть)

SELECT e.name AS employee,
       COALESCE(m.name, '<top>') AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id;

LEFT JOIN чтобы у топ-менеджеров (manager_id IS NULL) не пропала строка.

3) Все подчинённые Vasya (рекурсивный CTE)

WITH RECURSIVE subords AS (
    -- стартовая точка: сам Vasya
    SELECT emp_id, name, manager_id, 0 AS depth
    FROM employees
    WHERE name = 'Vasya'
 
    UNION ALL
 
    -- шаг рекурсии: все подчинённые предыдущего слоя
    SELECT e.emp_id, e.name, e.manager_id, s.depth + 1
    FROM employees e
    JOIN subords s ON e.manager_id = s.emp_id
    WHERE s.depth < 20                              -- предохранитель от циклов
)
SELECT emp_id, name, depth
FROM subords
WHERE depth > 0                                     -- сам Vasya исключён
ORDER BY depth, name;

Как работает рекурсивный CTE

Anchor member — стартовая строка (Vasya).
Recursive member — берёт результат предыдущего шага, ищет тех, у кого manager_id совпадает с emp_id из предыдущего слоя.
Повторяется, пока не вернёт пустоту.
UNION ALL объединяет все слои.

Уровень иерархии каждого сотрудника

WITH RECURSIVE hierarchy AS (
    SELECT emp_id, name, manager_id, 1 AS lvl, ARRAY[emp_id] AS path
    FROM employees
    WHERE manager_id IS NULL
 
    UNION ALL
 
    SELECT e.emp_id, e.name, e.manager_id, h.lvl + 1, h.path || e.emp_id
    FROM employees e
    JOIN hierarchy h ON h.emp_id = e.manager_id
    WHERE NOT e.emp_id = ANY(h.path)               -- защита от циклов
)
SELECT * FROM hierarchy ORDER BY lvl, name;

ARRAY хранит путь — защищает от циклов (если в данных всё-таки появится e.manager_id цепляющий на потомка).

Подводные камни

Цикл в иерархии (A→B, B→A) — без depth < N или NOT IN (path) рекурсия зацикливается до OOM / timeout.
UNION vs UNION ALL в recursive CTE: UNION (без ALL) часто нельзя — большинство движков требуют UNION ALL.
Self-join без алиасов не скомпилируется: FROM employees JOIN employees ON ... — нужен алиас обоим.
NULL в manager_id: JOIN не сматчит, нужен LEFT JOIN.
MySQL < 8.0 не поддерживает recursive CTE — придётся через хранимую процедуру или денормализацию.
«Глубина 100» — большинство движков ограничивают глубину рекурсии (max_recursion_depth у SQL Server = 100 по умолчанию). Меняется опцией.
Производительность: для глубоких иерархий и многих запросов часто денормализуют — хранят path или materialized closure table.

Эталонный ответ

-- (1) Зарплата выше менеджера
SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id
WHERE e.salary > m.salary;
 
-- (3) Все подчинённые Vasya
WITH RECURSIVE subords AS (
    SELECT emp_id, name, 0 AS depth FROM employees WHERE name = 'Vasya'
    UNION ALL
    SELECT e.emp_id, e.name, s.depth + 1
    FROM employees e JOIN subords s ON e.manager_id = s.emp_id
    WHERE s.depth < 20
)
SELECT * FROM subords WHERE depth > 0;

Ключевые приёмы: алиасы при self-join (e, m); LEFT JOIN для сохранения топов; recursive CTE с anchor + recursive member; защита от циклов через depth-лимит или path-array.

DataLearn SQL-101: Self-join — иерархия сотрудников и менеджеров