Оператор CROSS APPLY в T-SQL

Всем привет! Сегодня мы поговорим о достаточно полезном операторе CROSS APPLY языка T-SQL, Вы узнаете, что это за оператор и как его использовать на практике. В статье рассмотрено несколько SQL запросов с применением оператора CROSS APPLY, а также OUTER APPLY.

CROSS APPLY в T-SQL

CROSS APPLY – это тип оператора APPLY, который позволяет вызывать табличную функцию для каждой строки внешнего табличного выражения. Вместо табличной функции можно также использовать и вложенный запрос, который возвращает производную таблицу.

Есть еще и другой тип OUTER APPLY, он в отличие от CROSS APPLY возвращает и строки, которые формируют результирующий набор, и те, которые этого не делают, т.е. со значениями NULL в столбцах. Например, табличная функция может не возвращать никаких данных для определенных значений, CROSS APPLY в таких случаях подобные строки не выводит, а OUTER APPLY выводит.

Примеры использования CROSS APPLY в T-SQL

Давайте рассмотрим несколько примеров использования операторов CROSS APPLY и OUTER APPLY в языке T-SQL.

Заметка! Все примеры в данной статье рассмотрены в Microsoft SQL Server 2019 Express.

Исходные данные для примеров

Для начала давайте определимся с исходными данными, которые мы будем использовать в примерах, допустим, у нас есть таблица товаров (Goods) и таблица продаж (Sales).

SQL инструкция ниже создаёт эти таблицы и наполняет их тестовыми данными.

   
   --Таблица товаров
   CREATE TABLE Goods (
     ProductId INT IDENTITY(1,1) NOT NULL,
     ProductName VARCHAR(100) NOT NULL
   );

   INSERT INTO Goods(ProductName)
     VALUES ('Системный блок'),
            ('Принтер'),
            ('Монитор'),
            ('Клавиатура');

   --Таблица продаж
   CREATE TABLE Sales (
     SaleId INT IDENTITY(1,1) NOT NULL,
     ProductId INT NOT NULL,
     SaleDate DATETIME NOT NULL
   );

   INSERT INTO Sales (ProductId, SaleDate)
     VALUES (1, '01.02.2020'),
            (1, '10.03.2020'),
            (1, '25.04.2020'),
            (1, '15.05.2020'),
            (2, '25.02.2020'),
            (2, '15.06.2020'),
            (2, '01.07.2020'),
            (3, '01.04.2020'),
            (3, '05.05.2020');
   
   SELECT * FROM Goods;
   SELECT * FROM Sales;

А также у нас есть табличная функция SaleGoods, которая просто выводит список продаж по идентификатору товара.

   
   CREATE FUNCTION SaleGoods
     (
       @ProductID INT
     )
   RETURNS TABLE
   AS
   RETURN
    (
     SELECT S.SaleId, S.SaleDate, G.ProductName
     FROM Sales S
     INNER JOIN Goods G ON S.ProductID = G.ProductID
     WHERE S.ProductID = @ProductID
    );
   
   GO

   SELECT * FROM SaleGoods (1);

Заметка! Если Вы не знаете, что делает вышеуказанная инструкция, рекомендую посмотреть мой видеокурс «T-SQL. Путь программиста от новичка к профессионалу. Уровень 1 – Новичок», который предназначен для начинающих и в нем подробно рассмотрены все базовые конструкции языка SQL, включая все вышеперечисленные.

Пример использования CROSS APPLY с табличной функцией

Теперь допустим, нам необходимо получить продажи не только одного товара, но и других, для этого мы можем использовать оператор CROSS APPLY.

   
   SELECT G.ProductName, Sales.SaleDate
   FROM Goods G
   CROSS APPLY SaleGoods (G.ProductId) AS Sales;

Где, как Вы понимаете, табличная функция SaleGoods была вызвана для каждой строки таблицы Goods.

Пример использования CROSS APPLY с подзапросом

Как уже было сказано выше, CROSS APPLY можно использовать и с подзапросом, для примера давайте представим, что нам нужно получить последнюю дату продажи каждого товара.

   
   SELECT G.ProductName, Sales.SaleDate
   FROM Goods G
   CROSS APPLY  (
                 SELECT TOP 1 S.SaleDate
                 FROM SaleGoods(G.ProductId) AS S
                 ORDER BY S.SaleDate DESC
                ) AS Sales;

Как видим, после CROSS APPLY у нас идет вложенный запрос, формирующий производную таблицу.

Заметка! ТОП 20 статей для изучения языка T-SQL – Уровень «Продвинутый».

Пример использования OUTER APPLY

Если Вы заметили, у нас в исходных данных есть позиция «Клавиатура», по которой не было продаж. И в случае возникновения необходимости получить последнюю дату продажи, включая товары, по которым не было продаж, т.е. выводить NULL значения, чтобы мы видели, какие товары не продавались вообще, оператор CROSS APPLY можно заменить на OUTER APPLY.

   
   SELECT G.ProductName, Sales.SaleDate
   FROM Goods G
   OUTER APPLY  (
                 SELECT TOP 1 S.SaleDate
                 FROM SaleGoods(G.ProductId) AS S
                 ORDER BY S.SaleDate DESC
                 ) AS Sales;

Теперь видно, что некоторые товары продавались и последняя дата их продажи такая-то, а некоторые товары не продавались, т.е. у них отсутствует дата продажи.

Интересные материалы по теме:

• SQL код – самоучитель по SQL для начинающих программистов
• Как выполнить код Python в Microsoft SQL Server на T-SQL
• Объект SEQUENCE (последовательность) в Microsoft SQL Server
• Что такое DDL, DML, DCL и TCL в языке SQL

На сегодня это все, удачи Вам, пока!