WITH дає імʼя проміжному результату в межах одного стейтменту. Виграш переважно в читабельності: замість пʼяти вкладених підзапитів виходить послідовність кроків, які читаються згори вниз, і на кожен крок можна послатись більше ніж один раз, не копіюючи текст. Живе такий набір рядків рівно до кінця запиту, на відміну від тимчасової таблиці, і жодних прав чи DDL для нього не потрібно.
Рекурсивний варіант влаштований інакше. WITH RECURSIVE складається з якірної частини, UNION ALL і рекурсивної частини, яка посилається на сам CTE. Справжньої рекурсії там немає: база кладе результат якоря в робочу таблицю, проганяє рекурсивну частину по її вмісту, отриманим рядкам робить те саме, і так доки чергова ітерація не поверне порожній результат. У плані це видно як Recursive Union з WorkTable Scan усередині. Звідси й обмеження: у рекурсивній частині заборонені ORDER BY, LIMIT і агрегати, а посилання на CTE може бути лише одне і не всередині підзапиту.
Поводження нерекурсивних CTE змінилось у PostgreSQL 12. До 11 версії включно вони були оптимізаційним барʼєром: рахувались повністю й окремо, предикат із зовнішнього запиту всередину не протягувався, і половина порад в інтернеті будувалась саме на цьому. З 12 версії CTE без побічних ефектів, на який є рівно одне посилання, вбудовується в основний запит, наче звичайний підзапит, а керується це словами MATERIALIZED і NOT MATERIALIZED. CTE, що модифікують дані, вбудовуються ніколи. MySQL отримав CTE лише у 8.0 і теж уміє або матеріалізувати derived table, або злити його з зовнішнім запитом.
За захист від нескінченної рекурсії відповідає розробник. MySQL обриває запит після cte_max_recursion_depth ітерацій (за умовчанням 1000) з помилкою 3636, а PostgreSQL не має ліміту взагалі й крутитиме цикл, доки не зʼїсть памʼять або тимчасове місце на диску. Як мінімум потрібен лічильник глибини в рекурсивній частині; для даних, де parent_id правлять руками, ще й перевірка вже пройденого шляху або CYCLE із PostgreSQL 14.
Підзапит кращий там, де логіка вміщається в один зрозумілий рядок, і CTE в такому місці додає лише розмір. Рекурсивний CTE потрібен тоді, коли глибина ієрархії наперед невідома, але на гарячих сторінках із деревом на тисячі вузлів дешевше зберігати структуру явно: closure table, materialized path або ltree. Рекурсивний обхід по parent_id без індексу на цій колонці перетворює кожну ітерацію на seq scan, і EXPLAIN (ANALYZE) покаже це першим же рядком.
-- Дерево категорій: categories(id, parent_id, title); індекс на parent_id обовʼязковий
WITH RECURSIVE subtree AS (
-- якірна частина: рядок, з якого починається обхід
SELECT c.id, c.parent_id, c.title, 1 AS depth, ARRAY[c.id] AS path
FROM categories c
WHERE c.id = 42
UNION ALL
-- рекурсивна частина: діти рядків, знайдених попередньою ітерацією
SELECT c.id, c.parent_id, c.title, s.depth + 1, s.path || c.id
FROM categories c
JOIN subtree s ON c.parent_id = s.id
WHERE s.depth < 10 -- запобіжник: PostgreSQL сам рекурсію не обмежує
AND c.id <> ALL (s.path) -- захист від циклу в даних
)
SELECT id, title, depth FROM subtree ORDER BY path;
-- PostgreSQL 14+: цикли ловить сама база, масив вести руками не треба
WITH RECURSIVE subtree AS (
SELECT id, parent_id, title FROM categories WHERE id = 42
UNION ALL
SELECT c.id, c.parent_id, c.title
FROM categories c JOIN subtree s ON c.parent_id = s.id
) CYCLE id SET is_cycle USING path
SELECT id, title FROM subtree WHERE NOT is_cycle;
-- PostgreSQL 12+: CTE з одним посиланням вбудовується в основний запит.
-- MATERIALIZED змушує порахувати агрегат один раз і перевикористати результат.
WITH post_counts AS MATERIALIZED (
SELECT category_id, count(*) AS posts
FROM posts
GROUP BY category_id
)
SELECT c.title, coalesce(p.posts, 0)
FROM categories c
LEFT JOIN post_counts p ON p.category_id = c.id;Назвіть версію вголос: до PostgreSQL 11 CTE був барʼєром оптимізації, з 12 - ні, і саме тому зʼявилось слово MATERIALIZED. Це відрізняє людину, яка читала release notes, від людини, яка переказує старий блог.