Coding Interview Prep · Урок

SELF JOIN для иерархий

Объединяйте таблицу с самой собой, чтобы моделировать связи сотрудник — руководитель и родитель — потомок.

«SELF JOIN для иерархий» — бесплатный урок Coding Interview Prep на CoddyKit. Это урок 2 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения Coding Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс Coding Interview Prep содержит 4 уроков всего.

Что на самом деле представляет собой SELF JOIN

Самосоединение — это обычное соединение, в котором таблица находится по обе стороны. Специального ключевого слова SELF JOIN нет: вы записываете обычное INNER или LEFT JOIN и обращаетесь к одной и той же таблице дважды.

Трюк, благодаря которому это работает, — псевдонимы таблиц. Каждой копии назначается свой псевдоним, чтобы система воспринимала их как две независимые таблицы.

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

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

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

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

-- e = the employee, m = that employee's manager
SELECT e.id, e.name, m.name AS reports_to
FROM employees AS e
JOIN employees AS m ON e.manager_id = m.id;

Модель «сотрудник — руководитель»

Классический сценарий самосоединения — это список смежности: одна таблица хранит строки, и каждая строка указывает на родительскую через внешний ключ в той же таблице.

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

-- One table holds the whole hierarchy
-- employees(id, name, manager_id)
-- manager_id -> employees.id

Вывод всех сотрудников вместе с руководителями

Самый частый вопрос о самосоединении: показать каждого сотрудника рядом с именем его руководителя. Соедините копию сотрудников с копией руководителей по условию e.manager_id = m.id.

Это вернёт по одной строке для каждого сотрудника, у которого есть руководитель. Обратите внимание: самый верхний уровень организации, CEO, имеет NULL в manager_id и будет исключён внутренним соединением.

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

LEFT JOIN для сохранения вершины дерева

Чтобы включить CEO, у которого manager_id равен NULL, переключитесь на LEFT JOIN. Сторона сотрудников сохраняется, а столбцы руководителя возвращают NULL для строк без родителя.

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

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

Подсчёт непосредственных подчинённых у каждого руководителя

Частый дополнительный вопрос: сколько людей непосредственно подчиняется каждому руководителю? Выполните самосоединение, а затем сгруппируйте строки по руководителю.

Мы соединяем работников с руководителями, группируем по идентификатору руководителя и считаем работников. Это подсчитывает только непосредственных подчинённых, а не всё поддерево под ними.

SELECT m.name AS manager, COUNT(*) AS direct_reports
FROM employees e
JOIN employees m ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY direct_reports DESC;

Переход на два уровня вглубь

Чтобы получить сотрудника, его руководителя и руководителя его руководителя, соедините три копии таблицы. Каждый уровень — ещё одно самосоединение.

Это работает для фиксированной, заранее известной глубины. Если нужна произвольная глубина, самосоединения недостаточно — это сигнал использовать рекурсивный CTE, о котором интервьюеры ожидают, что вы упомянете.

SELECT e.name AS employee,
       m.name AS manager,
       g.name AS grand_manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
LEFT JOIN employees g ON m.manager_id = g.id;

Самосоединение и рекурсивный CTE

Ключевое различие, которое проверяют на собеседованиях:

Самосоединение обрабатывает фиксированное число уровней. Три копии = три уровня, не больше.
Рекурсивный CTE обрабатывает неограниченную глубину, повторно соединяя таблицу с самой собой, пока новые строки не перестанут появляться.

Поэтому «показать каждого сотрудника и его непосредственного руководителя» — это самосоединение, а для «перечислить всех предков в цепочке» нужна рекурсия.

Категории «родитель — потомок»

Та же схема моделирует любое дерево: категории товаров, ветки комментариев, географические регионы. Таблица categories со столбцом parent_id, ссылающимся на собственный id, имеет ту же структуру, что и случай с сотрудниками и руководителями.

Полезный универсальный вывод: «таблица с внешним ключом, ссылающимся на саму себя» означает «самосоединение или рекурсия».

SELECT c.name AS category,
       p.name AS parent_category
FROM categories c
LEFT JOIN categories p ON c.parent_id = p.id;

Типичные ошибки самосоединения

Обратите внимание на это на собеседованиях:

Забыть псевдонимы, что приводит к ошибкам неоднозначности столбцов.
Использовать INNER JOIN и незаметно исключить корневые строки (родитель NULL).
Соединить в неправильном направлении: e.id = m.manager_id вместо e.manager_id = m.id.

Перед написанием ON всегда вслух называйте, какой псевдоним обозначает потомка, а какой — родителя.

Когда использовать самосоединение

Обращайтесь к самосоединению, когда таблица связывает строки с другими строками в той же таблице:

Иерархии с одним фиксированным уровнем поиска (сотрудник — руководитель).
Сопоставление или сравнение строк одной таблицы (это будет в следующем уроке).

Если связь рекурсивная и неограниченная, назовите рекурсивный CTE более подходящим инструментом. Эта тонкость отличает начинающих разработчиков от специалистов среднего уровня.

Проверка

Проверьте понимание самосоединений в иерархиях.

Итоги: SELF JOIN для иерархий

Основные выводы:

Самосоединение — это обычное соединение с одной и той же таблицей по обе стороны, различаемой с помощью псевдонимов.
Список смежности (внешний ключ, ссылающийся на себя, например manager_id) моделирует дерево в одной таблице.
Используйте INNER JOIN для сопоставленных пар и LEFT JOIN, чтобы сохранять корневые строки с родителями NULL.
Самосоединения обрабатывают фиксированную глубину; для неограниченного обхода нужен рекурсивный CTE.

Часто задаваемые вопросы

Урок «SELF JOIN для иерархий» бесплатный?

Да — полный текст урока «SELF JOIN для иерархий» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс Coding Interview Prep, подпишись на CoddyKit PRO. Курс Coding Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «SELF JOIN для иерархий»?

Объединяйте таблицу с самой собой, чтобы моделировать связи сотрудник — руководитель и родитель — потомок. Ты практикуешь Coding Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать Coding Interview Prep?

Предыдущий опыт не требуется. Coding Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 2 из 4.

Сколько времени занимает урок «SELF JOIN для иерархий»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке Coding Interview Prep?

Да. Каждый урок Coding Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

← Назад к Coding Interview Prep