Перейти на PRO

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

Тип вопроса: Базы данных Вероятность: 25% Категория: C# Разработчик

Ответ

Общие табличные выражения (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 материализуется (вычисляется) только при первом обращении к ней в основном запросе, что может влиять на производительность в сравнении с временными таблицами.

Ответ 18+ 🔞

Давай разжую тебе про эти ваши CTE, а то смотрю, глаза стекленеют уже. Вообще, конечно, придумали же люди удобную штуку, чтобы не писать эти многоэтажные подзапросы, от которых мозг вскипает, как каша в микроволновке без крышки.

Представь, что тебе надо не просто данные выбрать, а целую генеалогию какого-нибудь менеджера вытащить — кто ему подчиняется, кто подчиняется его подчинённым, и так до бесконечности, пока не упрёшься в стажёра, который только кофе разносит. Без CTE это пиздец, прости Господи, вложенных SELECT'ов на десять экранов. А с CTE — красота, всё как по нотам.

Вот смотри, как это выглядит в жизни, на примере этих самых менеджеров:

WITH RECURSIVE EmployeeHierarchy AS (
    -- Это начало, якорь, так сказать. Берём главного пахану.
    SELECT
        EmployeeID,
        Name,
        ManagerID,
        1 AS Level, -- Уровень первый, он и в Африке первый.
        CAST(Name AS VARCHAR(1000)) AS Path -- Путь начнём записывать
    FROM Employees
    WHERE ManagerID IS NULL -- Самый главный, у него босса нет

    UNION ALL -- Скрепляем это дело

    -- А вот тут начинается магия рекурсии. Цепляем подчинённых, как вагоны.
    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 -- Вот она, рекурсия! CTE сама на себя ссылается.
    WHERE eh.Level < 10 -- Страховочный трос от дурака. Чтобы если в данных петля, не ушёл в бесконечный цикл.
)
-- Ну и, собственно, выводим нашу выстроенную иерархию
SELECT
    EmployeeID,
    Name,
    ManagerID,
    Level,
    Path
FROM EmployeeHierarchy
ORDER BY Level, Name; -- Чтоб красиво было, по уровням и по алфавиту.

Суть в чём, а? Сначала мы взяли вершину айсберга — главного. Потом рекурсивная часть берёт этого главного, ищет всех, у кого он в поле ManagerID, и цепляет их к результату. Потом берёт этих подчинённых и ищет уже их подчинённых. И так по кругу, пока не кончатся люди или пока мы не упрёмся в лимит уровней. Получается такая аккуратная лестница вниз.

Но, блядь, есть важный момент, про который все забывают, а потом чешут репу. Если в твоих данных есть цикл — ну, типа Вася начальник Пети, Петя начальник Коли, а Коля, мудак, записан как начальник Васи — то запрос без защиты уйдёт в бесконечную рекурсию и накроется медным тазом. Поэтому всегда ставим ограничение, как в примере WHERE eh.Level < 10, или используем специфичные для базы штуки вроде MAXRECURSION.

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

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

© ХакСобесов, 2026
support@hacksobesov.com