Что такое общие табличные выражения (CTE) в базах данных?

Общие табличные выражения (CTE) — это временный именованный результирующий набор, который определяется в рамках выполнения одной SQL-инструкции (например, SELECT или UPDATE) с помощью предложения WITH. CTE помогает организовать сложные запросы, делая их более модульными и читаемыми.

Основные сценарии использования:

1. Упрощение сложных запросов: Замена многоуровневых вложенных подзапросов на цепочку логически названных CTE.
2. Рекурсивные запросы: Обработка иерархических или древовидных данных (оргструктура, категории товаров, цепочки комментариев).

Развёрнутый пример рекурсивного CTE: Задача: получить полную иерархию подчинённых для заданного менеджера, включая уровни вложенности.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Якорная часть (Anchor Member): начальная точка рекурсии
    SELECT 
        EmployeeID,
        Name,
        ManagerID,
        1 AS Level,
        CAST(Name AS VARCHAR(1000)) AS Path
    FROM Employees
    WHERE ManagerID IS NULL -- Начинаем с верхнего руководителя

    UNION ALL

    -- Рекурсивная часть (Recursive Member): присоединение следующих уровней
    SELECT 
        e.EmployeeID,
        e.Name,
        e.ManagerID,
        eh.Level + 1,
        CAST(eh.Path || ' -> ' || e.Name AS VARCHAR(1000))
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
    WHERE eh.Level < 10 -- Защита от потенциальной бесконечной рекурсии (циклов)
)
-- Финальный выборка из рекурсивного CTE
SELECT 
    EmployeeID,
    Name,
    ManagerID,
    Level,
    Path
FROM EmployeeHierarchy
ORDER BY Level, Name;

Важные технические детали:

• Рекурсивный CTE обязательно состоит из двух частей, объединённых UNION ALL: якорной и рекурсивной.
• Необходимо следить за возможными циклами в данных, которые могут привести к бесконечной рекурсии. В разных СУБД есть механизмы защиты (например, MAXRECURSION в SQL Server).
• CTE материализуется (вычисляется) только при первом обращении к ней в основном запросе, что может влиять на производительность в сравнении с временными таблицами.