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

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

MySQL 8.1
-- Пример использования конструкции 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

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

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

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

MySQL 8.1
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 8.1
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 8.1
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, чтобы продолжить рекурсию.

WITH RECURSIVE название_cte [(столбец [, ...])] AS (
    -- Начальный набор данных
    SELECT выражение [, ...]
    FROM имя_таблицы
    [WHERE условие]

    UNION ALL

    -- Рекурсивная часть
    SELECT выражение [, ...]
    FROM название_cte
    INNER JOIN имя_таблицы ON условие_соединения
    [WHERE условие]
)

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

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

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

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

MySQL 8.1
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 для упрощения сложных длинных запросов, особенно с множественными подзапросами. Их главная задача – улучшение читабельности, простоты написания запросов и их дальнейшей поддержки. Это происходит за счёт сокрытия больших и сложных запросов в созданные именованные выражения, которые потом используются в основном запросе.