SQL 查詢中 CROSS APPLY 何時優於 INNER JOIN？

SQL 查詢中 CROSS APPLY 何時優於 INNER JOIN？

問題：

在 SQL 查詢中，何時使用 CROSS APPLY 比使用 INNER JOIN 更有效？儘管它們在多對一關係中看起來等效，但 CROSS APPLY 效能優勢的範例卻難以找到。

答案：

CROSS APPLY 在複雜連結的優勢：

當連接表具有無法用 INNER JOIN 輕鬆表達的複雜條件時，CROSS APPLY 顯示出其優勢。考慮以下範例：

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

此查詢為 t1 中的每筆記錄檢索 t2 中的最近三筆記錄。這無法透過 INNER JOIN 輕鬆實現。

CTE 與 CROSS APPLY 的效能比較：

CROSS APPLY 的替代方法是使用具有視窗函數的公用表表達式 (CTE)：

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

但是，此方法可讀性較差，且在某些情況下效率可能較低。

範例：

為了說明效能差異，請考慮以下查詢：

-- 使用 CTE
WITH    q AS
        (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY id) AS rn
        FROM    master
        ),
        t AS 
        (
        SELECT  1 AS id
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    t
JOIN    q
ON      q.rn <= t.id

-- 使用 CROSS APPLY
WITH    t AS 
        (
        SELECT  1 AS id
        UNION ALL
        SELECT  2
        )
SELECT  *
FROM    t
CROSS APPLY
        (
        SELECT  TOP (t.id) m.*
        FROM    master m
        ORDER BY
                id
        ) q

在一個大約有 20,000,000 筆記錄的表上，CTE 查詢需要近 30 秒才能執行，而 CROSS APPLY 查詢則可以立即完成。

以上是SQL 查詢中 CROSS APPLY 何時優於 INNER JOIN？的詳細內容。更多資訊請關注PHP中文網其他相關文章！