CONNECT_BY_ISLEAF в Oracle SQL — как определить конечные узлы в иерархических запросах

ORACLE

🟢CONNECT_BY_ISLEAF в Oracle SQL. Введение

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

🔤 Написание

sql
SELECT ..., CONNECT_BY_ISLEAF
FROM ...
CONNECT BY PRIOR id = parent_id;

🔹 Значения:

  • 1 — если узел не имеет дочерних элементов

  • 0 — если у узла есть потомки

🔄 Где часто используется

  • В построении дерева сотрудников, категорий, задач

  • Для визуализации структуры с отметками «лист»

  • В построении JSON/XML иерархий

  • Для расчётов только по конечным узлам

  • В SELECT-запросах с CONNECT BY

🧪 10 Примеров использования CONNECT_BY_ISLEAF

1️⃣ Простой иерархический запрос с отметкой «лист»

sql
SELECT employee_id, manager_id, LEVEL, CONNECT_BY_ISLEAF AS is_leaf
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Каждый сотрудник получает отметку: лист или нет.

2️⃣ Фильтрация только конечных узлов

sql
SELECT * FROM employees
WHERE CONNECT_BY_ISLEAF = 1
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Выводим только тех, у кого нет подчинённых.

3️⃣ Подсчёт количества листьев

sql
SELECT COUNT(*) AS leaf_count
FROM employees
WHERE CONNECT_BY_ISLEAF = 1
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Сколько узлов на «концах» дерева?

4️⃣ Отображение структуры с флагами

sql
SELECT LPAD(' ', LEVEL * 2) || name AS tree_node,
CONNECT_BY_ISLEAF
FROM categories
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

Визуализируем структуру + помечаем листья.

5️⃣ Выборка задач без подзадач

sql
SELECT task_id, task_name
FROM tasks
WHERE CONNECT_BY_ISLEAF = 1
START WITH parent_id IS NULL
CONNECT BY PRIOR task_id = parent_id;

Для задач, которые не делятся дальше.

6️⃣ Сортировка с приоритетом листов

sql
SELECT name, CONNECT_BY_ISLEAF
FROM tree_nodes
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id
ORDER BY CONNECT_BY_ISLEAF DESC;

Сначала конечные узлы, затем промежуточные.

7️⃣ Объединение CONNECT_BY_ISLEAF с LEVEL

sql
SELECT id, LEVEL, CONNECT_BY_ISLEAF
FROM nodes
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

Комбинация глубины и признака листа.

8️⃣ Использование в представлении

sql
CREATE VIEW leaf_employees AS
SELECT * FROM employees
WHERE CONNECT_BY_ISLEAF = 1
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Фиксируем представление только для конечных сотрудников.

9️⃣ Построение отчёта по конечным элементам

sql
SELECT department_id, COUNT(*) AS leaf_count
FROM employees
WHERE CONNECT_BY_ISLEAF = 1
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
GROUP BY department_id;

Аналитика: сколько «листьев» в каждом департаменте.

🔟 Формирование древовидного вывода с пометкой

sql
SELECT LPAD(' ', LEVEL * 3) || name AS node,
CASE WHEN CONNECT_BY_ISLEAF = 1 THEN '[LEAF]' ELSE '[BRANCH]' END AS node_type
FROM tree_structure
START WITH parent_id IS NULL
CONNECT BY PRIOR id = parent_id;

Прямое текстовое указание типа узла.

🧩 Заключение

CONNECT_BY_ISLEAF — это простой, но очень мощный псевдостолбец, который делает работу с иерархиями в Oracle SQL более информативной и наглядной. Особенно полезен, если вы строите отчёты, визуализации или анализируете структуру данных.

💡 Запомни:

  • Работает только с CONNECT BY

  • Возвращает 1 для узлов без потомков, 0 — для остальных

  • Удобен для фильтрации, сортировки и маркировки узлов

  • Поддерживается в SELECT, VIEW, ORDER BY

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

OPTION в Oracle SQL — как управлять обновляемыми представлениями

 

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

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

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

0 0 голоса
Рейтинг статьи
Подписаться
Уведомить о
guest
0 комментариев
Старые
Новые Популярные
Межтекстовые Отзывы
Посмотреть все комментарии