Продвинутые возможности SQL: Погружение в конструкцию WITH

Многие специалисты по базам данных начинают свой путь с изучения основ SQL. Они быстро осваивают базовые запросы, такие как SELECT, INSERT, UPDATE и DELETE. Однако после освоения этих базовых навыков многие останавливаются, полагая, что это достаточно для большинства задач. На самом деле, SQL предлагает гораздо более мощные инструменты, которые могут существенно упростить и оптимизировать вашу работу с данными. Одним из таких инструментов является конструкция WITH, также известная как Common Table Expressions (CTE).

Что такое CTE (Common Table Expressions)?

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

Преимущества использования CTE:

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

WITH Hierarchy AS (
    -- Базовый случай: верхний уровень иерархии (например, компания)
    SELECT ID, Name, ParentID, 
           CAST(Name AS VARCHAR(255)) AS Path
    FROM org_structure
    WHERE ParentID IS NULL

    UNION ALL

    -- Рекурсивный случай: добавляем каждый следующий уровень иерархии
    SELECT o.ID, o.Name, o.ParentID,
           CAST(h.Path + ' -> ' + o.Name AS VARCHAR(255))
    FROM org_structure o
    JOIN Hierarchy h ON o.ParentID = h.ID
)

SELECT Path AS Hierarchy
FROM Hierarchy
WHERE Name = 'Сотрудник'
ORDER BY Path;

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