CTE (WITH) в SQL: что это, синтаксис и примеры запросов

CTE — это именованный подзапрос, вынесенный в начало запроса через WITH. Синтаксис один: WITH имя AS (SELECT ...), дальше основной запрос обращается к имя как к таблице. Результат от этого не меняется. Меняется читаемость: вложенность разворачивается в список шагов, который читают сверху вниз, а не изнутри наружу. Работает одинаково в PostgreSQL, MySQL 8.0, SQLite и MS SQL.

Весь разбор на двух таблицах.

users

orders

Сквозная задача через всю статью одна: оплаченный оборот по клиентам и кто из них выше среднего. Запросы и планы ниже сняты на PostgreSQL 16 после ANALYZE по обеим таблицам. Повторишь их на свежих таблицах без статистики: форма плана совпадёт, а числа cost, rows и width будут другими.

Синтаксис WITH: тот же подзапрос, только сверху

Первый шаг задачи. Клиенты с оплаченным оборотом больше 1000, через подзапрос в FROM:

SELECT name, total
FROM (
  SELECT u.name, sum(o.amount) AS total
  FROM users u
  JOIN orders o ON o.user_id = u.id
  WHERE o.status = 'paid'
  GROUP BY u.name
) t
WHERE total > 1000
ORDER BY total DESC;

То же самое через CTE:

WITH user_totals AS (
  SELECT u.name, sum(o.amount) AS total
  FROM users u
  JOIN orders o ON o.user_id = u.id
  WHERE o.status = 'paid'
  GROUP BY u.name
)
SELECT name, total
FROM user_totals
WHERE total > 1000
ORDER BY total DESC;

Оба варианта возвращают одно и то же:

Разница в порядке чтения. В первом варианте глаз натыкается на SELECT name, total FROM (, и что там внутри скобок, выясняется через шесть строк. Во втором сначала идёт определение user_totals, потом то, что с ним делают. На двух уровнях вложенности это дело вкуса. На четырёх подзапрос приходится читать снизу вверх, и чинить такой запрос в проде никто не хочет.

Данные сошлись: отменённый заказ Бориса на 1200 отсечён внутри CTE через WHERE o.status = 'paid', Глеб с оборотом 500 не прошёл внешний фильтр, Вера выпала на JOIN, потому что заказов у неё нет. Имя CTE ведёт себя как имя таблицы: к нему пишут JOIN, WHERE, GROUP BY. Остальные формы подзапросов разобраны в отдельной статье.

Несколько CTE в одном запросе

CTE перечисляются через запятую, слово WITH пишется один раз. Каждый следующий видит все предыдущие. Отсюда и главный сценарий: запрос собирается ступенями, и каждую ступень можно прочитать отдельно, не держа в голове остальные.

Усложняем задачу. Нужны клиенты с оборотом выше среднего по платящим, а это три шага: отобрать оплаченные заказы, посчитать оборот по клиентам, посчитать среднее по этим оборотам.

WITH paid AS (
  SELECT user_id, amount
  FROM orders
  WHERE status = 'paid'
),
user_totals AS (
  SELECT u.name, sum(p.amount) AS total
  FROM users u
  JOIN paid p ON p.user_id = u.id
  GROUP BY u.name
),
average AS (
  SELECT round(avg(total), 2) AS avg_total
  FROM user_totals
)
SELECT t.name, t.total, a.avg_total
FROM user_totals t
CROSS JOIN average a
WHERE t.total > a.avg_total
ORDER BY t.total DESC;

Обороты вышли 4100, 1200 и 500, среднее по ним 1933.33, выше него одна Аня. Смотри на два места. user_totals строится поверх paid, а average поверх user_totals: ступени видят друг друга по цепочке назад. И average возвращает ровно одну строку, поэтому её цепляют через CROSS JOIN — одна строка на всё ничего не размножает, а работает подстановкой константы.

Вперёд ссылаться нельзя. Поставь a AS (SELECT * FROM b) раньше определения b, и PostgreSQL ответит relation "b" does not exist с подсказкой переставить элементы местами. В MS SQL действует тот же запрет, документация Microsoft называет это forward referencing.

CTE против подзапроса: что меняется в плане

Читаемость меняется всегда, а вот план запроса далеко не обязательно. Проверим на запросе покороче, чтобы план помещался в две строки:

EXPLAIN
WITH paid AS (
  SELECT * FROM orders WHERE status = 'paid'
)
SELECT * FROM paid WHERE user_id = 1;

 Seq Scan on orders  (cost=0.00..1.09 rows=2 width=21)
   Filter: ((status = 'paid'::text) AND (user_id = 1))

Узла CTE в плане нет. PostgreSQL подставил определение в основной запрос и склеил оба условия в один фильтр. Тот же запрос, написанный подзапросом в FROM, даёт идентичный план, строка в строку.

Отдельный случай, когда обёртка обязательна независимо от вкусов, — фильтр по оконной функции. Окно считается после WHERE, поэтому WHERE rn = 1 приходится выносить наружу, и WITH ranked AS (...) читается тут заметно лучше вложенного FROM (...) t. Порядок выполнения разобран в статье про оконные функции.

Материализация: когда CTE становится барьером

До PostgreSQL 12 любой CTE вычислялся отдельно и целиком, а результат складывался во временный набор строк. Внешние условия внутрь не проваливались, оптимизатор через границу CTE не заглядывал. Этим даже пользовались намеренно, как заградительным барьером.

С 12-й версии правило другое. CTE подставляется в основной запрос, если он нерекурсивный, не содержит volatile-функций и ссылка на него в запросе ровно одна. Две ссылки и больше — вычисляется один раз и материализуется, как раньше. Поведение переопределяется явно, и MATERIALIZED заставляет считать отдельно:

EXPLAIN
WITH paid AS MATERIALIZED (
  SELECT * FROM orders WHERE status = 'paid'
)
SELECT * FROM paid WHERE user_id = 1;

 CTE Scan on paid  (cost=1.07..1.19 rows=1 width=48)
   Filter: (user_id = 1)
   CTE paid
     ->  Seq Scan on orders  (cost=0.00..1.07 rows=5 width=21)
           Filter: (status = 'paid'::text)

План распался надвое. Внутри узла CTE paid собираются все пять оплаченных заказов, и только потом CTE Scan отбрасывает чужие по user_id = 1. Условие осталось снаружи барьера. На шести строках это стоит ноль, на миллионах здесь проходит граница между «взять по индексу две строки» и «материализовать полтаблицы, потом отфильтровать».

Тот же эффект получается без подсказки, если сослаться на CTE дважды. Запрос с двумя count(*) по paid в плане показывает узел CTE paid и два CTE Scan над ним: таблица читается один раз, оба счётчика идут по накопленному набору, фильтр user_id = 1 применяется уже к нему. Допишешь NOT MATERIALIZED — узел CTE исчезнет, вместо него появятся два Seq Scan on orders, зато фильтр уедет внутрь скана. Вот весь размен: одно вычисление против сквозной оптимизации.

Практическое правило одно. Подсказки не трогай, пока не увидел проблему в EXPLAIN: MATERIALIZED ставят, когда внутри CTE дорогой расчёт и повторять его накладно, а NOT MATERIALIZED — когда CTE лёгкий и внешний фильтр обязан пробиться внутрь. Обе подсказки есть в PostgreSQL с 12-й версии и в SQLite с 3.35; в MySQL и MS SQL их нет.

Рекурсивный CTE: WITH RECURSIVE

Рекурсивный CTE ссылается сам на себя. Он состоит из двух частей, между которыми стоит UNION ALL или, если повторы нужно отбрасывать, UNION:

• якорь: стартовый набор строк, вычисляется один раз;
• рекурсивная часть: читает то, что добавила предыдущая итерация, и добавляет новые строки.

Итерации идут, пока рекурсивная часть возвращает хоть одну строку. Вернула ноль, и обход закончен: UNION ALL склеивает якорь и все итерации в один результат.

Повод из практики: дни без заказов в отчёте просто отсутствуют, и расчёт «прирост к предыдущему дню» перепрыгивает через дыру. Лечится сгенерированным календарём, который приклеивают к дневной выручке:

WITH RECURSIVE calendar AS (
  SELECT date '2026-06-01' AS day
  UNION ALL
  SELECT day + 1 FROM calendar WHERE day < date '2026-06-07'
),
daily AS (
  SELECT created_at AS day, sum(amount) AS revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY created_at
)
SELECT c.day, coalesce(d.revenue, 0) AS revenue
FROM calendar c
LEFT JOIN daily d ON d.day = c.day
ORDER BY c.day;

Разбери calendar по частям. Якорь тут SELECT date '2026-06-01', ровно одна строка. Рекурсивная часть берёт последнюю добавленную дату и прибавляет сутки. Условие day < date '2026-06-07' работает тормозом: на 7 июня рекурсивная часть вернёт пусто, и обход остановится. Второй CTE, daily, обычный, не рекурсивный. Слово RECURSIVE пишется один раз сразу после WITH и распространяется на весь список, помечать каждый элемент не нужно.

Бесконечная рекурсия. Без условия остановки рекурсивная часть всегда возвращает строку, и обход не заканчивается:

WITH RECURSIVE endless AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM endless
)
SELECT count(*) FROM endless;

Запрос крутится, пока не упрётся в память или в таймаут. На тестовой базе с statement_timeout = '5s' он закончился так:

ERROR:  canceling statement due to statement timeout

Основная страховка — условие в WHERE рекурсивной части, без него запрос писать нельзя. Если данные грязные и на условие полагаться страшно, добавь счётчик глубины: step + 1 в рекурсивной части и WHERE step < 100 рядом с основным условием. На графах с циклами берут UNION вместо UNION ALL: повторно пришедшая строка отсеивается, и обход останавливается сам. Внешний LIMIT тоже тормозит рекурсию: PostgreSQL считает её лениво и бросает итерации, набрав нужное число строк. Но страховка эта хрупкая. Любой ORDER BY, DISTINCT или агрегат над CTE требует весь набор целиком, ленивость пропадает, и SELECT * FROM endless ORDER BY n LIMIT 5 крутится до таймаута.

Календарь в PostgreSQL быстрее собрать через generate_series(), так что пример выше учебный. Настоящая работа для WITH RECURSIVE начинается на иерархиях: дерево категорий, цепочка подчинения, разузлование спецификации. Скелет тот же, меняется содержимое двух частей. Якорь выбирает корни (WHERE parent_id IS NULL), рекурсивная часть присоединяет детей по совпадению parent_id с id родителя.

Чем CTE не является

CTE — не временная таблица. Он живёт ровно один запрос, до точки с запятой. Выполни WITH user_totals AS (...) SELECT count(*) FROM user_totals; и следом отдельным запросом SELECT * FROM user_totals; — второй упадёт:

ERROR:  relation "user_totals" does not exist

Индексов на CTE нет и не будет. Вернись к плану материализованного CTE выше: там CTE Scan on paid с Filter, то есть полный проход по накопленному набору с проверкой условия на каждой строке. Индексируется исходная таблица, а не результат. Внутри узла CTE paid индекс работает как обычно: на большой таблице с подходящим условием PostgreSQL возьмёт там Index Scan. А вот накопленный этим узлом временный набор индексировать нечем, поэтому CTE Scan поверх него всегда идёт полным проходом.

Отсюда и граница применимости. Промежуточный набор нужен нескольким запросам подряд, он крупный, и по нему идут точечные выборки по ключу: бери CREATE TEMP TABLE, на ней строятся индексы, и живёт она до конца сессии. Задача CTE другая, разложить один запрос на понятные шаги.

Типичные ошибки

Забыть RECURSIVE. PostgreSQL не догадается о рекурсии по факту ссылки на себя:

ERROR:  relation "calendar" does not exist
DETAIL:  There is a WITH item named "calendar", but it cannot be referenced from this part of the query.
HINT:  Use WITH RECURSIVE, or re-order the WITH items to remove forward references.

Сообщение сбивает с толку: calendar вот он, прямо в запросе. Но DETAIL объясняет ровно это. Элемент с таким именем есть, только сослаться на него из этой части запроса нельзя, а готовое решение лежит в HINT. В MySQL правило то же: RECURSIVE обязателен, если хоть один CTE в списке рекурсивный. В MS SQL этого слова нет вовсе, там CTE становится рекурсивным просто потому, что ссылается на себя.

Ждать от CTE ускорения. Переписывание подзапроса в CTE само по себе не ускоряет ничего: при одной ссылке план совпадает построчно. Иногда выходит медленнее, если CTE материализовался и запретил протолкнуть фильтр внутрь. CTE берут ради читаемости, а скорость смотрят в EXPLAIN.

Фильтровать снаружи тяжёлого CTE. Пока ссылка одна, PostgreSQL сам протолкнёт условие внутрь. Появилась вторая ссылка, и CTE молча стал материализованным: расчёт выполняется целиком, фильтр применяется к готовому набору. Запрос при этом работает и данные отдаёт верные, поэтому проблему замечают по времени выполнения, а не по ошибке.

Ставить NOT MATERIALIZED не глядя. Подсказка подставляет определение в каждое место ссылки. Десять ссылок на дорогой CTE превратятся в десять вычислений.

Писать ORDER BY внутри CTE. Сортировка внутри ничего не гарантирует внешнему запросу: тот вправе прочитать строки в любом порядке. ORDER BY ставят в самом внешнем SELECT. В T-SQL это ограничение ещё и синтаксическое: ORDER BY в определении CTE запрещён, если рядом нет TOP или OFFSET/FETCH.

Частые вопросы

Работает ли WITH в MS SQL, MySQL и SQLite

Да, WITH имя AS (...) — стандартный синтаксис, он одинаковый везде. Везде же можно дописать к имени список колонок: WITH sales_cte (person_id, total) AS (...). MySQL умеет CTE начиная с 8.0, на 5.7 такой запрос не выполнится. Дальше отличия точечные:

• В T-SQL нет ключевого слова RECURSIVE, а глубина рекурсии по умолчанию ограничена сотней уровней. Меняется через OPTION (MAXRECURSION n), где 0 снимает лимит. Между якорем и рекурсивной частью там разрешён только UNION ALL.
• В MySQL RECURSIVE обязателен, а предел глубины задаёт переменная cte_max_recursion_depth со значением 1000 по умолчанию.
• В MS SQL результат CTE не материализуется никогда: каждая внешняя ссылка выполняет определение заново. Документация Microsoft прямо советует брать временную таблицу, если ссылок на набор несколько.
• Если WITH идёт в батче не первым, T-SQL требует точку с запятой после предыдущей инструкции. Отсюда ;WITH в чужом коде.

Что быстрее — CTE или подзапрос

В PostgreSQL 12 и новее при одной ссылке разницы нет, планы совпадают. На версиях до 12-й подзапрос часто выигрывал: CTE всегда материализовался и блокировал проталкивание фильтров. MS SQL разворачивает CTE в подзапрос всегда. MySQL решает сам, склеить определение с основным запросом или материализовать. Так что вопрос почти никогда не про скорость, а про то, какой вариант поймёт следующий человек.

Можно ли использовать CTE в INSERT, UPDATE и DELETE

Да, причём в обе стороны. CTE готовит набор для INSERT или UPDATE, а в PostgreSQL он умеет и сам менять данные: WITH removed AS (DELETE FROM orders WHERE status = 'cancelled' RETURNING *) SELECT id, amount FROM removed; удалит отменённый заказ 13 и тут же покажет удалённую строку. Такой CTE выполняется ровно один раз, и все части запроса видят снимок данных на начало выполнения. В MS SQL пишущих CTE нет, там WITH только готовит набор, а меняет данные внешняя инструкция.

Чем CTE отличается от представления

Представление (VIEW) хранится в базе и доступно всем запросам, CTE существует внутри одного запроса и после точки с запятой исчезает. Если один и тот же промежуточный набор нужен в трёх отчётах, ему место в представлении. Если он нужен ради одного запроса, тащить его в схему базы незачем.

Где потренироваться

Написать эти запросы руками полезнее, чем прочитать. Начни с восьми задач по SQL с решениями: в шестой стоит ровно тот FROM (...) t, который переписывается в WITH без единой правки в логике, а восьмая с помесячным приростом заметно выигрывает, если разложить её на две ступени.