CONNECT BY в Oracle SQL: как строить иерархии и древовидные структуры

ORACLE

🟢 CONNECT BY в Oracle SQL. Введение

CONNECT BY — механизм иерархических запросов в Oracle SQL. Вместе с START WITH, ключевым словом PRIOR и псевдостолбцом LEVEL он строит дерево или граф по родительско‑дочерним связям:
оргструктуры, каталоги, BOM, треды комментариев, сети.

Где используют

  • Организационные и продуктовые деревья: начальники → подчинённые, разделы → подразделы.
  • BOM/спецификации: развёртка состава изделия по уровням.
  • Навигация и пути: «хлебные крошки», путь от корня до узла.
  • Аналитика: агрегации на поддеревьях, счёт потомков, отметка листьев.

Синтаксис

SELECT ...
FROM t
START WITH root_condition
CONNECT BY [NOCYCLE] PRIOR parent_expr = child_expr [ AND ... ]
[ ORDER SIBLINGS BY ... ]
  • PRIOR ставится у стороны связи, принадлежащей родителю: PRIOR emp_id = manager_id.
  • LEVEL — номер уровня (корень = 1); CONNECT_BY_ROOT — значение у корня;
  • SYS_CONNECT_BY_PATH(col,'/') — путь от корня; CONNECT_BY_ISLEAF — признак листа.
  • NOCYCLE — защищает от циклов (см. также CONNECT_BY_ISCYCLE).

100 примеров

1. Дерево сотрудников с устойчивой сортировкой соседей

SELECT
  LPAD(' ', (LEVEL-1)*2) || emp_name AS emp_name,
  emp_id, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY emp_name;

2. Предки конкретного сотрудника (обратный подъём)

SELECT emp_id, manager_id, LEVEL
FROM employees
START WITH emp_id = :emp_id
CONNECT BY emp_id = PRIOR manager_id;

3. Путь «корень → узел» с SYS_CONNECT_BY_PATH

SELECT emp_id, SYS_CONNECT_BY_PATH(emp_name, ' / ') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

4. Значение корня для каждого узла

SELECT emp_id, CONNECT_BY_ROOT emp_name AS root_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

5. Определить листья в оргструктуре

SELECT emp_id, emp_name, CONNECT_BY_ISLEAF AS is_leaf
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

6. Ограничение глубины до трёх уровней

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL <= 3
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

7. Отсечь ветви условием статуса

SELECT emp_id, emp_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND status = 'ACTIVE';

8. Защита от циклов и их признак

SELECT emp_id, manager_id, CONNECT_BY_ISCYCLE AS is_cycle
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR emp_id = manager_id;

9. Категории с «хлебными крошками»

SELECT cat_id, SYS_CONNECT_BY_PATH(cat_name, ' > ') AS breadcrumb
FROM categories
START WITH parent_id IS NULL
CONNECT BY PRIOR cat_id = parent_id;

10. Только листья каталога

SELECT cat_id, cat_name
FROM categories
WHERE CONNECT_BY_ISLEAF = 1
START WITH parent_id IS NULL
CONNECT BY PRIOR cat_id = parent_id;

11. Только узлы второго уровня

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL = 2
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

12. Потоки комментариев с сортировкой соседей по времени

SELECT
  LPAD(' ', (LEVEL-1)*2) || author || ': ' || text AS thread_line,
  created_at
FROM comments
START WITH parent_id IS NULL
CONNECT BY PRIOR comment_id = parent_id
ORDER SIBLINGS BY created_at;

13. Сумма зарплат в поддеревьях

SELECT CONNECT_BY_ROOT emp_id AS root_id, SUM(salary) AS subtotal
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
GROUP BY CONNECT_BY_ROOT emp_id;

14. Подсчитать количество потомков у узла

SELECT e.emp_id,
       (SELECT COUNT(*)
          FROM employees
          START WITH emp_id = e.emp_id
          CONNECT BY PRIOR emp_id = manager_id) AS descendants
FROM employees e;

15. BOM: изделие → компоненты

SELECT parent_part, child_part, qty, LEVEL
FROM bom
START WITH parent_part = :product
CONNECT BY PRIOR child_part = parent_part;

16. Найти всех родителей компонента

SELECT parent_part, child_part, LEVEL
FROM bom
START WITH child_part = :component
CONNECT BY parent_part = PRIOR child_part;

17. Путь идентификаторов узлов

SELECT comment_id, SYS_CONNECT_BY_PATH(comment_id, '/') AS id_path
FROM comments
START WITH parent_id IS NULL
CONNECT BY PRIOR comment_id = parent_id;

