SQL Server OUTER APPLY

В этом руководстве вы узнаете, как использовать предложение SQL Server OUTER APPLY для выполнения левого соединения таблицы с табличной функцией или коррелированным подзапросом.

Введение в предложение SQL Server OUTER APPLY

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

В SQL Server функция с табличным значением — это определяемая пользователем функция, которая возвращает несколько строк в виде таблицы.

Предложение OUTER APPLY работает как предложение LEFT JOIN. Однако вместо объединения двух таблиц предложение OUTER APPLY объединяет таблицу с таблично-значимой функцией или коррелированным подзапросом.

Ниже показан синтаксис предложения OUTER APPLY:

SELECT
  select_list
FROM
  table1
  OUTER APPLY table_function(table1.column) AS alias;

В этом синтаксисе:

• table1 — основная таблица, к которой вы хотите присоединиться.
• table_function — это таблично-значимая функция, применяемая к каждой строке. В качестве альтернативы можно использовать коррелированный подзапрос.
• column — это столбец из table1, который будет передан в качестве параметра в table_function.
• alias — это псевдоним для набора результатов, возвращаемого table_function.

В этом запросе предложение OUTER APPLY применит table_function к каждой строке из table1 или выполнит коррелированный подзапрос для каждой строки из table1.

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

На практике следует использовать предложения OUTER APPLY, когда невозможно использовать предложения LEFT JOIN.

Примеры предложения OUTER APPLY для SQL Server

Давайте рассмотрим несколько примеров использования предложения OUTER APPLY.

Для демонстрации мы будем использовать таблицы production.products и production.order_items из образца базы данных :

1) Использование предложения SQL Server OUTER APPLY для соединения таблицы с коррелированным подзапросом

В следующем примере предложение OUTER APPLY используется для объединения таблицы production.products с коррелированным подзапросом для извлечения названия продукта, количества и скидки для продуктов с идентификатором бренда 1 в последних заказах:

SELECT
  p.product_name,
  r.quantity,
  r.discount
FROM
  production.products p OUTER apply(
    SELECT
      top 1 i.*
    FROM
      sales.order_items i
      INNER JOIN sales.orders o ON o.order_id = i.order_id
    WHERE
      product_id = p.product_id
    ORDER BY
      order_date DESC
  ) r
WHERE
  p.brand_id = 1
ORDER BY
  r.quantity;

Выход:

product_name                                          | quantity | discount
-----------------------------------------------------------------------
Electra Townie Go! 8i Ladies' - 2018                  | NULL     | NULL
Electra Savannah 1(20-inch) - Girl's - 2018          | NULL     | NULL
Electra Sweet Ride 1(20-inch) - Girl's - 2018        | NULL     | NULL
Electra Townie Original 21D - 2018                    | 1        | 0.20
Electra Townie Balloon 7i EQ - 2018                   | 1        | 0.10
Electra Townie Balloon 3i EQ - 2017/2018              | 1        | 0.10
Electra Townie Balloon 8D EQ - 2016/2017/2018         | 1        | 0.07

Как это работает.

Для каждой строки из таблицы production.products OUTER APPLY выполняет следующий коррелированный подзапрос для получения скидки и количества последних заказов:

SELECT
  TOP 1 i.*
FROM
  sales.order_items i
  INNER JOIN sales.orders o ON o.order_id = i.order_id
WHERE
  product_id = p.product_id
ORDER BY
  order_date DESC

2) Использование предложения OUTER APPLY для соединения таблицы с табличной функцией

Сначала определите табличную функцию, которая возвращает последние позиции заказа продукта, указанного по идентификатору продукта:

CREATE FUNCTION GetLatestQuantityDiscount(@product_id INT) 
RETURNS TABLE 
AS RETURN(
  SELECT
    TOP 1 i.*
  FROM
    sales.order_items i
    INNER JOIN sales.orders o ON o.order_id = i.order_id
  WHERE
    product_id = @product_id
  ORDER BY
    order_date DESC
);

Во-вторых, используйте предложение OUTER APPLY с табличной функцией GetLatestQuantityDiscount, чтобы получить последнее количество и скидку для каждого продукта в таблице production.products:

SELECT
  p.product_name,
  r.quantity,
  r.discount
FROM
  production.products p 
OUTER APPLY GetLatestQuantityDiscount(p.product_id) r
WHERE
  p.brand_id = 1
ORDER BY
  r.quantity;

Он возвращает тот же результат, что и запрос, который объединяется с коррелированным подзапросом выше.

Краткое содержание

• Используйте оператор OUTER APPLY для выполнения левого соединения таблицы с табличной функцией или коррелированным подзапросом.