CTE — это именованный подзапрос
CTE (Common Table Expression) — это подзапрос, который вынесли наружу и дали ему имя. Вместо того чтобы вкладывать SELECT внутрь другого SELECT, вы описываете его в начале через WITH, придумываете название, а затем обращаетесь к нему в основном запросе как к обычной таблице.
Структура всегда одна и та же:
Читаем сверху вниз: сначала формируем именованный результат customer_totals, а уже потом обращаемся к нему. По сути, CTE ведёт себя как временное представление, которое живёт только в рамках одного запроса.
Тот же запрос без CTE
А вот та же логика, записанная через подзапрос — чтобы было видно, что именно заменяет CTE:
Тот же результат. Но обратите внимание на порядок чтения: глазу приходится нырять внутрь скобок, разбираться, что там считается, и потом возвращаться обратно. В версии с CTE всё читается в том порядке, в котором происходят вычисления: сначала описываем промежуточный результат, потом используем его. На маленьком запросе разницы почти нет. А вот на запросе из трёх-четырёх шагов это разница между кодом, который можно пробежать глазами, и кодом, который приходится расшифровывать.
Несколько CTE в одном запросе
CTE можно объединять в цепочку через запятую. Каждый следующий может ссылаться на те, что объявлены раньше, — так получается конвейер именованных шагов:
Один WITH, дальше определения CTE через запятую. Второй CTE (big_spenders) обращается к первому (customer_totals) так же, как к обычной таблице. Главный SELECT идёт после последнего определения CTE.
Типичная промашка — написать WITH ещё раз перед вторым CTE. Не надо — это ошибка синтаксиса. Одного WITH хватает на все определения сразу.
Несколько обращений к одному CTE
Вот где CTE реально обходят подзапросы. Если один и тот же промежуточный результат нужен в двух местах, CTE позволяет вычислить его один раз и сослаться дважды:
CTE используется здесь дважды: первый раз — чтобы посчитать среднее, второй — как основной источник данных. Без CTE пришлось бы дублировать запрос с GROUP BY, и любую правку нужно было бы вносить в двух местах сразу.
CTE с INSERT, UPDATE и DELETE
CTE — это не только про SELECT. Конструкцию WITH можно поставить перед INSERT, UPDATE или DELETE, чтобы использовать именованный подзапрос в запросе на изменение данных:
CTE описывает, какие строки нужно пометить, а INSERT ... SELECT берёт его как источник данных. Тот же приём отлично работает и с DELETE FROM ... WHERE id IN (SELECT id FROM cte) — удобно для пакетных удалений, когда логика отбора нетривиальная.
Когда стоит использовать CTE
Несколько практических ориентиров:
- В запросе больше одного логического шага. Сначала агрегируем, потом фильтруем по агрегату, потом джойним результат — это конвейер, и отдельный CTE на каждый шаг делает его читаемым.
- Иначе пришлось бы повторять один и тот же подзапрос. Опишите его один раз — обращайтесь сколько угодно.
- Подзапросу нужно имя. Если над подзапросом так и просится комментарий с пояснением, что он вообще считает, — имя CTE и есть этот комментарий, только закреплённый синтаксисом.
- Вы собираетесь писать рекурсивный запрос. Без
WITH RECURSIVEэто попросту невозможно — об этом подробно в следующем разделе.
А когда CTE — это излишество:
- Один простой подзапрос, который используется в единственном месте.
WHERE id IN (SELECT id FROM ...)и так нормально читается. - Запросы, где важна производительность, и вы уже убедились, что встроенная логика работает быстрее. SQLite обычно меньше других СУБД относится к CTE как к барьеру для оптимизатора, но для горячих путей всё же стоит проверить план через
EXPLAIN QUERY PLAN.
Разбираем пример целиком
Соберём всё вместе — небольшой отчёт, который находит самый крупный заказ каждого клиента и сравнивает его со средним по этому же клиенту:
Два CTE, каждый делает что-то одно. Основной SELECT собирает финальный результат. Запрос читается сверху вниз, и каждый шаг понятен сам по себе — собственно, ради этого CTE и придуманы.
Дальше: рекурсивные CTE
Всё, что было до этого, — это обычные CTE, то есть именованные подзапросы, которые вычисляются один раз. Но в SQLite есть ещё WITH RECURSIVE — когда CTE ссылается сам на себя. Это нужно, чтобы обходить иерархии, генерировать последовательности или ходить по графам. Об этом — на следующей странице.
Часто задаваемые вопросы
Что такое CTE в SQLite?
Common Table Expression (CTE) — это именованный подзапрос, который объявляется в начале SELECT, INSERT, UPDATE или DELETE. Пишете ключевое слово WITH, задаёте имя — и дальше обращаетесь к нему в основном запросе так, будто это обычная таблица. CTE здорово упрощает чтение сложных запросов: результат собирается шаг за шагом, а не одной громоздкой конструкцией.
Чем CTE отличается от подзапроса в SQLite?
Результат может быть один в один — по сути CTE это тот же подзапрос, только вынесенный наверх и с собственным именем. Разница в читаемости и переиспользовании: на CTE можно ссылаться несколько раз внутри одного запроса, а имя сразу говорит, что за промежуточный результат вы получили. Для разового фильтра хватит подзапроса; когда логика идёт в несколько шагов — выигрывает CTE.
Можно ли использовать несколько CTE в одном запросе SQLite?
Да. После первого WITH следующие CTE перечисляются через запятую — повторно WITH писать не нужно. Каждый следующий CTE может ссылаться на предыдущие, так что получается цепочка именованных шагов. Основной SELECT идёт после последнего CTE.
