Общие табличные выражения (Common Table Expressions, CTE) в SQL представляют собой временные результирующие наборы, определяемые в пределах одного запроса. Они позволяют упростить чтение и управление сложными SQL-запросами, особенно при работе с рекурсивными запросами и многократном использовании одних и тех же подзапросов.
Синтаксис CTE
CTE определяется с использованием ключевого слова WITH, за которым следует имя CTE, определение набора данных и основной SQL-запрос, использующий этот временный набор.
WITH cte_example AS (
SELECT id, name, salary
FROM employees
WHERE salary > 50000
)
SELECT * FROM cte_example;Преимущества использования CTE
- Упрощение кода: CTE делает SQL-запросы более читаемыми и понятными.
- Повторное использование данных: Позволяет обращаться к временным таблицам несколько раз в одном запросе.
- Улучшенная производительность: В некоторых случаях CTE может работать быстрее, чем вложенные подзапросы.
- Рекурсивные запросы: Позволяет эффективно работать со связанными данными, например, для обработки иерархических структур.
Рекурсивные CTE
Рекурсивные CTE применяются для работы с древовидными структурами, такими как организационные структуры или каталоги файлов. Они используют рекурсивный вызов внутри определения CTE.
WITH RECURSIVE employee_hierarchy AS (
SELECT id, name, manager_id
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;Этот запрос строит иерархию сотрудников, начиная с руководителей (manager_id IS NULL) и рекурсивно добавляя подчиненных.
Использование CTE для обновления данных
CTE может использоваться не только для выборки, но и для обновления данных в таблице.
WITH updated_salaries AS (
SELECT id, salary * 1.1 AS new_salary FROM employees
)
UPDATE employees
SET salary = (SELECT new_salary FROM updated_salaries WHERE employees.id = updated_salaries.id);