Можешь объяснить, что такое Common Table Expression (CTE), и описать практические сценарии, где ты бы использовал CTE в SQL?

Что такое CTE?

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

Ключевые характеристики

• Определяется один раз, но на него можно ссылаться несколько раз в рамках одного запроса
• Не сохраняется в базе данных — это исключительно временная структура, существующая в памяти
• Улучшает читаемость, заменяя глубоко вложенные подзапросы именованными логическими шагами
• Поддерживает рекурсивные запросы через ключевое слово RECURSIVE (или нативно в некоторых базах данных)

Типичные сценарии использования

• Разбивка сложной логики — раздели многошаговое преобразование на чётко именованные этапы вместо нагромождения вложенных подзапросов
• Устранение дублирования — определи подзапрос один раз и переиспользуй его, вместо того чтобы повторять одну и ту же логику в разных местах
• Рекурсивный обход — обходи иерархические данные, такие как организационные диаграммы, структуры папок или спецификации материалов
• Упрощение отладки — изолируй отдельные CTE-блоки, чтобы тестировать и проверять промежуточные результаты независимо

Пример

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

Это делает намерение понятным, логику переиспользуемой и запрос простым в сопровождении.