Использование нескольких CTE в SQL: синтаксис SELECT

Быстрый ответ

В SQL вы можете создавать несколько общих табличных выражений (ОТВ, или CTE) в рамках одного и того же запроса, отделяя их между собой запятыми. Сложные подзапросы в ОТВ действуют как присоединённые таблицы, что позволяет структурировать запрос. Рассмотрим пример:

WITH
CTE1 AS (
    SELECT * FROM Employees
),
CTE2 AS (
    SELECT * FROM Departments
)
SELECT * FROM CTE1 JOIN CTE2 ON CTE1.DeptID = CTE2.ID;

Отметим, что каждое CTE определяется один раз и может использоваться несколько раз внутри основного запроса. Это делает SQL более структурированным и элегантным при выборе данных.

Создание и использование нескольких ОТВ

Порядок объявления ОТВ имеет значение. Если CTE2 использует данные из CTE1, то CTE1 должен быть объявлен первым:

WITH
CTE1 AS (
    SELECT * FROM Employees WHERE Status = 'Active'
),
CTE2 AS (
    SELECT E.ManagerID, COUNT(*) AS NumOfSubordinates
    FROM CTE1 E
    GROUP BY E.ManagerID
)
SELECT * FROM CTE2;

Представьте это как последовательное построение: данные из предыдущего ОТВ влияют на следующие операции.

Устранение ошибок в запросах с ОТВ

Следует помнить правила синтаксиса: отдельные ОТВ разделяются запятыми, а ключевое слово WITH ставится только в начале запроса. Каждое ОТВ — это отдельная сущность со своим назначением в запросе.

Ошибка с сообщением 'Incorrect syntax near 'WITH'' зачастую свидетельствует о неправильном порядке или разграничении ОТВ. Также в конце оператора с ОТВ требуется точка с запятой.

Визуализация

Можно представить создание SQL-запроса с несколькими ОТВ как подготовку сэндвича, где каждый ОТВ — это отдельный слой, который добавляется последовательно:

🥪 ВЕРХНИЙ СЛОЙ – CTE3: Выбор сыра.
|
🥬 СРЕДНИЙ СЛОЙ – CTE2: Выбор салата.
|
🍞 НИЖНИЙ СЛОЙ – CTE1: Выбор хлеба.

Составление запроса происходит в последовательном порядке, от нижнего ОТВ к верхнему:

WITH
Grain AS (SELECT * FROM BreadBox),
Veggie AS (SELECT * FROM LettuceBox),
Dairy AS (SELECT * FROM CheeseBox)
SELECT * FROM Dairy
JOIN Veggie ON Veggie.ID = Dairy.VeggieID
JOIN Grain ON Grain.ID = Veggie.BreadID;

Каждый ОТВ (или слой) подбирается отдельно, а затем все слои объединяются в один запрос.

Улучшение читабельности и производительности

ОТВ делают запрос более аккуратным и эффективным. Заслугу в этом имеют хорошие практики форматирования и ясное именование, которые способствуют улучшению читаемости кода.

При использовании нескольких ОТВ важно следить за тем, чтобы SQL-запрос оставался читабельным и легко изменяемым. SQL Server рассматривает каждое ОТВ как временную таблицу, и их чрезмерное использование может снизить производительность.

Рассмотрение рекурсивных ОТВ

ОТВ позволяют проводить рекурсивные запросы, например, для построения иерархической структуры организации:

WITH RecursiveCTE AS (
    SELECT ID, ParentID, Name, 0 AS Level
    FROM Category
    WHERE ParentID IS NULL

    UNION ALL

    SELECT c.ID, c.ParentID, c.Name, p.Level + 1
    FROM Category c
    INNER JOIN RecursiveCTE p ON c.ParentID = p.ID
)
SELECT * FROM RecursiveCTE ORDER BY Level, Name;

Полезные материалы

1. WITH common_table_expression (Transact-SQL) – SQL Server | Microsoft Learn — Официальная документация Microsoft о ОТВ.
2. Visual Representation of SQL Joins – CodeProject — Наглядное пособие по соединениям в SQL.
3. SQL Server Common Table Expression (CTE) Basics – Simple Talk — Руководство по основам работы с ОТВ.
4. Recursive Queries using Common Table Expressions (CTE) in SQL Server – MSSQLTips — Глубокое изучение рекурсивных ОТВ.
5. Multiple CTEs in a single SQL Server query – Stack Overflow — Обсуждение примеров использования нескольких ОТВ на Stack Overflow.