CTE с WITH

CTE — имя для результата внутри запроса

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

WITH sales(amount) AS (VALUES (100), (250), (400)),
large_sales AS (SELECT amount FROM sales WHERE amount >= 250)
SELECT SUM(amount) AS total FROM large_sales;

Результат — 650. Здесь sales и large_sales доступны только в этом SQL-выражении; постоянные таблицы не создаются. Несколько CTE разделяются запятой, а точка с запятой ставится после основного SELECT.

В прошлом уроке скалярный подзапрос жил внутри WHERE прямо на месте. Логически он вычислялся первым, возвращал одно значение, а внешний запрос использовал его в условии; физический порядок при этом выбирал оптимизатор. Этот механизм работает, но при усложнении запроса вложенные конструкции становятся трудночитаемыми. CTE позволяет дать промежуточному результату имя и вынести его наверх, не меняя логику.

Синтаксис WITH cte_name AS (...) и природа CTE как именованного временного результата

CTE объявляется перед основным запросом: ключевое слово WITH, затем имя, затем AS и в скобках — обычный SELECT. После закрывающей скобки сразу идёт основной запрос, который обращается к этому имени как к обычной таблице.

WITH expensive_products AS (
  SELECT product_id, title, price
  FROM products
  WHERE price > 1000
)
SELECT title, price
FROM expensive_products
ORDER BY price DESC;

Здесь expensive_products — это не таблица в базе данных. Это имя, которое живёт ровно на время выполнения одного SQL-выражения. Точка с запятой стоит только в конце основного SELECT — весь блок от WITH до последней строки является единым запросом.

Что именно происходит внутри: СУБД читает определение CTE, а затем основной SELECT может использовать его как табличный источник в FROM или JOIN; после этого столбцы CTE доступны в SELECT, WHERE и других предложениях. К CTE также можно обратиться из подзапроса внутри WHERE. Оптимизатор сам решает, вычислять ли CTE заранее или встраивать его как подзапрос — это детали реализации, не влияющие на результат.

Важная граница: CTE не создаёт никакого объекта в схеме базы. Если написать следующий SQL-запрос (уже после точки с запятой), имя expensive_products там будет недоступно — СУБД вернёт ошибку «no such table». Это принципиально отличает CTE от представления (VIEW), которое сохраняется в схеме, и от временной таблицы. CTE — это именованный промежуточный результат только для текущего запроса.

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

Переписывание вложенного подзапроса в CTE

Возьмём запрос, который уже знаком: найти товары с максимальной ценой.

-- вариант со скалярным подзапросом
SELECT title
FROM products
WHERE price = (SELECT MAX(price) FROM products);

В логической модели чтения скалярный подзапрос (SELECT MAX(price) FROM products) вычисляется первым и возвращает одно число; физический план выполнения оптимизатор может устроить иначе. Логика понятная, но подзапрос спрятан внутри WHERE. Теперь вынесем его в CTE:

WITH max_price AS (
  SELECT MAX(price) AS value
  FROM products
)
SELECT title
FROM products
WHERE price = (SELECT value FROM max_price);

Механизм тот же: max_price содержит одну строку с одним столбцом value. Основной SELECT обращается к нему скалярным подзапросом (SELECT value FROM max_price) — и получает то самое максимальное значение. Результат идентичен первому варианту.

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

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

Два последовательных CTE: зависимость второго от первого и синтаксис запятой

Когда промежуточных шагов два, CTE перечисляются через запятую после одного WITH. Второй CTE может напрямую ссылаться на первый — это и есть «последовательная» цепочка.

Задача: найти имена клиентов, чья суммарная сумма заказов превышает среднюю по всем клиентам с заказами.

WITH totals AS (
  SELECT customer_id, SUM(total_amount) AS total
  FROM orders
  GROUP BY customer_id
),
above_average AS (
  SELECT customer_id
  FROM totals
  WHERE total > (SELECT AVG(total) FROM totals)
)
SELECT c.name
FROM customers AS c
JOIN above_average AS a ON c.customer_id = a.customer_id;

Что здесь происходит шаг за шагом. totals считает сумму заказов для каждого клиента — это агрегация с GROUP BY. above_average берёт totals как источник и фильтрует строки, где total превышает среднее по тому же totals. Обратите внимание: AVG(total) считается скалярным подзапросом уже из CTE, а не из исходной таблицы — это именно то, ради чего первый CTE и существует. Основной SELECT соединяет таблицу customers с above_average по customer_id и возвращает имена.

Два синтаксических правила, нарушить которые легко:

• Запятая стоит между CTE, а не после последнего перед основным SELECT. Пропустить её — синтаксическая ошибка.
• Слово WITH пишется один раз. Написать WITH totals AS (...) WITH above_average AS (...) — тоже ошибка.

Для переносимого SQL объявляйте зависимость раньше: если cte2 читает cte1, сначала записывайте cte1. PostgreSQL требует такой порядок. SQLite может разрешить прямую ссылку на CTE, объявленный ниже в том же WITH, но переносить эту особенность в другие СУБД нельзя.

Такая цепочка превращает сложный запрос в последовательность именованных шагов: сначала агрегация, затем фильтрация по агрегату, затем финальное соединение. Каждый шаг читается отдельно, и его назначение ясно из имени.