Що таке CTE і навіщо потрібен WITH RECURSIVE?

Question

Accepted Answer

CTE (Common Table Expression) - іменований підзапит у блоці WITH, до якого основний запит звертається як до таблиці. Головна користь - читабельність: складний запит розбивається на кроки з іменами.
WITH paid AS (
    SELECT user_id, SUM(total) AS spent
    FROM orders
    WHERE status = 'paid'
    GROUP BY user_id
)
SELECT u.name, p.spent
FROM users u
JOIN paid p ON p.user_id = u.id
WHERE p.spent > 10000;

WITH RECURSIVE дозволяє CTE посилатися на себе - так обходять ієрархії: дерево категорій, оргструктуру, ланцюжок коментарів.
WITH RECURSIVE tree AS (
    SELECT id, parent_id, name, 1 AS depth
    FROM categories WHERE id = 42          -- початок
    UNION ALL
    SELECT c.id, c.parent_id, c.name, t.depth + 1
    FROM categories c
    JOIN tree t ON c.parent_id = t.id      -- крок
)
SELECT * FROM tree;

Що треба знати:

У PostgreSQL до версії 12 CTE завжди матеріалізувався й був «бар'єром оптимізації». З 12-ї простий CTE вбудовується в запит, а поведінку можна задати явно: AS MATERIALIZED / AS NOT MATERIALIZED.
У рекурсії потрібна умова зупинки, інакше цикли в даних дадуть нескінченний запит. Захист - лічильник глибини чи масив пройдених вузлів.
MySQL підтримує CTE з версії 8.0.

Докладніше в документації: Запити WITH (CTE)

Що таке CTE і навіщо потрібен WITH RECURSIVE?

Перевір себе

Схожі питання