Что такое иерархические запросы и зачем они нужны
Иерархические запросы — это специальный тип SQL-запросов, предназначенный для работы с данными, которые имеют древовидную структуру "родитель-потомок". Представьте себе организационную структуру компании, где у каждого сотрудника есть начальник, или файловую систему, где папки содержат подпапки.
Основная проблема, которую решают иерархические запросы
В обычных реляционных таблицах данные хранятся "плоско" — каждая строка содержит ссылку на своего "родителя". Например:
emp_id | name | manager_id
-------|----------|----------
1 | Иванов | NULL (топ-менеджер)
2 | Петров | 1 (подчиненный Иванова)
3 | Сидоров | 1 (подчиненный Иванова)
4 | Козлов | 2 (подчиненный Петрова)
Чтобы получить полную иерархию (кто кому подчиняется на всех уровнях), обычными JOIN'ами это сделать крайне сложно, особенно если уровней вложенности неизвестно заранее.
Основные сферы применения
1. Организационные структуры
- Иерархия сотрудников в компании
- Управленческие цепочки
- Департаменты и подразделения
2. Категории и классификации
- Каталоги товаров (категория → подкатегория → товар)
- Системы тегов и рубрик
- Географические структуры (страна → регион → город)
3. Технические структуры
- Файловые системы (папки и подпапки)
- Меню сайтов и приложений
- Комментарии с ответами и вложениями
4. Финансовые и учетные системы
- Планы счетов в бухгалтерии
- Бюджетные структуры
- Иерархии центров затрат
5. Производственные процессы
- Состав изделий (деталь → сборочная единица → изделие)
- Технологические маршруты
- Зависимости между задачами проекта
Почему это важно
Без иерархических запросов для получения "всех подчиненных Иванова на всех уровнях" пришлось бы:
1. Писать множественные JOIN'ы (но сколько уровней?)
2. Использовать рекурсивные запросы или процедуры
3. Обрабатывать данные в коде приложения
Пример: Каталог товаров
Создадим таблицу для каталога товаров с иерархической структурой:
-- Создание таблицы категорий товаров
CREATE TABLE product_categories (
category_id INT PRIMARY KEY,
category_name VARCHAR(100),
parent_category_id INT,
category_level VARCHAR(20), -- для наглядности
FOREIGN KEY (parent_category_id) REFERENCES product_categories(category_id)
);
Объяснение FOREIGN KEY (parent_category_id) REFERENCES product_categories(category_id)
Эта строка создает внешний ключ (foreign key) - связь, которая обеспечивает целостность данных в иерархической структуре.
Что это означает:
FOREIGN KEY (parent_category_id) - объявляет, что столбец parent_category_id является внешним ключом
REFERENCES product_categories(category_id) - указывает, что этот внешний ключ ссылается на столбец category_id в той же таблице product_categories
Как это работает:
- Самоссылка: Таблица ссылается сама на себя - это называется рекурсивная связь
- Родительско-дочерние отношения: Каждая категория может иметь родительскую категорию
- Корневые элементы: Записи верхнего уровня имеют
parent_category_id = NULL
Что гарантирует этот constraint:
-- ✅ Разрешено: parent_category_id ссылается на существующую запись
INSERT INTO product_categories VALUES (100, 'Ноутбуки', 10, 'Уровень 2');
-- где 10 - это существующий category_id ('Компьютеры')
-- ✅ Разрешено: parent_category_id = NULL (корневой элемент)
INSERT INTO product_categories VALUES (1, 'Электроника', NULL, 'Корень');
-- ❌ Запрещено: parent_category_id ссылается на несуществующую запись
INSERT INTO product_categories VALUES (200, 'Рубашки', 999, 'Уровень 2');
-- Ошибка: нет записи с category_id = 999
Преимущества использования:
- Целостность данных: Нельзя создать "сиротские" записи
- Каскадные операции: Можно настроить автоматическое удаление дочерних элементов
- Защита от ошибок: СУБД автоматически проверяет корректность связей
Пример нарушения без FOREIGN KEY:
Без этого constraint можно было бы вставить:
-- Без FK это прошло бы, но создало бы "битую" иерархию
INSERT INTO product_categories VALUES (300, 'Новая категория', 99999, 'Уровень 1');
Результат: Категория со ссылкой на несуществующего родителя - иерархия сломана!
SQL код для наполенения тестовой таблицы данными:
-- Заполнение данными
INSERT INTO product_categories VALUES (1, 'Электроника', NULL, 'Корень');
INSERT INTO product_categories VALUES (2, 'Одежда', NULL, 'Корень');
INSERT INTO product_categories VALUES (3, 'Дом и сад', NULL, 'Корень');
-- Подкатегории Электроники
INSERT INTO product_categories VALUES (10, 'Компьютеры', 1, 'Уровень 1');
INSERT INTO product_categories VALUES (11, 'Телефоны', 1, 'Уровень 1');
INSERT INTO product_categories VALUES (12, 'Бытовая техника', 1, 'Уровень 1');
-- Подкатегории Одежды
INSERT INTO product_categories VALUES (20, 'Мужская одежда', 2, 'Уровень 1');
INSERT INTO product_categories VALUES (21, 'Женская одежда', 2, 'Уровень 1');
INSERT INTO product_categories VALUES (22, 'Детская одежда', 2, 'Уровень 1');
-- Подкатегории Компьютеров
INSERT INTO product_categories VALUES (100, 'Ноутбуки', 10, 'Уровень 2');
INSERT INTO product_categories VALUES (101, 'Настольные ПК', 10, 'Уровень 2');
INSERT INTO product_categories VALUES (102, 'Планшеты', 10, 'Уровень 2');
-- Подкатегории Телефонов
INSERT INTO product_categories VALUES (110, 'Смартфоны', 11, 'Уровень 2');
INSERT INTO product_categories VALUES (111, 'Кнопочные телефоны', 11, 'Уровень 2');
-- Подкатегории Мужской одежды
INSERT INTO product_categories VALUES (200, 'Рубашки', 20, 'Уровень 2');
INSERT INTO product_categories VALUES (201, 'Брюки', 20, 'Уровень 2');
INSERT INTO product_categories VALUES (202, 'Костюмы', 20, 'Уровень 2');
-- Подкатегории Смартфонов
INSERT INTO product_categories VALUES (1100, 'iPhone', 110, 'Уровень 3');
INSERT INTO product_categories VALUES (1101, 'Samsung', 110, 'Уровень 3');
INSERT INTO product_categories VALUES (1102, 'Xiaomi', 110, 'Уровень 3');
-- Подкатегории рубашек
INSERT INTO product_categories VALUES (2000, 'Деловые рубашки', 200, 'Уровень 3');
INSERT INTO product_categories VALUES (2001, 'Повседневные рубашки', 200, 'Уровень 3');
Наглядная структура каталога:
├── Электроника (1)
│ ├── Компьютеры (10)
│ │ ├── Ноутбуки (100)
│ │ ├── Настольные ПК (101)
│ │ └── Планшеты (102)
│ ├── Телефоны (11)
│ │ ├── Смартфоны (110)
│ │ │ ├── iPhone (1100)
│ │ │ ├── Samsung (1101)
│ │ │ └── Xiaomi (1102)
│ │ └── Кнопочные телефоны (111)
│ └── Бытовая техника (12)
├── Одежда (2)
│ ├── Мужская одежда (20)
│ │ ├── Рубашки (200)
│ │ │ ├── Деловые рубашки (2000)
│ │ │ └── Повседневные рубашки (2001)
│ │ ├── Брюки (201)
│ │ └── Костюмы (202)
│ ├── Женская одежда (21)
│ └── Детская одежда (22)
└── Дом и сад (3)
Структуру можно получить стаким селектом:
SELECT
LEVEL,
LPAD(' ', (LEVEL-1)*4) || category_name AS tree_view,
category_id,
parent_category_id
FROM product_categories
START WITH parent_category_id IS NULL
CONNECT BY PRIOR category_id = parent_category_id
ORDER SIBLINGS BY category_name;
В моей базе Oracle результат выполнения запроса выглядит так:
