Что такое WITH … AS (CTE) в SQL?

WITH ... AS — это способ дать имя промежуточному запросу и потом использовать его в основном запросе как обычную таблицу.

Такой промежуточный запрос называется CTE — Common Table Expression. По-русски иногда говорят «обобщённое табличное выражение», но в реальной работе чаще услышишь просто: «сделай через CTE», «вынеси в with-блок», «разбей запрос через WITH».

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

Мы сначала подготавливаем данные в понятном отдельном блоке, даём этому блоку имя, а потом работаем с ним дальше.

Это похоже на аккуратную кухню. Можно вывалить все продукты, специи и посуду в одну огромную кастрюлю и надеяться, что получится суп. А можно сначала нарезать овощи, отдельно сварить бульон, отдельно подготовить мясо — и потом спокойно собрать блюдо. CTE делает с SQL-запросом примерно то же самое: разбивает большую кашу на понятные шаги.

Зачем нужен CTE

Главная причина — читаемость.

Представь, что у нас есть таблица заказов. Нужно:

1. Посчитать сумму заказов по каждому клиенту.
2. Посчитать общую сумму всех заказов.
3. Показать долю каждого крупного клиента в общей выручке.

Без CTE запрос может выглядеть так:

SELECT
  customer_id,
  total,
  total * 1.0 / (SELECT SUM(amount) FROM orders) AS share
FROM (
  SELECT
    customer_id,
    SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
) sub
WHERE total > 1000
ORDER BY total DESC;

Запрос рабочий, но читать его не очень приятно. Внутри одного запроса спрятан другой запрос, рядом ещё один подзапрос, логика скачет туда-сюда. Новичку приходится держать в голове сразу всё.

Теперь тот же смысл через CTE:

WITH per_customer AS (
  SELECT
    customer_id,
    SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
),
grand_total AS (
  SELECT SUM(amount) AS sum_all
  FROM orders
)
SELECT
  pc.customer_id,
  pc.total,
  pc.total * 1.0 / gt.sum_all AS share
FROM per_customer pc
CROSS JOIN grand_total gt
WHERE pc.total > 1000
ORDER BY pc.total DESC;

Да, второй вариант длиннее. Но он намного понятнее:

1. per_customer — считаем сумму по каждому клиенту.
2. grand_total — считаем общую сумму.
3. В основном запросе соединяем результаты и считаем долю.

Такой запрос легче читать, легче проверять и легче чинить. Особенно через месяц, когда ты уже забыл, зачем писал этот код.

Базовый синтаксис

Общий шаблон выглядит так:

WITH cte_name AS (
  SELECT ...
  FROM ...
)
SELECT ...
FROM cte_name;

Здесь две части:

1. WITH cte_name AS (...) — создаём временный именованный результат.
2. Основной запрос — используем этот результат как таблицу.

Важно: CTE не живёт сам по себе. После блока WITH обязательно должен идти основной запрос: SELECT, INSERT, UPDATE или DELETE.

Вот так нельзя:

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
);

Почему нельзя? Потому что мы только описали промежуточный результат, но не сказали, что с ним делать.

Правильно так:

WITH customer_totals AS (
  SELECT
    customer_id,
    SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT *
FROM customer_totals;

Простой пример

Допустим, есть таблица orders:

id	customer_id	amount
1	1	100
2	1	250
3	2	80
4	3	500

Нужно найти клиентов, у которых сумма заказов больше 200, и отсортировать их по убыванию суммы.

Запрос:

WITH customer_totals AS (
  SELECT
    customer_id,
    SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT *
FROM customer_totals
WHERE total > 200
ORDER BY total DESC;

Что происходит внутри:

Сначала выполняется логика из customer_totals:

SELECT
  customer_id,
  SUM(amount) AS total
FROM orders
GROUP BY customer_id;

Она даёт такой промежуточный результат:

customer_id	total
1	350
2	80
3	500

Потом основной запрос берёт этот результат и применяет фильтр:

WHERE total > 200

Остаются только клиенты с суммой больше 200:

customer_id	total
3	500
1	350

Вот в этом и сила CTE: сначала мы спокойно считаем промежуточную таблицу, потом спокойно работаем с ней дальше.

Почему CTE удобно читать

Без CTE часто получается запрос-матрёшка: один SELECT внутри другого, тот внутри третьего, а где начинается главная логика — уже непонятно.

С CTE запрос превращается в цепочку шагов:

WITH step_1 AS (
  SELECT ...
),
step_2 AS (
  SELECT ...
  FROM step_1
),
step_3 AS (
  SELECT ...
  FROM step_2
)
SELECT ...
FROM step_3;

Это особенно полезно в аналитике, где запрос часто похож на небольшой конвейер:

1. Берём нужные строки.
2. Считаем метрики.
3. Фильтруем результат.
4. Добавляем ранги.
5. Выводим финальную таблицу.

Чем больше логики в запросе, тем полезнее CTE.

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

В одном запросе можно объявить несколько CTE. Они перечисляются после WITH через запятую.

WITH active_users AS (
  SELECT id, email
  FROM users
  WHERE last_login_at > NOW() - INTERVAL '30 days'
),
big_orders AS (
  SELECT
    user_id,
    COUNT(*) AS orders_cnt
  FROM orders
  WHERE amount > 1000
  GROUP BY user_id
)
SELECT
  u.id,
  u.email,
  COALESCE(b.orders_cnt, 0) AS big_orders
FROM active_users u
LEFT JOIN big_orders b ON b.user_id = u.id
ORDER BY big_orders DESC;

Здесь два промежуточных блока:

1. active_users — пользователи, которые заходили за последние 30 дней.
2. big_orders — количество крупных заказов по каждому пользователю.

Потом основной запрос соединяет эти два результата через LEFT JOIN.

Обрати внимание на синтаксис: слово WITH пишется один раз, а сами CTE разделяются запятыми.

Правильно:

WITH first_cte AS (
  SELECT ...
),
second_cte AS (
  SELECT ...
)
SELECT ...
FROM first_cte;

Неправильно:

WITH first_cte AS (
  SELECT ...
)
WITH second_cte AS (
  SELECT ...
)
SELECT ...
FROM first_cte;

Два раза подряд писать WITH нельзя.

CTE можно использовать в JOIN

CTE ведёт себя почти как обычная таблица внутри запроса. Его можно использовать в FROM, соединять через JOIN, фильтровать через WHERE, группировать, сортировать и так далее.

Например, найдём топ-10 товаров по количеству продаж, а потом подтянем к ним название и категорию.

WITH top_products AS (
  SELECT
    product_id,
    SUM(quantity) AS sold
  FROM order_items
  GROUP BY product_id
  ORDER BY sold DESC
  LIMIT 10
)
SELECT
  p.name,
  p.category,
  t.sold
FROM top_products t
JOIN products p ON p.id = t.product_id
ORDER BY t.sold DESC;

Внутри top_products мы не думаем о названиях товаров. Там только одна задача — найти самые продаваемые товары.

В основном запросе мы уже соединяем результат с таблицей products и получаем красивые данные для вывода.

Такой подход делает запрос спокойным и управляемым: каждый блок отвечает за свою часть работы.

CTE может ссылаться на предыдущий CTE

Один CTE может использовать результат другого CTE, объявленного выше.

WITH paid_orders AS (
  SELECT
    id,
    customer_id,
    amount
  FROM orders
  WHERE status = 'paid'
),
customer_totals AS (
  SELECT
    customer_id,
    SUM(amount) AS total
  FROM paid_orders
  GROUP BY customer_id
)
SELECT *
FROM customer_totals
WHERE total > 1000
ORDER BY total DESC;

Здесь логика идёт по шагам:

1. paid_orders — берём только оплаченные заказы.
2. customer_totals — считаем сумму по клиентам уже среди оплаченных заказов.
3. Основной запрос — оставляет только крупных клиентов.

Это очень хороший стиль для сложных запросов: не пытаться сразу сделать всё, а собирать результат постепенно.

CTE vs подзапрос

Иногда CTE и обычный подзапрос делают одно и то же.

Вариант с подзапросом:

SELECT *
FROM (
  SELECT
    customer_id,
    SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
) sub
WHERE total > 1000;

Вариант с CTE:

WITH per_customer AS (
  SELECT
    customer_id,
    SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT *
FROM per_customer
WHERE total > 1000;

Результат будет одинаковым. Разница — в удобстве чтения.

CTE обычно лучше, когда:

• промежуточная логика длинная;
• один и тот же результат нужен несколько раз;
• запрос удобно объяснить как набор шагов;
• нужно дать промежуточному результату понятное имя;
• запрос станет читать другой человек.

Обычный подзапрос часто лучше, когда:

• он короткий;
• используется один раз;
• находится прямо в условии;
• не мешает читать основной запрос.

Например, такой подзапрос вполне нормален:

SELECT *
FROM orders
WHERE customer_id IN (
  SELECT id
  FROM customers
  WHERE city = 'Berlin'
);

Делать отдельный CTE ради такого короткого условия не всегда нужно. Хороший SQL — это не «везде CTE», а понятный запрос без лишней тяжести.

CTE и временная таблица — это не одно и то же

Новички иногда думают, что CTE создаёт настоящую временную таблицу. Это не совсем так.

CTE существует только внутри одного SQL-выражения.

Например:

WITH customer_totals AS (
  SELECT
    customer_id,
    SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT *
FROM customer_totals;

После выполнения этого запроса имя customer_totals исчезает. В следующем запросе его уже нет.

Такой запрос не сработает:

SELECT *
FROM customer_totals;

Если тебе нужен промежуточный результат, который можно использовать в нескольких разных запросах, тогда нужны другие инструменты:

• VIEW — сохранённое представление;
• временная таблица;
• обычная таблица для заранее рассчитанных данных.

CTE — это не хранилище. Это удобный именованный шаг внутри одного запроса.

CTE с INSERT, UPDATE и DELETE в PostgreSQL

В PostgreSQL CTE может использовать не только SELECT, но и запросы изменения данных: INSERT, UPDATE, DELETE, особенно вместе с RETURNING.

Например, можно удалить старые заказы и сразу вставить удалённые строки в архив:

WITH archived AS (
  DELETE FROM orders
  WHERE created_at < NOW() - INTERVAL '2 years'
  RETURNING *
)
INSERT INTO orders_archive
SELECT *
FROM archived;

Что здесь происходит:

1. DELETE удаляет старые заказы из orders.
2. RETURNING * возвращает удалённые строки.
3. Основной INSERT вставляет эти строки в orders_archive.

Всё выполняется как один SQL-statement. Это удобно, когда нужно сделать связанную операцию аккуратно и без промежуточных ручных шагов.

Но важно понимать: так умеют не все базы данных одинаково. Это сильная сторона PostgreSQL, а в других СУБД синтаксис и возможности могут отличаться.

Можно ли изменить сам CTE

CTE нельзя обновлять как настоящую таблицу.

Вот так делать нельзя:

WITH expensive_orders AS (
  SELECT *
  FROM orders
  WHERE amount > 1000
)
UPDATE expensive_orders
SET status = 'vip';

Почему? Потому что expensive_orders — не настоящая таблица. Это именованный результат внутри запроса.

Если нужно менять данные, меняй настоящую таблицу:

UPDATE orders
SET status = 'vip'
WHERE amount > 1000;

А CTE можно использовать, чтобы сначала аккуратно выбрать нужные строки, а потом применить изменение к реальной таблице.

Например:

WITH target_orders AS (
  SELECT id
  FROM orders
  WHERE amount > 1000
)
UPDATE orders o
SET status = 'vip'
FROM target_orders t
WHERE o.id = t.id;

Здесь target_orders помогает выбрать нужные id, но обновляется всё равно настоящая таблица orders.

Производительность CTE в PostgreSQL

Есть важная историческая деталь про PostgreSQL.

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

Начиная с PostgreSQL 12, оптимизатор стал умнее: многие обычные нерекурсивные CTE он может встроить в основной запрос, то есть обработать примерно как подзапрос.

Обычно это хорошо: ты пишешь понятный код, а PostgreSQL сам решает, как лучше его выполнить.

Если нужно явно попросить PostgreSQL материализовать CTE, можно написать так:

WITH customer_totals AS MATERIALIZED (
  SELECT
    customer_id,
    SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT *
FROM customer_totals
WHERE total > 1000;

Если нужно явно попросить PostgreSQL не материализовать CTE и попробовать встроить его в основной запрос:

WITH customer_totals AS NOT MATERIALIZED (
  SELECT
    customer_id,
    SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT *
FROM customer_totals
WHERE total > 1000;

Для новичка главный вывод простой: в большинстве обычных ситуаций пиши простой WITH ... AS (...) и не усложняй раньше времени. О производительности стоит думать, когда запрос реально стал медленным и ты смотришь план выполнения.

Частые ошибки новичков

CTE без основного запроса

Ошибка:

WITH customer_totals AS (
  SELECT
    customer_id,
    SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
);

CTE не является самостоятельным запросом. После него должен идти основной SELECT, INSERT, UPDATE или DELETE.

Правильно:

WITH customer_totals AS (
  SELECT
    customer_id,
    SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT *
FROM customer_totals;

Два WITH подряд

Ошибка:

WITH active_users AS (
  SELECT id
  FROM users
)
WITH paid_orders AS (
  SELECT user_id
  FROM orders
  WHERE status = 'paid'
)
SELECT *
FROM active_users;

Правильно использовать один WITH, а CTE разделять запятыми:

WITH active_users AS (
  SELECT id
  FROM users
),
paid_orders AS (
  SELECT user_id
  FROM orders
  WHERE status = 'paid'
)
SELECT *
FROM active_users;

Лишний CTE ради одного SELECT

Иногда новичок пишет так:

WITH all_orders AS (
  SELECT *
  FROM orders
)
SELECT *
FROM all_orders;

Формально запрос рабочий, но смысла в CTE здесь нет. Лучше проще:

SELECT *
FROM orders;

CTE нужен не для красоты ради красоты, а для понятного разделения логики.

Ожидание, что CTE сохранится после запроса

CTE исчезает сразу после выполнения SQL-statement.

Такой подход не сработает:

WITH recent_orders AS (
  SELECT *
  FROM orders
  WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT *
FROM recent_orders;

SELECT COUNT(*)
FROM recent_orders;

Во втором запросе recent_orders уже не существует. Если результат нужен повторно, используй временную таблицу или представление.

Попытка сослаться на CTE, который объявлен ниже

Такой запрос проблемный:

WITH second_cte AS (
  SELECT *
  FROM first_cte
),
first_cte AS (
  SELECT *
  FROM orders
)
SELECT *
FROM second_cte;

Лучше объявлять шаги сверху вниз: сначала то, от чего зависят другие блоки, потом следующие шаги.

WITH first_cte AS (
  SELECT *
  FROM orders
),
second_cte AS (
  SELECT *
  FROM first_cte
)
SELECT *
FROM second_cte;

Читай запрос как инструкцию: сначала подготовили первый результат, потом использовали его во втором, потом вывели финал.

Как понять, что пора использовать CTE

Используй CTE, если в голове появляется мысль:

«Сначала я хочу получить вот эти данные, потом на их основе посчитать вот это, а потом уже вывести результат».

Например:

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

Это идеальный случай для CTE.

WITH paid_orders AS (
  SELECT
    customer_id,
    amount
  FROM orders
  WHERE status = 'paid'
),
customer_totals AS (
  SELECT
    customer_id,
    SUM(amount) AS total
  FROM paid_orders
  GROUP BY customer_id
),
big_customers AS (
  SELECT *
  FROM customer_totals
  WHERE total > 1000
)
SELECT
  c.id,
  c.email,
  b.total
FROM big_customers b
JOIN customers c ON c.id = b.customer_id
ORDER BY b.total DESC;

Такой запрос читается почти как рассказ:

1. Берём оплаченные заказы.
2. Считаем суммы по клиентам.
3. Оставляем крупных клиентов.
4. Показываем их email и сумму.

Вот это и есть хороший SQL: не просто «чтобы работало», а чтобы человек мог понять ход мысли.

Мини-резюме

WITH ... AS создаёт CTE — временный именованный результат внутри одного SQL-запроса.

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

Несколько CTE пишутся после одного WITH и разделяются запятыми.

CTE можно использовать в FROM, JOIN, WHERE, GROUP BY, ORDER BY — почти как обычную таблицу внутри запроса.

CTE не сохраняется между запросами. Он живёт только во время выполнения одного SQL-statement.

В PostgreSQL CTE может использоваться вместе с INSERT, UPDATE, DELETE и RETURNING, что удобно для аккуратных атомарных операций.

В современных версиях PostgreSQL обычные нерекурсивные CTE часто оптимизируются достаточно хорошо, поэтому не бойся использовать их для читаемости.

Главное правило: если запрос становится трудно читать — попробуй разложить его на несколько понятных CTE. Хороший WITH не усложняет SQL, а наводит в нём порядок.