Что такое оператор WITH (CTE) в SQL и каковы его преимущества?

Оператор WITH, также известный как CTE (Common Table Expression), — это конструкция в SQL, которая позволяет создать временный, именованный набор результатов запроса. Этот временный набор существует только в рамках выполнения одного основного запроса (SELECT, INSERT, UPDATE или DELETE).

Синтаксис:

WITH CteName AS (
    -- Запрос, формирующий временный набор данных
    SELECT column1, column2 FROM some_table WHERE condition
)
-- Основной запрос, использующий CTE
SELECT * FROM CteName WHERE ...;

Ключевые преимущества использования CTE:

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

2. Многократное использование: На один и тот же CTE можно ссылаться несколько раз в основном запросе, избегая дублирования кода.

   WITH regional_sales AS (
       SELECT region, SUM(amount) AS total_sales
       FROM orders
       GROUP BY region
   )
   SELECT region, total_sales
   FROM regional_sales
   WHERE total_sales > (SELECT AVG(total_sales) FROM regional_sales);

3. Рекурсивные запросы: CTE — это стандартный способ выполнения рекурсивных операций в SQL, что незаменимо для работы с иерархическими данными (например, деревья категорий, организационные структуры).

   WITH RECURSIVE employee_hierarchy (id, name, manager_id, level) AS (
       -- Начальная часть рекурсии (якорь)
       SELECT id, name, manager_id, 0 FROM employees WHERE manager_id IS NULL
       UNION ALL
       -- Рекурсивная часть
       SELECT e.id, e.name, e.manager_id, eh.level + 1
       FROM employees e
       JOIN employee_hierarchy eh ON e.manager_id = eh.id
   )
   SELECT * FROM employee_hierarchy;