多表 JOIN 查詢優化：從 INNER JOIN 改寫為衍生表提升效能

優化前：標準 INNER JOIN

SELECT table1.ID
FROM table1
INNER JOIN table2 ON table1.ID = table2.ID
WHERE table2.col2 = 'xxx';

潛在問題：若 table2 資料量大且 col2 無索引，優化器可能選擇掃描全表再 JOIN，導致大量 I/O。

優化後：衍生表預先篩選

SELECT a.ID
FROM table1 a
INNER JOIN (
    SELECT col1, ID
    FROM table2
    WHERE col2 = 'xxx'
) b ON a.ID = b.ID;

為什麼較快？

1. 減少 JOIN 參與列數：衍生表 b 僅包含符合條件的資料列與必要欄位（ID、col1），資料量大幅縮減。
2. 優化器更易選擇 Nested Loops：小結果集驅動大表索引查找，避免 Hash Match 或 Merge Join 的建構成本。
3. 索引覆蓋機會增加：若 table2 有 (col2, ID, col1) 覆蓋索引，衍生表可直接索引掃描，完全避免鍵查找。

適用場景與注意事項

• ✅ 其中一張表經過嚴格篩選後資料量極小（< 1%）。
• ✅ 篩選欄位有索引，或可建立篩選索引（Filtered Index）。
• ⚠️ 若兩表資料量相近且無選擇性條件，改寫效果有限，甚至因素化導致統計資訊不準。
• ⚠️ SQL Server 2005+ 優化器已能自動將部分 IN/EXISTS 子查詢轉為 JOIN，手動改寫前請先比對實際執行計畫。

進階：搭配 APPLY 運算子

SELECT a.ID, ca.col1
FROM table1 a
CROSS APPLY (
    SELECT TOP 1 col1, ID
    FROM table2
    WHERE table2.ID = a.ID AND col2 = 'xxx'
) ca;

CROSS APPLY 適用於「每筆主表資料只需對應一筆從表資料」的場景，可進一步避免掃描整張從表。

驗證效能差異

SET STATISTICS IO, TIME ON;
-- 執行兩種寫法，比較 logical reads、CPU time、elapsed time

重點觀察 logical reads 減少幅度與執行計畫中 Estimated Rows 是否接近 Actual Rows。