Как написать запрос с использованием CTE в SQL

Что проверяют

Умеете ли вы разбить сложный запрос на читаемые шаги и знаете ли, чем CTE отличается от подзапроса и временной таблицы. Следом почти всегда просят рекурсивный CTE для дерева и спрашивают про производительность. От Go-разработчика ещё ждут, как этот запрос живёт в коде.

Как отвечать

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

WITH paid AS (
  SELECT user_id, sum(amount) AS total
  FROM orders WHERE status = 'paid'
  GROUP BY user_id
)
SELECT u.email, p.total
FROM users u JOIN paid p ON p.user_id = u.id
WHERE p.total > $1;

Рекурсивный вариант WITH RECURSIVE состоит из якорной части и рекурсивной, соединённых UNION ALL: так обходят оргструктуру, дерево категорий, граф зависимостей. Глубину нужно ограничивать или отслеживать циклы, иначе запрос не завершится. В PostgreSQL 14 для этого появилась конструкция CYCLE.

Про производительность отвечайте точно:

• PostgreSQL до 12: CTE всегда материализовалась и работала как барьер оптимизации, внешний фильтр внутрь не проталкивался;
• PostgreSQL 12 и новее: нерекурсивная CTE без побочных эффектов, на которую ссылаются один раз, встраивается в запрос, а поведение задаётся явно через AS MATERIALIZED или AS NOT MATERIALIZED;
• MySQL: поддерживает CTE с версии 8.0.

В Go CTE это обычный текст запроса: db.QueryContext(ctx, query, minTotal), плейсхолдеры $1 для pgx и lib/pq, ? для MySQL. Не забывайте defer rows.Close() и проверку rows.Err() после цикла. Длинный запрос удобно держать в .sql через embed или генерировать по нему типизированный код с sqlc.

Где спотыкаются

Говорят, что CTE ускоряет запрос. Сама по себе она про читаемость, в старом PostgreSQL могла и замедлить, а реальный план показывает только EXPLAIN ANALYZE.

Путают CTE с временной таблицей: думают, что результат сохраняется и его можно использовать в следующем вызове из Go.

Пишут рекурсию без условия остановки и не понимают разницы UNION и UNION ALL: UNION убирает дубли и дороже, но иногда именно он спасает от зацикливания.