隨筆

FROM、CTE和APPLY

2026-06 繁體中文

FROM 的主表一般就是左表，而 LEFT JOIN本質上就是取左表+兩表交集的部分，剩下的右表未交集部分則直接為null；而INNER JOIN則是直接取交集，兩表都有的才返回。

CTE 是個很有意思的操作，它跟子查詢不一樣的點是，其本身可以先定義再使用，更符合人類可讀的形式——從外向內。而子查詢就跟變成裡面的巢狀if一樣，你得從內向外的取梳理邏輯：看到一個子查詢的時候得先看裡面，再去看外面倒推這裡在幹嘛。子查詢可以理解為現場使用的邏輯，而執行上來說確實如此，CTE則是預先宣告的邏輯變數。在沒被呼叫之前並不會執行，只有真的被呼叫時才會運作。而從執行器的角度來看，它跟子查詢沒什麼太大差別，效能不會更好，但也不會更壞。

-- CTE 写法：逻辑清晰，先定义，后使用
WITH DeptAvg AS (
    SELECT DepartmentID, AVG(Salary) AS AvgSalary 
    FROM Employees 
    GROUP BY DepartmentID
),
第二个 AS (SELECT ...)
SELECT * 
FROM DeptAvg    -- 像使用普通表一样使用这个 CTE
WHERE AvgSalary > 50000;

OUTER APPLY 也是一個有意思的操作。這是一個SQL Server特有的操作，其他資料庫裡可能叫JOIN LATERAL之類的。類比的話，就像是帶了關聯查詢的JOIN。一般情況下，對於後者來說，它內部是無法引用外部其他表的欄位來進行關聯的——但是，OUTER APPLY就可以：

SELECT 
    A.列名, 
    B.列名
FROM 左表 AS A
OUTER APPLY (
    -- 这里是可以直接引用 A.列名 的子查询或表值函数
    SELECT TOP 1 * 
    FROM 右表 AS R 
    WHERE R.关联键 = A.关联键 
    ORDER BY R.排序键 DESC
) AS B;

JOIN就沒辦法這麼玩兒。如果我需要在JOIN裡面進行一次子查詢，那麼這個子查詢就只能是完全獨立的，不可能還引用左表的欄位來進行匹配。OUTER APPLY 在這裡對應的是 LEFT JOIN，左表裡面沒匹配到的會返回 NULL，而 CROSS APPLY 則是 INNER JOIN，只會返回左右兩表的交集。

這個看上去其實蠻好用的，但它可能會存在效能損失。因為這種關聯匹配本質上是對左表的每行進行遍歷，也自然存在經典的遍歷問題——如果左表資料量極其龐大，那右邊的迴圈就會爆炸。解決此問題的方法就是建立索引，如果右表有非常良好的索引，那麼迴圈爆炸就會消失。執行器不再需要每次遍歷全表來進行匹配，只需要精確匹配到指定項即可。但是，這種情況下遍歷問題仍然存在，即便右表的匹配可以壓縮到精確的幾行，但如果左表幾百上千萬行，那就算是從N對N最佳化到N對1的逐行匹配，效能損失仍然不小。

因此在效能優先的場合下，這個最好還是最佳化成別的效能寫法，但在合適的情況下，甚至是大多數中小場合下，倒是沒有這些擔憂。畢竟比起硬寫JOIN寫到理解和維護困難，輕微的效能損失完全是可接受的。比如我需要獲取子表的TOP N，那這種能關聯外部的APPLY寫法，就比直接寫JOIN要好讀的多。