Обобщённое табличное выражение, оператор 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;
planetown_fromtown_to
IL-86MoscowRostov
IL-86RostovMoscow
  1. Аналогично, создаём табличное выражение 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;
aeroflot_planetown_fromtown_to
IL-86MoscowRostov
IL-86RostovMoscow
  1. С помощью оператора 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;
idcompanyplanetown_fromtown_totime_outtime_in
11811TU-134RostovMoscow1900-01-01T06:12:00.000Z1900-01-01T08:01:00.000Z
11821TU-134MoscowRostov1900-01-01T12:35:00.000Z1900-01-01T14:30:00.000Z
11871TU-134RostovMoscow1900-01-01T15:42:00.000Z1900-01-01T17:39:00.000Z
11881TU-134MoscowRostov1900-01-01T22:50:00.000Z1900-01-02T00:48:00.000Z
11951TU-154RostovMoscow1900-01-01T23:30:00.000Z1900-01-02T01:11:00.000Z
11961TU-154MoscowRostov1900-01-01T04:00:00.000Z1900-01-01T05:45:00.000Z
11452IL-86MoscowRostov1900-01-01T09:35:00.000Z1900-01-01T11:23:00.000Z
11462IL-86RostovMoscow1900-01-01T17:55:00.000Z1900-01-01T20:01:00.000Z

Заключение

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