Представьте: вы пишете сложный отчет по продажам. Нужно соединить пять таблиц, отфильтровать данные по дате, посчитать метрики и вывести итог. Запрос получается громоздким, с тремя уровнями вложенных подзапросов. Через месяц вы возвращаетесь к коду и тратите полчаса, чтобы понять, что вообще происходит.
Именно для таких случаев в SQL существует CTE (Common Table Expression) — общее табличное выражение, которое задается через оператор WITH. Оно помогает разбивать сложную логику на именованные блоки, делать код читаемым и упрощать отладку.
В этой статье разберем, как работает WITH AS SQL, покажем практические примеры и расскажем, когда стоит использовать общие табличные выражения, а когда лучше выбрать другое решение.
Что такое CTE и зачем он нужен
CTE в SQL — это особый синтаксический конструкт, то есть именованный подзапрос, который определяется через оператор WITH и может использоваться в основном запросе как обычная таблица. Важно: CTE — это часть кода, а не готовый результат; база данных интерпретирует этот блок при выполнении запроса.
Без CTE вы не сможете:
- сделать код сложного отчета читаемым для коллег;
- избежать дублирования одинаковых подзапросов;
- реализовать обход иерархических данных (например, оргструктуры или категорий товаров).
Важно: если ваш запрос занимает больше 20 строк и содержит вложенные SELECT — самое время задуматься о CTE.
Базовый синтаксис: как работает WITH AS SQL
Самая частая форма записи выглядит так:
WITH active_users AS ( SELECT id, name, email FROM users WHERE status = 'active')SELECT au.name, COUNT(o.id) AS order_countFROM active_users auLEFT JOIN orders o ON o.user_id = au.idGROUP BY au.id, au.name;
- WITH — ключевое слово, указывается один раз в начале;
- active_users — имя CTE (выбирайте описательные названия!);
- AS (…) — запрос, который формирует временный набор данных;
- после закрывающей скобки — основной запрос, который использует CTE как обычную таблицу.
Важно: несколько CTE разделяются запятыми, а не новыми WITH:
WITH ru_locations AS ( SELECT id FROM locations WHERE country = 'RU' ), ru_departments AS ( SELECT id FROM departments WHERE location_id IN (SELECT id FROM ru_locations) )SELECT name, salaryFROM employeesWHERE department_id IN (SELECT id FROM ru_departments);
Чем CTE отличается от подзапроса и временных таблиц
| Конструкция | Область видимости | Повторное использование | Сохранение в БД |
| Вложенный запрос SQL | Только в месте объявления | Нет | Нет |
| CTE (общие табличные выражения SQL) | Весь основной запрос | Да, многократно | Нет |
| Временные таблицы SQL | Сессия/транзакция | Да | Да (физически, временно) |
| Представление (VIEW) | Вся база данных | Да, в любых запросах | Да (виртуально) |
Используйте оператор WITH в SQL, когда один и тот же набор данных нужен в нескольких местах запроса. Так вы не продублируете код и упростите отладку.
Когда стоит использовать оператор WITH в SQL
Улучшение читаемости сложных запросов
Читаемость — приоритет при написании SQL-кода. CTE в SQL запросе позволяют понятно обозначить каждый логический шаг:
WITH -- Шаг 1: отбираем активные заказы active_orders AS ( SELECT * FROM orders WHERE status = 'active' ), -- Шаг 2: считаем сумму по каждому клиенту client_totals AS ( SELECT client_id, SUM(amount) AS total FROM active_orders GROUP BY client_id )-- Шаг 3: финальная выборкаSELECT c.name, ct.totalFROM clients cJOIN client_totals ct ON ct.client_id = c.idWHERE ct.total > 10000;
Повторное использование данных: несколько CTE SQL
Если один и тот же подзапрос используется больше двух раз, то CTE может быть материализован один раз — в отличие от вложенного SELECT WITH, который может выполняться многократно.
Оптимизация: когда CTE может замедлить запрос
- В больших наборах данных. Если CTE возвращает миллионы строк, но используется один раз, подзапрос может быть эффективнее.
- При отсутствии индексов. CTE не наследует индексы базовых таблиц.
- При рекурсии без ограничений. В этом случае всегда добавляйте условие выхода.
Практические примеры: от простого к сложному
Пример 1: упрощение вложенных подзапросов
До (сложно читать):
SELECT product_name, priceFROM productsWHERE supplier_id IN ( SELECT id FROM suppliers WHERE region_id IN ( SELECT id FROM regions WHERE country_code = 'RU' ));
После (с CTE WITH SQL):
WITH ru_regions AS ( SELECT id FROM regions WHERE country_code = 'RU'),ru_suppliers AS ( SELECT id FROM suppliers WHERE region_id IN (SELECT id FROM ru_regions))SELECT product_name, priceFROM productsWHERE supplier_id IN (SELECT id FROM ru_suppliers);
Пример 2: агрегация с несколькими CTE
WITH -- Завершенные курсы по неделям weekly_completions AS ( SELECT DATE_TRUNC('week', completed_at) AS week, COUNT(*) AS completions FROM course_progress WHERE status = 'completed' GROUP BY 1 ), -- Новые регистрации по неделям weekly_enrollments AS ( SELECT DATE_TRUNC('week', enrolled_at) AS week, COUNT(*) AS enrollments FROM enrollments GROUP BY 1 )-- Финальный отчет: конверсия в завершениеSELECT wc.week, wc.completions, we.enrollments, ROUND((wc.completions * 100.0 / NULLIF(we.enrollments, 0)), 2) AS completion_rate_pctFROM weekly_completions wcJOIN weekly_enrollments we ON we.week = wc.weekORDER BY wc.week;
Лучшие практики: как писать CTE правильно
- Называйте понятно: active_users, а не t1.
- Комментируйте сложные блоки: если речь идет о рекурсивных запросах, это особенно важно.
- Форматируйте код: следите за отступами, переносами, выравниванием — это экономит время при отладке.
- Тестируйте по частям: запускайте каждый CTE отдельно перед сборкой полного запроса.
-- Хорошо WITH -- Пользователи с неподтвержденным email verified_users AS ( SELECT id, name FROM users WHERE email_confirmed = true )SELECT * FROM verified_users; -- Плохо WITH t1 AS (SELECT id,name FROM users WHERE email_confirmed=true) SELECT*FROM t1;
Типичные ошибки и как их избежать
CTE в SQL: коротко о главном
- Оператор WITH в SQL — основной способ создать именованный временный набор данных.
- CTE улучшает читаемость, то есть разбивает сложную логику на понятные блоки.
- Не панацея: для очень больших данных или многократного использования между запросами лучше временные таблицы или материализованные представления.
- Пишите для людей: читаемость и здравый смысл важнее механического вхождения ключей.
Освоив общие табличные выражения, вы сможете писать чистые, поддерживаемые запросы — от простых отчетов до сложных аналитических пайплайнов.
