Занятие 26. Табличные выражения

📁 Блок: SQL / MySQL / Подзапросы и CTE ⏱️ Время изучения: ~75 мин 🎯 Сложность: Средняя

#as #select #from

⚡ Кратко: суть темы

CTE — именованный временный результат SELECT, объявленный через WITH. Доступен только внутри текущего запроса и помогает сделать код читаемее.

Синтаксис: WITH имя AS (SELECT ...) SELECT ... FROM имя;
Подходит для сложных запросов и многократного использования одной логики.
Не хранит данные между запросами — это не временная таблица.

📖 Что такое CTE

Common Table Expression (CTE) — это конструкция SQL, которая позволяет создать временный набор данных и обратиться к нему по имени в основном запросе. CTE объявляется с помощью ключевого слова WITH и действует как виртуальная таблица, доступная только в рамках одного SQL-запроса.

CTE часто используют, чтобы заменить вложенные подзапросы и сделать запросы более понятными.

📖 Синтаксис CTE

WITH cte_name AS (
    -- Подзапрос
    SELECT column1, column2, ...
    FROM table_name
    WHERE conditions
)
SELECT column1, column2, ...
FROM cte_name
WHERE conditions;

WITH — ключевое слово, открывающее блок CTE.
cte_name — имя, по которому можно обращаться к результату.
AS (SELECT ...) — запрос, формирующий набор строк.
После объявления CTE идёт основной SELECT, который использует CTE как обычную таблицу.

📖 Повторное использование CTE

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

WITH avg_price AS (
    SELECT AVG(unit_price) AS ap
    FROM order_details
)
SELECT *
FROM order_details
WHERE unit_price > (SELECT ap FROM avg_price)
  AND unit_price < (SELECT ap * 1.5 FROM avg_price);

В этом запросе среднее значение вычисляется один раз, но используется дважды — для нижней и верхней границы диапазона.

📖 CTE vs подзапросы

CTE и подзапросы решают схожие задачи, но подходят для разных ситуаций.

Когда использовать CTE

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

Когда использовать подзапросы

Запрос простой и подзапрос логически вписывается в структуру.
Важна производительность и известно, что конкретный SQL-движок лучше оптимизирует подзапросы.

Проверить по документации: в современных версиях MySQL и PostgreSQL оптимизатор часто преобразует нерекурсивный CTE и эквивалентный подзапрос в одинаковый план выполнения. Конкретное поведение зависит от версии СУБД и наличия индексов.

📖 Плюсы и минусы CTE

Плюсы

Читаемость — сложный запрос разбивается на именованные шаги.
Модульность — логику можно переиспользовать внутри одного запроса.
Лёгкость использования — не требуется явно создавать и удалять временную таблицу.

Минусы

Ограниченная область действия — CTE доступен только в рамках одного запроса и не сохраняется между выполнениями.
В некоторых старых версиях СУБД поддержка CTE может быть ограничена или отсутствовать.