18. Поддерево от выбранного начальника

SELECT emp_id, emp_name, LEVEL
FROM employees
START WITH emp_id = :boss
CONNECT BY PRIOR emp_id = manager_id;

19. Список корней (те, у кого нет родителя)

SELECT emp_id, emp_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

20. С ограничением глубины и сортировкой по имени

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL <= 4
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY emp_name;

Еще 20 примеров.

21. Исключить уволенных из дерева

SELECT emp_id, emp_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND status <> 'FIRED';

22. Только листовые файлы с путями

SELECT path
FROM (
  SELECT SYS_CONNECT_BY_PATH(file_name, '/') AS path, CONNECT_BY_ISLEAF AS leaf
  FROM files
  START WITH parent_id IS NULL
  CONNECT BY PRIOR file_id = parent_id
)
WHERE leaf = 1;

23. Корневой идентификатор для каждого узла

SELECT emp_id, CONNECT_BY_ROOT emp_id AS root_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

24. Глубина узла (псевдостолбец LEVEL)

SELECT emp_id, emp_name, LEVEL AS depth
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

25. Нумерация узлов в пределах корня

SELECT
  CONNECT_BY_ROOT emp_id AS root_id,
  ROW_NUMBER() OVER (PARTITION BY CONNECT_BY_ROOT emp_id ORDER BY LEVEL) AS rn,
  emp_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

26. Ключ маршрута «корень:узел:глубина»

SELECT CONNECT_BY_ROOT emp_id || ':' || emp_id || ':' || LEVEL AS key
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

27. Иерархия подразделений по региону

SELECT dept_id, dept_name, LEVEL
FROM departments
START WITH parent_id IS NULL
CONNECT BY PRIOR dept_id = parent_id
  AND region = :region;

28. Узлы на глубине 3 и глубже

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL >= 3
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

29. Счёт узлов на каждом уровне

SELECT LEVEL, COUNT(*) AS cnt
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
GROUP BY LEVEL
ORDER BY LEVEL;

30. Несколько корней (несколько департаментов)

SELECT emp_id, emp_name
FROM employees
START WITH dept_id IN (10, 20) AND manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

31. Только подчинённые (исключая корни)

SELECT emp_id, emp_name
FROM employees
START WITH manager_id IS NOT NULL
CONNECT BY PRIOR emp_id = manager_id;

32. Пути до целевого узла (без него)

SELECT SYS_CONNECT_BY_PATH(emp_name, ' > ') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND emp_id <> :target;

33. Подсчёт потомков с условием LEVEL > 1

SELECT e.emp_id,
       (SELECT COUNT(*) FROM employees
         START WITH emp_id = e.emp_id
         CONNECT BY PRIOR emp_id = manager_id AND LEVEL > 1) AS child_cnt
FROM employees e;

34. Обрезка ветвей по максимальной глубине

SELECT emp_id, emp_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND LEVEL <= :max_depth;

35. Путь предков для заданного листа

SELECT SYS_CONNECT_BY_PATH(emp_name, ' / ') AS ancestor_path
FROM employees
START WITH emp_id = :leaf
CONNECT BY emp_id = PRIOR manager_id;

36. Корень и количество листьев в поддереве

SELECT CONNECT_BY_ROOT emp_id AS root_id,
       SUM(CASE WHEN CONNECT_BY_ISLEAF = 1 THEN 1 ELSE 0 END) AS leaf_cnt
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
GROUP BY CONNECT_BY_ROOT emp_id;

37. Сортировка ветвей, затем глобальная сортировка

SELECT*
FROM (
  SELECT emp_id, emp_name, LEVEL
  FROM employees
  START WITH manager_id IS NULL
  CONNECT BY PRIOR emp_id = manager_id
  ORDER SIBLINGS BY emp_name
)
ORDER BY LEVEL, emp_name;

38. Пути категорий и фильтр по части пути

SELECT *
FROM (
  SELECT cat_id, SYS_CONNECT_BY_PATH(cat_name, '/') AS p
  FROM categories
  START WITH parent_id IS NULL
  CONNECT BY PRIOR cat_id = parent_id
)
WHERE p LIKE '%/Hardware/%';

39. Вывести только определённые уровни (1 и 3)

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL IN (1,3)
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

40. Количество корней и листьев на уровне

