[SQL] APPLY

Apply 從 SQL Server 2005 有，通常搭配資料表值函數（Table-Valued Function；TVF），而 Apply 有兩種模式：

1. CROSS APLLY：搭配 TVF 可以產生類似 JOIN 效果
2. OUTER APPLY：搭配 TVF 可以產生類似 LEFT JOIN 效果

APPLY 搭配 TVF 使用

下面利用找尋每個客戶最近的兩筆交易當成範例來說明

USE [AdventureWorks2012]

CREATE FUNCTION dbo.getTopOrder (@custid AS int , @topn AS bigint)
RETURNS TABLE
AS
RETURN 
  (
    SELECT 
      TOP (@topn) 
      SalesOrderID,
      OrderDate 
    FROM [Sales].[SalesOrderHeader]
    WHERE CustomerID = @custid
    ORDER BY OrderDate DESC
 )
GO 

下述的 T-SQL 語法，會把 CustomerID 當成變數傳進 dbo.getTopOrder() 中，搭配 CROSS APLLY、OUTER APPLY 來查詢結果

SELECT 
  C.CustomerID ,
  T.OrderDate ,
  T.SalesOrderID 
FROM [Sales].[Customer] AS C
  CROSS APPLY dbo.getTopOrder(C.CustomerID,2) AS T 
  -- OUTER APPLY dbo.getTopOrder(C.CustomerID,2) AS T 
ORDER BY CustomerID

下面兩張結果圖，可以觀察到，OUTER APPLY 即使該客戶沒有交易紀錄，仍然會把該客戶列出來，但假如是 CROSS JOIN 的話，這些沒有交易紀錄的客戶，就不會顯示出來

• OUTER APPLY 結果圖

• CROSS APPLY 結果圖

• 執行計畫

從執行計畫中，可以觀察到 CROSS APPLY 和 OUTER APPLY 都是把 Customers.CustomerID 一筆一筆（巢狀迴圈 Nested Loop）丟進 SalesOrderHeader，去找尋該客戶最近的前兩筆資料

直接使用 APPLY

下面 T-SQL 能得到跟 TVF 搭配 CROSS APPLY 一樣的結果

SELECT 
  C.CustomerID ,
  T.OrderDate ,
  T.SalesOrderID 
FROM [Sales].[Customer] AS C
  CROSS APPLY 
    (
        SELECT 
          TOP (2) 
          SalesOrderID,
          OrderDate 
        FROM [Sales].[SalesOrderHeader] AS H
        WHERE H.CustomerID = C.CustomerID
        ORDER BY OrderDate DESC
    ) AS T

延伸閱讀
• 使用者自訂函數

參考資料
• 論壇問題出處 1、2
• Apply