Обобщённое табличное выражение, оператор WITH

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

MySQL
-- Пример использования конструкции WITH
WITH Aeroflot_trips AS
    (SELECT TRIP.* FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = "Aeroflot")

SELECT plane, COUNT(plane) AS amount FROM Aeroflot_trips GROUP BY plane;

Выражение с WITH считается «временным», потому что результат не сохраняется где-либо на постоянной основе в схеме базы данных, а действует как временное представление, которое существует только на время выполнения запроса, то есть оно доступно только во время выполнения операторов SELECT, INSERT, UPDATE, DELETE или MERGE. Оно действительно только в том запросе, которому он принадлежит, что позволяет улучшить структуру запроса, не загрязняя глобальное пространство имён.

Синтаксис оператора WITH

MySQL
WITH название_cte [(столбец_1 [, столбец_2 ] …)] AS (подзапрос)
    [, название_cte [(столбец_1 [, столбец_2 ] …)] AS (подзапрос)] …

Порядок использования оператора WITH:

1. Ввести оператор WITH
2. Указать название обобщённого табличного выражения
3. Опционально: определить названия для столбцов получившегося табличного выражения, разделённых знаком запятой
4. Ввести AS и далее подзапрос, результат которого можно будет использовать в других частях SQL запроса, используя имя, определённое на 2 этапе
5. Опционально: если необходимо более одного табличного выражения, то ставится запятая и повторяются шаги 2-4

Примеры запросов

1. Создаём табличное выражение Aeroflot_trips, содержащее все полёты, совершенные авиакомпанией «Aeroflot»

MySQL
WITH Aeroflot_trips AS
    (SELECT plane, town_from, town_to FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = "Aeroflot")

SELECT * FROM Aeroflot_trips;

2. Аналогично, создаём табличное выражение Aeroflot_trips, но с переименованными колонками

MySQL
WITH Aeroflot_trips (aeroflot_plane, town_from, town_to) AS
    (SELECT plane, town_from, town_to FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = "Aeroflot")

SELECT * FROM Aeroflot_trips;

3. С помощью оператора WITH определяем несколько табличных выражений

MySQL
WITH Aeroflot_trips AS
    (SELECT TRIP.* FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = "Aeroflot"),
    Don_avia_trips AS
    (SELECT TRIP.* FROM Company
        INNER JOIN Trip ON Trip.company = Company.id WHERE name = "Don_avia")

SELECT * FROM Don_avia_trips UNION SELECT * FROM  Aeroflot_trips;

Работа с рекурсией в CTE

CTE также могут быть использованы для выполнения рекурсивных запросов, которые позволяют итеративно обрабатывать данные, например, для работы с иерархическими структурами данных, такими как «руководитель — подчинённый».

Синтаксис рекурсивного CTE

Рекурсивное CTE состоит из двух частей, разделенных оператором UNION ALL:

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

MySQL
WITH RECURSIVE название_cte (столбец_1, столбец_2, ...) AS (
    -- Начальный набор данных
    SELECT столбец_1, столбец_2, ...
    FROM таблица
    WHERE условие

    UNION ALL

    -- Рекурсивная часть
    SELECT столбец_1, столбец_2, ...
    FROM название_cte
    INNER JOIN таблица ON название_cte.столбец = таблица.столбец
    WHERE условие
)

SELECT * FROM название_cte;

Пример: иерархия руководителей и подчинённых

Рассмотрим таблицу Employees, которая содержит идентификаторы сотрудников и их руководителей:

Требуется найти всех подчинённых John Smith (id=1) на всех уровнях иерархии.

MySQL
WITH RECURSIVE Subordinates AS (
    -- Начальный набор данных
    SELECT id, name, managerId
    FROM Employees
    WHERE managerId = 1

    UNION ALL

    -- Рекурсивная часть: подчинённые подчинённых
    SELECT e.id, e.name, e.managerId
    FROM Employees e
    INNER JOIN Subordinates s ON e.managerId = s.id
)

SELECT * FROM Subordinates;

Шаги выполнения рекурсивного CTE

1. Начальный набор данных: выбираются все сотрудники, у которых managerId=1 (непосредственные подчинённые John Smith).
2. Рекурсивная часть: для каждого сотрудника, выбранного в начальном наборе данных, выбираются их подчинённые (где managerId равен id выбранного сотрудника).
3. Объединение: результаты начального набора данных и рекурсивной частей объединяются с помощью UNION ALL.
4. Рекурсия: процесс повторяется для каждого нового набора подчинённых, пока не будут выбраны все уровни иерархии.

Заключение

Обобщённые табличные выражения были добавлены в SQL для упрощения сложных длинных запросов, особенно с множественными подзапросами. Их главная задача – улучшение читабельности, простоты написания запросов и их дальнейшей поддержки. Это происходит за счёт сокрытия больших и сложных запросов в созданные именованные выражения, которые потом используются в основном запросе.