SELECT LEVEL,
       SUM(CASE WHEN LEVEL = 1 THEN 1 ELSE 0 END) AS roots,
       SUM(CASE WHEN CONNECT_BY_ISLEAF = 1 THEN 1 ELSE 0 END) AS leaves
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
GROUP BY LEVEL;

Еще 20 примеров.

41. Агрегация по корневому идентификатору

SELECT CONNECT_BY_ROOT dept_id AS root_dept, COUNT(*) AS cnt
FROM departments
START WITH parent_id IS NULL
CONNECT BY PRIOR dept_id = parent_id
GROUP BY CONNECT_BY_ROOT dept_id;

42. Сбор всех путей для аудита

SELECT SYS_CONNECT_BY_PATH(emp_id, '->') AS id_path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

43. Маркировка цикла при NOCYCLE

SELECT emp_id, manager_id, CONNECT_BY_ISCYCLE AS cyc
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR emp_id = manager_id;

44. Фильтр ветвей по условию на родителя

SELECT emp_id, emp_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND PRIOR status = 'ACTIVE';

45. Вывести только узлы без потомков (листья)

SELECT emp_id, emp_name
FROM employees
WHERE CONNECT_BY_ISLEAF = 1
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

46. Дерево сотрудников с устойчивой сортировкой соседей

SELECT
  LPAD(' ', (LEVEL-1)*2) || emp_name AS emp_name,
  emp_id, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY emp_name;

47. Предки конкретного сотрудника (обратный подъём)

SELECT emp_id, manager_id, LEVEL
FROM employees
START WITH emp_id = :emp_id
CONNECT BY emp_id = PRIOR manager_id;

48. Путь «корень → узел» с SYS_CONNECT_BY_PATH

SELECT emp_id, SYS_CONNECT_BY_PATH(emp_name, ' / ') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

49. Значение корня для каждого узла

SELECT emp_id, CONNECT_BY_ROOT emp_name AS root_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

50. Определить листья в оргструктуре

SELECT emp_id, emp_name, CONNECT_BY_ISLEAF AS is_leaf
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

51. Ограничение глубины до трёх уровней

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL <= 3
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

52. Отсечь ветви условием статуса

SELECT emp_id, emp_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND status = 'ACTIVE';

53. Защита от циклов и их признак

SELECT emp_id, manager_id, CONNECT_BY_ISCYCLE AS is_cycle
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR emp_id = manager_id;

54. Категории с «хлебными крошками»

SELECT cat_id, SYS_CONNECT_BY_PATH(cat_name, ' > ') AS breadcrumb
FROM categories
START WITH parent_id IS NULL
CONNECT BY PRIOR cat_id = parent_id;

55. Только листья каталога

SELECT cat_id, cat_name
FROM categories
WHERE CONNECT_BY_ISLEAF = 1
START WITH parent_id IS NULL
CONNECT BY PRIOR cat_id = parent_id;

56. Только узлы второго уровня

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL = 2
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

57. Потоки комментариев с сортировкой соседей по времени

SELECT
  LPAD(' ', (LEVEL-1)*2) || author || ': ' || text AS thread_line,
  created_at
FROM comments
START WITH parent_id IS NULL
CONNECT BY PRIOR comment_id = parent_id
ORDER SIBLINGS BY created_at;

58. Сумма зарплат в поддеревьях

SELECT CONNECT_BY_ROOT emp_id AS root_id, SUM(salary) AS subtotal
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
GROUP BY CONNECT_BY_ROOT emp_id;

59. Подсчитать количество потомков у узла

SELECT e.emp_id,
       (SELECT COUNT(*)
          FROM employees
          START WITH emp_id = e.emp_id
          CONNECT BY PRIOR emp_id = manager_id) AS descendants
FROM employees e;

60. BOM: изделие → компоненты

SELECT parent_part, child_part, qty, LEVEL
FROM bom
START WITH parent_part = :product
CONNECT BY PRIOR child_part = parent_part;

Еще 20 примеров.

61. Найти всех родителей компонента

SELECT parent_part, child_part, LEVEL
FROM bom
START WITH child_part = :component
CONNECT BY parent_part = PRIOR child_part;

62. Путь идентификаторов узлов

SELECT comment_id, SYS_CONNECT_BY_PATH(comment_id, '/') AS id_path
FROM comments
START WITH parent_id IS NULL
CONNECT BY PRIOR comment_id = parent_id;

63. Поддерево от выбранного начальника

SELECT emp_id, emp_name, LEVEL
FROM employees
START WITH emp_id = :boss
CONNECT BY PRIOR emp_id = manager_id;

