Краткий ответ

Теряется возможность обратиться к одному именованному результату из нескольких частей одного SQL-оператора. CTE получает имя в области видимости всего оператора, а производная таблица существует только как один конкретный источник в одном блоке FROM; для повторного использования её приходится объявлять заново.

Это ограничение области видимости, а не гарантия физической материализации. Даже при нескольких ссылках оптимизатор может встроить CTE или выбрать другой план, если это не меняет результат.

Исторический контекст

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

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

Постановка проблемы

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

Если подзапрос скопировать вручную, запрос станет длиннее, а изменения придётся синхронно вносить в несколько мест. Ошибка в одной копии может привести к различным фильтрам, наборам столбцов или правилам агрегации и, как следствие, к противоречивому результату.

Подробное решение

CTE объявляется в начале одного оператора и доступен в последующих частях этого оператора согласно правилам области видимости и порядку зависимостей. Один и тот же CTE можно указать как источник в нескольких подзапросах или соединениях.

Производная таблица — это подзапрос с псевдонимом внутри конкретного FROM. Её имя доступно только окружающему блоку запроса и только в том месте, где объявлен соответствующий источник. В другом блоке или второй позиции FROM тот же результат сам по себе недоступен.

Минимальное сравнение:

WITH filtered_sales AS (
    SELECT customer_id, amount
    FROM sales
    WHERE status = 'paid'
)
SELECT
    (SELECT SUM(amount) FROM filtered_sales) AS total_amount,
    (SELECT COUNT(DISTINCT customer_id) FROM filtered_sales) AS customers;

При использовании производной таблицы такую логику пришлось бы повторить в каждом подзапросе. Это может быть семантически эквивалентно, но увеличивает объём запроса и риск расхождения его частей.

Повторное использование имени CTE не означает, что данные обязательно вычисляются один раз и сохраняются. Стратегия материализации или встраивания определяется СУБД и настройками запроса; она влияет на производительность, но не на область видимости имени. Если требуется рекурсия, обычная производная таблица также не является прямой заменой рекурсивного CTE.

Ситуация из практики

В аналитическом отчёте набор оплаченных продаж использовался для расчёта выручки и одновременно для построения списка клиентов с покупками. Разработчик заменил CTE на производную таблицу, а затем попытался использовать её псевдоним во втором подзапросе. Запрос не прошёл разрешение имён, поскольку псевдоним производной таблицы виден только в своём блоке.

Рассматривались два варианта. Копирование производной таблицы позволяло сохранить локальную структуру, но создавало дублирование и риск расхождения фильтров. Сохранение CTE сохраняло единое логическое определение набора и делало зависимости очевидными; физический план при этом всё равно оставался ответственностью оптимизатора.

Выбрали CTE. Это устранило дублирование, упростило сопровождение и не навязывало предположение о единственной материализации результата. Если профилирование покажет проблему производительности, способ вычисления следует проверять отдельно по плану выполнения.

Что кандидаты часто упускают

Означает ли несколько ссылок на CTE, что СУБД вычислит его ровно один раз?

Нет. Многократное логическое использование имени не фиксирует физический план. Оптимизатор может встроить определение в места использования, материализовать результат или выбрать иной эквивалентный способ выполнения — в зависимости от СУБД, версии, стоимости плана и свойств самого запроса.

Можно ли получить тот же результат, повторив производную таблицу дважды?

Часто да, если копии действительно идентичны и выражения не зависят от порядка, случайности, побочных эффектов или иных специальных свойств конкретной СУБД. Однако повторение увеличивает объём текста и риск того, что одна копия со временем получит другой фильтр, набор столбцов или уровень агрегации.

Можно ли заменить рекурсивный CTE обычной производной таблицей?

Механически — нет. Рекурсивный CTE описывает повторное получение новых строк на основе уже найденных строк, а обычная производная таблица представляет один нерекурсивный запрос. Заменить его можно только другим эквивалентным механизмом, например заранее ограниченным набором итераций или специальными средствами конкретной СУБД, причём семантику обхода и остановки нужно проверять отдельно.