SQL Studio / Разборы SQL / SELF JOIN: где директор и почему NULL бывает разным?
SELF JOIN — это обычный JOIN таблицы с самой собой. Одной копии даём роль e (сотрудник), второй — m (руководитель), затем соединяем e.manager_id = m.employee_id. Специального оператора SELF JOIN в SQLite нет. Для полного списка сотрудников используйте LEFT JOIN: у руководителя верхнего уровня manager_id IS NULL, поэтому обычный INNER JOIN его теряет. Но NULL при отсутствии начальника и ссылка на несуществующий ID — разные ситуации, которые важно не смешать.
Одна таблица в двух ролях
В тестовых данных шесть работников. У Марины и Лады нет вышестоящего руководителя, Антон и Борис подчиняются Марине, Вера — Антону. У Глеба сохранён manager_id = 99, но человека с ID 99 в таблице нет. Этот намеренно неконсистентный крайний случай показывает, почему пропавший руководитель не обязательно означает верхний уровень. В реальной схеме составной/обычный внешний ключ может запрещать такое состояние.
Почему INNER JOIN теряет три строки
Этот исполнимый запрос покажет только работников, для которых нашлась запись руководителя. Алиасы e и m нужны, чтобы однозначно ссылаться на столбцы одной таблицы в двух ролях.
WITH sample_employees(employee_id, name, manager_id, team) AS (
VALUES (1, 'Марина', NULL, 'руководство'),
(2, 'Антон', 1, 'продукт'),
(3, 'Борис', 1, 'поддержка'),
(4, 'Вера', 2, 'аналитика'),
(5, 'Глеб', 99, 'аналитика'),
(6, 'Лада', NULL, 'поддержка')
)
SELECT e.employee_id, e.name AS employee,
m.name AS manager
FROM sample_employees AS e
JOIN sample_employees AS m ON m.employee_id = e.manager_id
ORDER BY e.employee_id;
| employee_id | employee | manager |
|---|---|---|
| 2 | Антон | Марина |
| 3 | Борис | Марина |
| 4 | Вера | Антон |
Из шести работников остались трое. Марина и Лада не имеют начальника по определению, а у Глеба ссылка сломана. Это три разных исходных строки, но все они одинаково не прошли условие INNER JOIN.
LEFT SELF JOIN возвращает и верхний уровень
Сохраним все строки со стороны сотрудника. Для ненайденного начальника SQLite заполнит столбец manager значением NULL:
WITH sample_employees(employee_id, name, manager_id, team) AS (
VALUES (1, 'Марина', NULL, 'руководство'),
(2, 'Антон', 1, 'продукт'),
(3, 'Борис', 1, 'поддержка'),
(4, 'Вера', 2, 'аналитика'),
(5, 'Глеб', 99, 'аналитика'),
(6, 'Лада', NULL, 'поддержка')
)
SELECT e.employee_id, e.name AS employee,
m.name AS manager
FROM sample_employees AS e
LEFT JOIN sample_employees AS m ON m.employee_id = e.manager_id
ORDER BY e.employee_id;
| employee_id | employee | manager |
|---|---|---|
| 1 | Марина | NULL |
| 2 | Антон | Марина |
| 3 | Борис | Марина |
| 4 | Вера | Антон |
| 5 | Глеб | NULL |
| 6 | Лада | NULL |
У Марины, Глеба и Лады справа виден NULL. Его нельзя автоматически переводить в «директор» через COALESCE: у Глеба записан конкретный, но отсутствующий ID 99. Базовый принцип фильтрации пустых соответствий разложен отдельно в материале про LEFT JOIN и WHERE.
Граничный случай: верхний уровень или испорченная ссылка?
Проверим сам manager_id и найденный ключ m.employee_id. Выражение CASE отличит две причины отсутствия имени:
WITH sample_employees(employee_id, name, manager_id, team) AS (
VALUES (1, 'Марина', NULL, 'руководство'),
(2, 'Антон', 1, 'продукт'),
(3, 'Борис', 1, 'поддержка'),
(4, 'Вера', 2, 'аналитика'),
(5, 'Глеб', 99, 'аналитика'),
(6, 'Лада', NULL, 'поддержка')
)
SELECT e.employee_id, e.name,
CASE
WHEN e.manager_id IS NULL THEN 'верхний уровень'
WHEN m.employee_id IS NULL THEN 'нет записи руководителя'
ELSE 'руководитель найден'
END AS manager_status
FROM sample_employees AS e
LEFT JOIN sample_employees AS m ON m.employee_id = e.manager_id
ORDER BY e.employee_id;
| employee_id | name | manager_status |
|---|---|---|
| 1 | Марина | верхний уровень |
| 2 | Антон | руководитель найден |
| 3 | Борис | руководитель найден |
| 4 | Вера | руководитель найден |
| 5 | Глеб | нет записи руководителя |
| 6 | Лада | верхний уровень |
Глеб — не «второй директор»: у него отсутствует строка для существующего в поле значения 99. Поэтому у верхнего уровня показываем отдельный статус, а нарушение ссылочной целостности передаём на проверку данных. Условия в этом примере отражают прямое подчинение, не всю иерархию на произвольную глубину.
Развернём соединение: сколько прямых подчинённых?
Самосоединение работает и в обратную сторону: левую роль отдадим потенциальному руководителю. Для подсчёта используем COUNT(e.employee_id), потому что COUNT(*) по LEFT JOIN дал бы «один» Борису, у которого нет ни одного подчинённого.
WITH sample_employees(employee_id, name, manager_id, team) AS (
VALUES (1, 'Марина', NULL, 'руководство'),
(2, 'Антон', 1, 'продукт'),
(3, 'Борис', 1, 'поддержка'),
(4, 'Вера', 2, 'аналитика'),
(5, 'Глеб', 99, 'аналитика'),
(6, 'Лада', NULL, 'поддержка')
)
SELECT m.employee_id, m.name,
COUNT(e.employee_id) AS direct_reports
FROM sample_employees AS m
LEFT JOIN sample_employees AS e ON e.manager_id = m.employee_id
GROUP BY m.employee_id, m.name
ORDER BY m.employee_id;
| employee_id | name | direct_reports |
|---|---|---|
| 1 | Марина | 2 |
| 2 | Антон | 1 |
| 3 | Борис | 0 |
| 4 | Вера | 0 |
| 5 | Глеб | 0 |
| 6 | Лада | 0 |
У Марины два прямых подчинённых, у Антона один. Вера не становится прямой подчинённой Марины: SELF JOIN с одним условием рассматривает только одно ребро дерева. Чтобы получить всю цепочку «начальник начальника» или глубину иерархии, понадобится рекурсивный CTE — это уже другая задача, а не скрытая магия SELF JOIN.
Проверьте себя
Добавьте в набор сотрудника с manager_id = 6. Изменится ли число прямых подчинённых Марины? Сколько строк будет в LEFT SELF JOIN? Для проверки используйте бесплатную SQLite-песочницу. Если интересует поведение пустых значений в других предикатах, откройте разбор NULL и NOT IN; для контроля числа строк пригодится диагностика повторов.
Для самостоятельной практики здесь достаточно бесплатного SQLite-тренажёра. Если нужен отдельный последовательный курс на MySQL 8, а не на SQLite, можно посмотреть курс SQL для аналитика на Stepik: функции и некоторые детали синтаксиса у движков отличаются.