64. Список корней (те, у кого нет родителя)

SELECT emp_id, emp_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

65. С ограничением глубины и сортировкой по имени

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL <= 4
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY emp_name;

66. Исключить уволенных из дерева

SELECT emp_id, emp_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND status <> 'FIRED';

67. Только листовые файлы с путями

SELECT path
FROM (
  SELECT SYS_CONNECT_BY_PATH(file_name, '/') AS path, CONNECT_BY_ISLEAF AS leaf
  FROM files
  START WITH parent_id IS NULL
  CONNECT BY PRIOR file_id = parent_id
)
WHERE leaf = 1;

68. Корневой идентификатор для каждого узла

SELECT emp_id, CONNECT_BY_ROOT emp_id AS root_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

69. Глубина узла (псевдостолбец LEVEL)

SELECT emp_id, emp_name, LEVEL AS depth
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

70. Нумерация узлов в пределах корня

SELECT
  CONNECT_BY_ROOT emp_id AS root_id,
  ROW_NUMBER() OVER (PARTITION BY CONNECT_BY_ROOT emp_id ORDER BY LEVEL) AS rn,
  emp_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

71. Ключ маршрута «корень:узел:глубина»

SELECT CONNECT_BY_ROOT emp_id || ':' || emp_id || ':' || LEVEL AS key
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

72. Иерархия подразделений по региону

SELECT dept_id, dept_name, LEVEL
FROM departments
START WITH parent_id IS NULL
CONNECT BY PRIOR dept_id = parent_id
  AND region = :region;

73. Узлы на глубине 3 и глубже

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL >= 3
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

74. Счёт узлов на каждом уровне

SELECT LEVEL, COUNT(*) AS cnt
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
GROUP BY LEVEL
ORDER BY LEVEL;

75. Несколько корней (несколько департаментов)

SELECT emp_id, emp_name
FROM employees
START WITH dept_id IN (10, 20) AND manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

76. Только подчинённые (исключая корни)

SELECT emp_id, emp_name
FROM employees
START WITH manager_id IS NOT NULL
CONNECT BY PRIOR emp_id = manager_id;

77. Пути до целевого узла (без него)

SELECT SYS_CONNECT_BY_PATH(emp_name, ' > ') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND emp_id <> :target;

78. Подсчёт потомков с условием LEVEL > 1

SELECT e.emp_id,
       (SELECT COUNT(*) FROM employees
         START WITH emp_id = e.emp_id
         CONNECT BY PRIOR emp_id = manager_id AND LEVEL > 1) AS child_cnt
FROM employees e;

79. Обрезка ветвей по максимальной глубине

SELECT emp_id, emp_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND LEVEL <= :max_depth;

80. Путь предков для заданного листа

SELECT SYS_CONNECT_BY_PATH(emp_name, ' / ') AS ancestor_path
FROM employees
START WITH emp_id = :leaf
CONNECT BY emp_id = PRIOR manager_id;

Еще 20 примеров.

81. Корень и количество листьев в поддереве

SELECT CONNECT_BY_ROOT emp_id AS root_id,
       SUM(CASE WHEN CONNECT_BY_ISLEAF = 1 THEN 1 ELSE 0 END) AS leaf_cnt
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
GROUP BY CONNECT_BY_ROOT emp_id;

82. Сортировка ветвей, затем глобальная сортировка

SELECT*
FROM (
  SELECT emp_id, emp_name, LEVEL
  FROM employees
  START WITH manager_id IS NULL
  CONNECT BY PRIOR emp_id = manager_id
  ORDER SIBLINGS BY emp_name
)
ORDER BY LEVEL, emp_name;

83. Пути категорий и фильтр по части пути

SELECT *
FROM (
  SELECT cat_id, SYS_CONNECT_BY_PATH(cat_name, '/') AS p
  FROM categories
  START WITH parent_id IS NULL
  CONNECT BY PRIOR cat_id = parent_id
)
WHERE p LIKE '%/Hardware/%';

84. Вывести только определённые уровни (1 и 3)

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL IN (1,3)
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

85. Количество корней и листьев на уровне

SELECT LEVEL,
       SUM(CASE WHEN LEVEL = 1 THEN 1 ELSE 0 END) AS roots,
       SUM(CASE WHEN CONNECT_BY_ISLEAF = 1 THEN 1 ELSE 0 END) AS leaves
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
GROUP BY LEVEL;

86. Агрегация по корневому идентификатору

