Что такое CTE (Common Table Expression) в SQL? Чем рекурсивный CTE отличается от обычного?

Короткий ответ

CTE (конструкция WITH name AS (...)) — это временный именованный результат подзапроса, который можно использовать в основном запросе как обычную таблицу, что улучшает читаемость сложных запросов; рекурсивный CTE (WITH RECURSIVE) ссылается сам на себя, что позволяет обходить иерархические структуры произвольной глубины.

Как это работает подробнее

Обычный CTE — способ разбить сложный запрос на именованные шаги (в PostgreSQL 12+ может инлайниться или материализоваться, в зависимости от оптимизатора). Рекурсивный CTE состоит из «базовой» части и «рекурсивной» части, присоединяемой к результату предыдущей итерации, пока не перестанут появляться новые строки.

Пример кода

example.sqlКопировать

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 AS depth
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
    WHERE ct.depth < 20  -- защита от бесконечной рекурсии на испорченных данных
)
SELECT * FROM category_tree ORDER BY depth, id;

Что использовать на практике

• Практика. Рекурсивные CTE используют для обхода деревьев категорий, графов зависимостей, иерархии сотрудников; важно предусмотреть условие остановки и ограничивать глубину явным счётчиком для защиты от циклов.

Подводные камни

• Ошибка. Забывают про возможность циклов в данных — без ограничения глубины рекурсивный CTE уйдёт в бесконечный цикл; используют обычный CTE там, где нужен проход по произвольной глубине иерархии, дублируя логику через код приложения.