SQL CTE за 5 минут: руководство по Common Table Expressions

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

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

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

WITH имя_cte [(имя_столбца1, имя_столбца2, ...)] AS (
    подзапрос
)

WITH: ключевое слово, обозначающее начало CTE.
имя_cte: имя, присваиваемое табличному выражению.
(имя_столбца1, имя_столбца2, …): (опционально) список имён столбцов для CTE, разделённых запятыми. Позволяет присвоить кастомные имена столбцам.
AS: ключевое слово, отделяющее имя CTE от подзапроса.
подзапрос: SQL-запрос, определяющий данные для CTE.

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

WITH cte1 AS (подзапрос1), cte2 AS (подзапрос2)

Примеры использования CTE

Примеры использования простой модели базы данных: таблиц Trips (рейсы), PassengerInTrip (пассажиры в рейсе), Passenger (пассажиры) и Company (авиакомпании).

Простой CTE

CTE aeroflot_trips, содержащий информацию о рейсах авиакомпании «Aeroflot»:

WITH aeroflot_trips AS (
    SELECT Plane, TownFrom, TownTo
    FROM Company
    INNER JOIN Trip ON Trip.CompanyID = Company.CompanyID
    WHERE Name = 'Aeroflot'
)
SELECT * FROM aeroflot_trips;

Запрос объединяет таблицы Company и Trip, фильтрует данные по авиакомпании «Aeroflot» и сохраняет результат в aeroflot_trips. Затем SELECT * FROM aeroflot_trips извлекает данные из CTE.

CTE с переименованными столбцами

Переименование столбцов в CTE:

WITH aeroflot_trips (AeroflotPlane, TFrom, TTo) AS (
    SELECT Plane, TownFrom, TownTo
    FROM Company
    INNER JOIN Trip ON Trip.CompanyID = Company.CompanyID
    WHERE Name = 'Aeroflot'
)
SELECT * FROM aeroflot_trips;

Столбцы Plane, TownFrom и TownTo переименованы в AeroflotPlane, TFrom и TTo.

Несколько CTE

Определение нескольких CTE в одном запросе:

WITH aeroflot_trips AS (
    SELECT *
    FROM Company
    INNER JOIN Trip ON Trip.CompanyID = Company.CompanyID
    WHERE Name = 'Aeroflot'
),
don_avia_trips AS (
    SELECT *
    FROM Company
    INNER JOIN Trip ON Trip.CompanyID = Company.CompanyID
    WHERE Name = 'Don Avia'
)
SELECT * FROM don_avia_trips
UNION
SELECT * FROM aeroflot_trips;

Два CTE: aeroflot_trips и don_avia_trips, содержащие рейсы «Aeroflot» и «Don Avia». Оператор UNION объединяет результаты.

Обобщённые табличные выражения (CTE) упрощают написание и поддержку сложных SQL-запросов, особенно с множественными подзапросами. Они улучшают читаемость кода и позволяют разбить сложные запросы на более мелкие, понятные части. Главное преимущество — повышение читаемости и упрощение написания и поддержки запросов.