SELECT CONNECT_BY_ROOT dept_id AS root_dept, COUNT(*) AS cnt
FROM departments
START WITH parent_id IS NULL
CONNECT BY PRIOR dept_id = parent_id
GROUP BY CONNECT_BY_ROOT dept_id;

87. Сбор всех путей для аудита

SELECT SYS_CONNECT_BY_PATH(emp_id, '->') AS id_path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

88. Маркировка цикла при NOCYCLE

SELECT emp_id, manager_id, CONNECT_BY_ISCYCLE AS cyc
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR emp_id = manager_id;

89. Фильтр ветвей по условию на родителя

SELECT emp_id, emp_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND PRIOR status = 'ACTIVE';

90. Вывести только узлы без потомков (листья)

SELECT emp_id, emp_name
FROM employees
WHERE CONNECT_BY_ISLEAF = 1
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

91. Дерево сотрудников с устойчивой сортировкой соседей

SELECT
  LPAD(' ', (LEVEL-1)*2) || emp_name AS emp_name,
  emp_id, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY emp_name;

92. Предки конкретного сотрудника (обратный подъём)

SELECT emp_id, manager_id, LEVEL
FROM employees
START WITH emp_id = :emp_id
CONNECT BY emp_id = PRIOR manager_id;

93. Путь «корень → узел» с SYS_CONNECT_BY_PATH

SELECT emp_id, SYS_CONNECT_BY_PATH(emp_name, ' / ') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

94. Значение корня для каждого узла

SELECT emp_id, CONNECT_BY_ROOT emp_name AS root_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

95. Определить листья в оргструктуре

SELECT emp_id, emp_name, CONNECT_BY_ISLEAF AS is_leaf
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

96. Ограничение глубины до трёх уровней

SELECT emp_id, emp_name, LEVEL
FROM employees
WHERE LEVEL <= 3
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

97. Отсечь ветви условием статуса

SELECT emp_id, emp_name, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
  AND status = 'ACTIVE';

98. Защита от циклов и их признак

SELECT emp_id, manager_id, CONNECT_BY_ISCYCLE AS is_cycle
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR emp_id = manager_id;

99. Категории с «хлебными крошками»

SELECT cat_id, SYS_CONNECT_BY_PATH(cat_name, ' > ') AS breadcrumb
FROM categories
START WITH parent_id IS NULL
CONNECT BY PRIOR cat_id = parent_id;

100. Только листья каталога

SELECT cat_id, cat_name
FROM categories
WHERE CONNECT_BY_ISLEAF = 1
START WITH parent_id IS NULL
CONNECT BY PRIOR cat_id = parent_id;

Частые ошибки и подводные камни

  1. Направление связи. Перепутали стороны — дерево инвертируется. Проверяйте на малых примерах.
  2. Фильтры после построения. Условия в WHERE применяются уже к результату — ими легко «обрубить» ветви. Для отсечения используйте условия прямо в CONNECT BY.
  3. Сортировка «соседей». Используйте ORDER SIBLINGS BY вместо обычного ORDER BY для стабильного порядка внутри ветвей.
  4. Циклы. Добавляйте NOCYCLE и выводите CONNECT_BY_ISCYCLE для диагностики.
  5. Производительность. Глубокие/широкие деревья растут экспоненциально — ограничивайте LEVEL, добавляйте селективные предикаты.

Альтернативы

  • Рекурсивный WITH на базе UNION ALL — гибче при сложных правилах, позволяет имитировать обход в ширину/глубину.
  • Материализованные пути (хранить путь/список предков) — быстро для чтения, дороже на запись и поддержку.

Заключение

CONNECT BY остаётся мощным инструментом для иерархий прямо в SQL.
Комбинируйте его с LEVEL, псевдостолбцами для путей и аккуратной сортировкой «соседей», а при сложной логике оценивайте рекурсивный WITH.

Документация

Oracle SQL Language Reference — Hierarchical Queries

LEVEL, CONNECT_BY_ROOT, CONNECT_BY_ISLEAF, SYS_CONNECT_BY_PATH

🔜 Следующая статья:

MINUS в Oracle SQL: как получить разницу между результатами двух запросов

0
Понравилась статья? Поделиться с друзьями:
Вам также может быть интересно

SELECT в Oracle SQL — 100 простых примеров для старта

SELECT в Oracle SQL. Введение SELECT — основной оператор выборки в Oracle SQL. Он строит результирующие наборы из таблиц и представлений, поддерживает фильтрацию, сортировку, группировку, подзапросы, соединения, аналитические…