SQL - 為何使用 CROSS APPLY

 其實我本人一直都是慣用標準的 SQL 語法，

因為在跨DB時的相容性高；

但難免需要改到其他人寫的 SQL ，

一直以來對於 CROSS APPLY 都很懷疑，

為何要寫 CROSS APPLY ？

今天花了點時間查證一下，

第一：

基本上 CROSS APPLY, OUTER APPLY 相等於 JOIN, LEFT JOIN，

但在需要 JOIN RETURN TABLE 的 UDF 時，

如果需要傳入其他 TABLE 的欄位當條件時就只能用 APPLY。

參考資料【理解 SQL Server 的 CROSS APPLY 和 OUTER APPLY 査詢 - 第 2 部分】

SELECT E.Id, E.Name, E.CountryName, TD.Name, E.CountryName

           FROM EMPLOYEE AS E

    CROSS APPLY GetDept(E.CountryName) TD

第二：

如果 JOIN 有複雜的條件的時候，

CROSS APPLY 的表現會比 JOIN 好，

參考資料【When should I use CROSS APPLY over INNER JOIN?】

CROSS APPLY works better on things that have no simple JOIN condition.

This one selects 3 last records from t2 for each record from t1:

SELECT  t1.*, t2o.*
FROM    t1
CROSS APPLY
        (
        SELECT  TOP 3 *
        FROM    t2
        WHERE   t2.t1_id = t1.id
        ORDER BY
                t2.rank DESC
        ) t2o

It cannot be easily formulated with an INNER JOIN condition.

You could probably do something like that using CTE's and window function:

WITH    t2o AS
        (
        SELECT  t2.*, ROW_NUMBER() OVER (PARTITION BY t1_id ORDER BY rank) AS rn
        FROM    t2
        )
SELECT  t1.*, t2o.*
FROM    t1
INNER JOIN
        t2o
ON      t2o.t1_id = t1.id
        AND t2o.rn <= 3

, but this is less readable and probably less efficient.