CTE (Common Table Expression), определяемое с помощью WITH, — это именованный временный результирующий набор, на который можно ссылаться в запросе. CTE делают сложные запросы более читаемыми, разбивая их на именованные логические шаги — и они позволяют использовать рекурсивные запросы (для иерархий).
active_users (
id, name users active
)
active_users
name ;
WITH ... AS (...) определяет CTE — именованный запрос, на который вы ссылаетесь позже. Это похоже на подзапрос, но именованный и читаемый, особенно для сложных запросов.
WITH
monthly_sales AS (
SELECT month, SUM(amount) AS total FROM orders GROUP BY month
),
avg_sales AS (
SELECT AVG(total) AS avg_total FROM monthly_sales -- a CTE can reference another
)
SELECT m.month, m.total
FROM monthly_sales m, avg_sales a
WHERE m.total > a.avg_total; -- months above the average
Несколько CTE разбивают сложный запрос на читаемые последовательные шаги — каждый построен на основе предыдущего. Это намного понятнее, чем глубоко вложенные подзапросы.
Nested subqueries → hard to read when deeply nested
CTEs → name each step, read top-to-bottom like a sequence → much clearer
→ CTEs are often preferred for COMPLEX queries purely for readability/maintainability.
-- traverse a hierarchy (e.g. all descendants of a category, an org chart)
WITH RECURSIVE subordinates AS (
SELECT id, name, manager_id FROM employees WHERE id = 1 -- base case (the top)
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN subordinates s ON e.manager_id = s.id -- recursive step
)
SELECT * FROM subordinates; -- the whole tree under employee 1
Рекурсивные CTE (WITH RECURSIVE) обходят иерархические/древовидные данные (организационные схемы, деревья категорий, спецификации изделий, пути в графах) — что иначе было бы очень сложно реализовать в SQL.
CTE — это важный признак SQL для читаемости и рекурсии, и их понимание ценно для написания поддерживаемых сложных запросов и для работы с иерархическими данными.
Основная повседневная выгода — читаемость: сложные запросы с несколькими уровнями вложенных подзапросов становятся трудными для чтения и поддержки, в то время как CTE позволяют разбить запрос на именованные последовательные логические шаги (читаемые сверху вниз как последовательность операций), значительно улучшая ясность — по этой причине CTE часто предпочитаются глубоко вложенным подзапросам для сложных запросов, что делает их важным инструментом для написания поддерживаемого SQL.
Не менее важна возможность рекурсивного CTE (WITH RECURSIVE), которая позволяет запрашивать иерархические и древовидные данные (организационные схемы, деревья категорий, спецификации изделий, обход графов) — класс запросов, которые иначе очень сложны или невозможны в стандартном SQL, что делает рекурсивные CTE стандартным решением для обхода иерархических данных.
Понимание обоих применений — CTE для разложения сложных запросов на читаемые шаги и рекурсивные CTE для иерархического обхода — охватывает практическую ценность.
Поскольку сложные запросы распространены в реальных приложениях (и читаемость/поддерживаемость имеют значение), а иерархические данные (которые рекурсивные CTE хорошо обрабатывают) появляются часто (структуры организаций, вложенные категории, структурированные данные), понимание CTE — это ценные знания старшего уровня, которые улучшают поддерживаемость запросов и открывают возможность иерархического запроса — часто релевантный инструмент для написания чистого, сложного SQL и частая тема, которая демонстрирует продвинутые возможности SQL, в частности рекурсивную форму для древовидных данных.