Операции работы с CTE (Common Table Expressions), простые и коррелированные подзапросы, рекурсия

Common Table Expressions (CTE), простые и коррелированные подзапросы, а также рекурсия — это важные элементы SQL, которые помогают в создании сложных запросов и упрощении их структуры. Давайте рассмотрим каждую из этих концепций подробно.

Common Table Expressions (CTE)

CTE — это временные результаты запроса, которые можно использовать в основном запросе. CTE определяется с помощью ключевого слова WITH и может улучшить читаемость и организацию запросов.

Простой CTE

Простой CTE создается с помощью WITH и может использоваться для упрощения запросов.

Пример:

WITH EmployeeCTE AS (
    SELECT employee_id, name, salary
    FROM employees
    WHERE salary > 50000
)
SELECT *
FROM EmployeeCTE;

В этом примере CTE EmployeeCTE содержит сотрудников с зарплатой более 50,000. Основной запрос выбирает все строки из EmployeeCTE.

Множественные CTE

Можно использовать несколько CTE в одном запросе, разделяя их запятыми.

Пример:

WITH HighSalaryCTE AS (
    SELECT employee_id, name, salary
    FROM employees
    WHERE salary > 50000
),
LowSalaryCTE AS (
    SELECT employee_id, name, salary
    FROM employees
    WHERE salary <= 50000
)
SELECT *
FROM HighSalaryCTE
UNION ALL
SELECT *
FROM LowSalaryCTE;

Этот запрос создает два CTE: HighSalaryCTE и LowSalaryCTE, которые затем объединяются в основном запросе.

Подзапросы

Подзапросы — это запросы, вложенные внутри других запросов. Они могут быть простыми или коррелированными.

Простой подзапрос

Простой подзапрос не зависит от внешнего запроса и выполняется отдельно.

Пример:

SELECT employee_id, name, salary
FROM employees
WHERE department_id = (
    SELECT department_id
    FROM departments
    WHERE department_name = 'Sales'
);

В этом запросе внутренний подзапрос выбирает department_id для отдела "Sales", который затем используется во внешнем запросе для выбора сотрудников из этого отдела.

Коррелированный подзапрос

Коррелированный подзапрос зависит от внешнего запроса и выполняется для каждой строки внешнего запроса.

Пример:

SELECT employee_id, name, salary
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id
);

Здесь внутренний подзапрос вычисляет среднюю зарплату для каждого department_id, и внешний запрос выбирает сотрудников, чья зарплата выше этой средней зарплаты.

Рекурсивные CTE

Рекурсивные CTE позволяют выполнять рекурсивные запросы, что полезно для работы с иерархическими или графовыми данными.

Рекурсивный CTE состоит из двух частей:

• Anchor Member (якорная часть) — начальная часть рекурсии.

• Recursive Member (рекурсивная часть) — часть, которая выполняется рекурсивно.

Пример:

Предположим, у нас есть таблица employees с полями employee_id, name, и manager_id, где manager_id указывает на employee_id руководителя.

WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor Member: выбираем начальников
    SELECT employee_id, name, manager_id
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive Member: выбираем подчиненных
    SELECT e.employee_id, e.name, e.manager_id
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT *
FROM EmployeeHierarchy;

Этот запрос возвращает иерархическую структуру сотрудников, начиная с тех, кто не имеет менеджера (т.е. топ-менеджеров), и рекурсивно добавляет их подчиненных.

Заключение

• CTE (Common Table Expressions) упрощают структуру сложных запросов и улучшают их читаемость, позволяя повторно использовать временные результаты.

• Простые подзапросы позволяют выполнять запросы внутри других запросов и возвращают результат, который используется во внешнем запросе.

• Коррелированные подзапросы зависят от внешнего запроса и выполняются для каждой строки внешнего запроса.

• Рекурсивные CTE позволяют работать с иерархическими данными, выполняя рекурсивные запросы.

Эти инструменты помогают создавать мощные и гибкие SQL-запросы для сложного анализа данных и обработки иерархий.