Что такое общее табличное выражение (CTE) в SQL

Что проверяют

Умеете ли вы писать читаемый SQL и понимаете ли, что CTE делает с планом выполнения. Следом спрашивают про рекурсивный CTE для иерархии и чем CTE отличается от временной таблицы.

Как отвечать

CTE это именованный результат запроса, объявленный через WITH и доступный только в одной следующей за ним инструкции: SELECT, INSERT, UPDATE, DELETE или MERGE. После её выполнения CTE не существует.

Зачем он нужен:

• читаемость: сложный запрос разбивается на шаги с понятными именами вместо вложенных подзапросов;
• повторные ссылки: на CTE можно сослаться несколько раз в основном запросе и из следующего CTE;
• рекурсия: иерархии вроде дерева категорий или оргструктуры. Пишется якорная часть, UNION ALL и рекурсивная часть, ссылающаяся на сам CTE. В SQL Server слово RECURSIVE не нужно, в PostgreSQL обязательно WITH RECURSIVE;
• изменение данных: удаление дублей через CTE с ROW_NUMBER() OVER (PARTITION BY ...) и DELETE FROM cte WHERE rn > 1.

Про производительность стоит сказать честно. В SQL Server CTE не материализуется: оптимизатор подставляет его в запрос, как представление, и две ссылки означают два вычисления. Если промежуточный результат большой и нужен несколько раз, лучше временная таблица #temp: у неё есть статистика и индексы. Рекурсия по умолчанию ограничена 100 уровнями, лимит меняется через OPTION (MAXRECURSION n).

Из C# запрос с CTE обычно выполняют через Dapper или FromSql в EF Core. Дописать к нему LINQ-операторы не выйдет: EF Core достраивает запрос только поверх SQL, который начинается с SELECT, а на тексте с WITH падает с ошибкой о некомпонуемом SQL. Выход: фильтровать внутри самого SQL, вызвать AsEnumerable() и дорабатывать уже в памяти или завернуть CTE в представление.

Где спотыкаются

Говорят, что CTE ускоряет запрос. Сам по себе он ничего не кэширует, это синтаксис, и план обычно совпадает с планом подзапроса.

Забывают точку с запятой перед WITH в T-SQL. Если предыдущая инструкция не закрыта ;, будет синтаксическая ошибка, отсюда привычка писать ;WITH.

Пишут рекурсию без условия остановки или на данных с циклом. Запрос упирается в MAXRECURSION и падает с ошибкой.