CTE с WITH

Переписывание вложенного подзапроса в 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 выглядит скромно — запрос и так короткий. Настоящая польза проявится, когда промежуточный результат нужно использовать несколько раз или когда промежуточных шагов становится два и больше.