0Pricing
SQL Academy · Урок

Ограничения самосоединений

Когда вместо этого нужна рекурсия

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

Что такое самосоединение

Самосоединение — это соединение таблицы с самой собой. Оно полезно для сравнения строк в одной таблице, например для поиска сотрудников и их руководителей, хранящихся в одной таблице employees.

Прежде чем рассматривать ограничения самосоединений, вспомним, как на практике работает простое самосоединение.

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

Один уровень вложенности

Самосоединение элегантно обрабатывает один переход в иерархии. Если нужно сопоставить каждого сотрудника с его непосредственным руководителем, достаточно одного самосоединения.

Это прекрасно работает, когда данные имеют только один уровень вложенности или когда Вас интересуют только непосредственные связи между родителем и потомком.

SELECT child.name AS employee, parent.name AS direct_manager
FROM employees child
LEFT JOIN employees parent ON child.manager_id = parent.id;

Два уровня: уже становится запутанно

Что делать, если нужны сотрудники, их руководители и руководители их руководителей? Необходимо добавить второе самосоединение. Запрос увеличивается и становится сложнее для чтения.

Для каждого дополнительного уровня иерархии требуется ещё один псевдоним соединения и ещё одно предложение JOIN.

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

Три уровня: шаблон перестаёт работать

Добавление третьего уровня требует ещё одного соединения. К этому моменту запрос становится многословным, хрупким и сложным в сопровождении. Если глубина иерархии изменится, придётся переписать весь запрос.

Это первое серьёзное ограничение самосоединений: они не масштабируются вместе с глубиной.

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

Неизвестная глубина: самосоединения не помогут

В реальных организационных структурах или деревьях категорий глубина часто неизвестна во время выполнения запроса. При самосоединениях число уровней нужно задать заранее. Если завтра иерархия станет 10-уровневой, запрос с самосоединениями для 3 уровней молча пропустит данные.

Это фундаментальное ограничение: самосоединения не могут проходить через произвольное число уровней.

-- This only retrieves up to 3 levels deep.
-- Employees deeper than level 3 are simply missing from results.
SELECT e.name, m.name, gm.name
FROM employees e
LEFT JOIN employees m  ON e.manager_id = m.id
LEFT JOIN employees gm ON m.manager_id = gm.id;

Циклы полностью нарушают работу самосоединений

Ещё одно серьёзное ограничение: если данные содержат цикл (A руководит B, B руководит C, C руководит A), запрос с самосоединением не зациклится бесконечно, но также не обнаружит и не сообщит о цикле корректно.

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

-- Cyclic data: row 3 points back to row 1
-- id | name    | manager_id
--  1 | Alice   | 3   <-- cycle!
--  2 | Bob     | 1
--  3 | Charlie | 2

-- A self join just shows one hop; it cannot detect the loop
SELECT e.name, m.name AS reports_to
FROM employees e
JOIN employees m ON e.manager_id = m.id;

Введение в рекурсивные CTE

SQL предоставляет специально предназначенное для обхода иерархий неизвестной глубины решение: рекурсивное общее табличное выражение (CTE). Для него используется синтаксис WITH RECURSIVE, поддерживаемый PostgreSQL, MySQL 8+, SQLite и SQL Server.

Рекурсивное CTE состоит из двух частей: базовой части (исходных строк) и рекурсивной части (шага, который следует за каждой связью).

WITH RECURSIVE org_tree AS (
  -- Anchor: start with the top-level CEO (no manager)
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find each employee whose manager is already in org_tree
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, depth FROM org_tree ORDER BY depth;

Отслеживание полного пути

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

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id,
         name AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id,
         ot.path || ' > ' || e.name
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT name, path FROM org_tree ORDER BY path;

Самосоединение или рекурсивное CTE: что выбрать

Используйте самосоединение, когда:

  • Вам нужен ровно один или два уровня иерархии.
  • Глубина фиксирована и заранее известна.
  • Вам нужна простота без накладных расходов, связанных с CTE.

Используйте рекурсивное CTE, когда:

  • Глубина переменна или неизвестна.
  • Вам нужен полный путь предков или потомков.
  • Вам нужно обнаружение циклов с помощью предложения CYCLE или ручных проверок.

Особенности производительности

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

Рекурсивные CTE более гибки, но могут быть затратными для глубоких или широких деревьев. Всегда добавляйте проверку ограничения глубины в рекурсивную часть, чтобы предотвратить неконтролируемое выполнение запросов из-за плохих данных или неожиданных циклов.

WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
  WHERE ot.depth < 10   -- safety guard: stop at depth 10
)
SELECT name, depth FROM org_tree;

Практические случаи, требующие рекурсии

Многие распространённые модели данных требуют обхода на произвольную глубину, с которым самосоединения просто не справляются:

  • Деревья категорий — вложенные категории товаров в каталоге интернет-магазина.
  • Спецификации изделий — изделие, состоящее из деталей, каждая из которых в свою очередь состоит из поддеталей.
  • Ветки комментариев — ответы на ответы на ответы.
  • Пути файловой системы — каталоги внутри каталогов.

Во всех этих случаях используйте рекурсивное CTE, а не последовательность самосоединений.

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, name AS full_path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.id, c.name, c.parent_id,
         ct.full_path || ' / ' || c.name
  FROM categories c
  JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, name, full_path FROM category_tree ORDER BY full_path;

Проверка знаний

Проверьте, насколько хорошо Вы поняли ограничения самосоединений и когда вместо них следует использовать рекурсивные CTE.

Итоги урока

В этом уроке Вы узнали об ограничениях самосоединений при работе с иерархическими данными:

  • Самосоединения хорошо подходят для одного или двух фиксированных уровней иерархии.
  • Для каждого дополнительного уровня требуется ещё одно явное JOIN, из-за чего запросы становятся хрупкими и сложными в сопровождении.
  • Самосоединения не поддерживают неизвестную глубину — строки за пределами заданных уровней молча исключаются.
  • Они не защищают от циклических ссылок в данных.
  • Если глубина переменна или неизвестна, вместо них используйте рекурсивное CTE (WITH RECURSIVE).
  • Всегда добавляйте ограничитель глубины в рекурсивные запросы, чтобы защититься от неконтролируемого выполнения.

Умение определить, когда перейти от самосоединения к рекурсивному CTE, — важный навык для работы с любыми древовидными данными в SQL.

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

Урок «Ограничения самосоединений» бесплатный?

Да — полный текст урока «Ограничения самосоединений» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Academy, подпишись на CoddyKit PRO. Курс SQL Academy содержит 4 уроков всего.

Чему я научусь в уроке «Ограничения самосоединений»?

Когда вместо этого нужна рекурсия Ты практикуешь SQL Academy с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Academy?

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

Сколько времени занимает урок «Ограничения самосоединений»?

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

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

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

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

  1. Что такое самосоединение
  2. Сотрудники и руководители
  3. Сравнение строк в одной таблице
  4. Ограничения самосоединений
← Назад к SQL Academy