SQL Server 子查詢優化完全指南：IN/EXISTS/相關子查詢/TOP/ROW_NUMBER/分析函數效能對比

前言：子查詢效能的常見誤區

「子查詢效能差」是片面認知。關鍵在於子查詢類型 (相關/非相關)、寫法 (IN/EXISTS/JOIN/APPLY)、最佳化器轉換能力。本文以「取得每位員工最新訂單」為核心案例，對比 Oracle 與 SQL Server 多種寫法效能，並提供現代最佳實務。

一、測試資料建立

CREATE TABLE Orders (
    OrderID     VARCHAR(6),
    CustomerID  VARCHAR(6),
    EmployeeID  INT,
    OrderDate   DATETIME
);

INSERT INTO Orders VALUES
('110001','WBQ',1,'2000-01-11'),('110002','WBQ',1,'2000-01-21'),
('110003','WBQ',2,'2000-01-11'),('110004','WBQ',3,'2000-02-01'),
('110005','CZH',1,'2000-02-01'),('110006','CZH',2,'2000-03-01'),
('110007','CZH',2,'2000-03-01'),('110008','KIDD',3,'2000-02-01'),
('110009','KIDD',1,'2000-04-01'),('110010','KIDD',2,'2000-03-01'),
('110011','KIDD',3,'2000-02-01'),('110012','CZH',3,'2000-01-10');

目標：每位 EmployeeID 取最新 OrderDate，同日期取最大 OrderID (唯一識別)。

二、Oracle 多欄位 IN 子查詢 (SQL Server 不支援)

-- Oracle 支援多欄位 IN
SELECT EmployeeID, OrderDate, OrderID, CustomerID
FROM Orders
WHERE (EmployeeID, OrderDate, OrderID) IN (
    SELECT EmployeeID, OrderDate, MAX(OrderID)
    FROM Orders
    WHERE (EmployeeID, OrderDate) IN (
        SELECT EmployeeID, MAX(OrderDate)
        FROM Orders GROUP BY EmployeeID
    )
    GROUP BY EmployeeID, OrderDate
);

SQL Server 不支援多欄位 IN，需改寫。

三、SQL Server 傳統相關子查詢寫法

1. 雙層 MAX (正確但掃描兩次)

SELECT EmployeeID, OrderDate, OrderID, CustomerID
FROM Orders O1
WHERE OrderDate = (SELECT MAX(OrderDate) FROM Orders O2 WHERE O1.EmployeeID = O2.EmployeeID)
  AND OrderID   = (SELECT MAX(OrderID)   FROM Orders O2 WHERE O1.EmployeeID = O2.EmployeeID AND O1.OrderDate = O2.OrderDate)
ORDER BY EmployeeID, OrderDate, OrderID;

• 每列外部查詢觸發兩次內部聚合，大資料量效能差。

2. 二進位串聯技巧 (單次掃描，但難維護)

SELECT
    CAST(SUBSTRING(BinStr, 1, 8) AS DATETIME) AS OrderDate,
    CAST(SUBSTRING(BinStr, 9, 6) AS VARCHAR(6)) AS OrderID,
    CAST(SUBSTRING(BinStr, 15, 6) AS VARCHAR(6)) AS CustomerID
FROM (
    SELECT EmployeeID,
           MAX(CAST(OrderDate AS BINARY(8)) + CAST(OrderID AS BINARY(6)) + CAST(CustomerID AS BINARY(6))) AS BinStr
    FROM Orders
    GROUP BY EmployeeID
) D;

• 利用二進位排序特性，單次 GROUP BY 完成。但型別轉換、長度計算極易出錯，不建議生產使用。

四、現代最佳寫法 (SQL Server 2005+)

1. TOP 1 + ORDER BY (相關子查詢，直觀高效)

SELECT EmployeeID, OrderDate, OrderID, CustomerID
FROM Orders O1
WHERE OrderID = (
    SELECT TOP (1) OrderID
    FROM Orders O2
    WHERE O1.EmployeeID = O2.EmployeeID
    ORDER BY OrderDate DESC, OrderID DESC, CustomerID
)
ORDER BY EmployeeID, OrderDate, OrderID;

• 優點：邏輯清晰、最佳化器通常轉為 APPLY / TOP 計畫、索引 (EmployeeID, OrderDate DESC, OrderID DESC) 可見極速。
• 缺點：若外部表極大，相關子查詢逐列執行仍有開銷。

2. IN + TOP 1 (可擴充為 TOP N)

SELECT EmployeeID, OrderDate, OrderID, CustomerID
FROM Orders O1
WHERE OrderID IN (
    SELECT TOP (1) OrderID
    FROM Orders O2
    WHERE O1.EmployeeID = O2.EmployeeID
    ORDER BY OrderDate DESC, OrderID DESC, CustomerID
)
ORDER BY EmployeeID, OrderDate, OrderID;

• 語意上 IN 允許回傳多列，未來需求改為「每員工最新 3 筆」只需改 TOP (3)。

3. ROW_NUMBER() 視窗函數 (SQL 2005+，集合式、效能最穩定)

WITH Ranked AS (
    SELECT
        EmployeeID, OrderDate, OrderID, CustomerID,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY OrderDate DESC, OrderID DESC) AS rn
    FROM Orders
)
SELECT EmployeeID, OrderDate, OrderID, CustomerID
FROM Ranked
WHERE rn = 1
ORDER BY EmployeeID, OrderDate, OrderID;

• 優點：單次掃描 + 排序/雜湊分割、無相關子查詢逐列開銷、易擴充 WHERE rn <= N、支援 RANK()/DENSE_RANK() 處理同分。
• 最佳化器：通常產生 Segment + Sequence Project + Filter 計畫，極高效。

4. CROSS APPLY (SQL 2005+，語意等同 TOP 1 相關子查詢)

SELECT O1.EmployeeID, CA.OrderDate, CA.OrderID, CA.CustomerID
FROM (SELECT DISTINCT EmployeeID FROM Orders) O1
CROSS APPLY (
    SELECT TOP (1) OrderDate, OrderID, CustomerID
    FROM Orders O2
    WHERE O2.EmployeeID = O1.EmployeeID
    ORDER BY OrderDate DESC, OrderID DESC
) CA;

• 適用於「外部驅動集較小、內部查詢需複雜邏輯」場景。

五、效能對比總結表 (大資料量趨勢)

六、索引設計建議

-- 針對上述所有寫法的最佳索引
CREATE INDEX IX_Orders_Emp_Date_ID ON Orders (EmployeeID, OrderDate DESC, OrderID DESC)
INCLUDE (CustomerID);  -- 覆蓋索引，避免回表

• EmployeeID 為等值查找 (Seek)。
• OrderDate DESC, OrderID DESC 符合 ORDER BY 方向，TOP 1 直接取首列。
• INCLUDE (CustomerID) 覆蓋查詢，無需 Key Lookup。

七、避坑指南

• 別用 IN (SELECT ...) 做大表半連接：SQL Server 早期版本易轉為 Nested Loops 逐列執行，改用 EXISTS 或 JOIN。
• 非相關子查詢提升為 JOIN/CTE：WHERE col IN (SELECT col FROM table WHERE ...) → JOIN (SELECT DISTINCT col FROM table WHERE ...) t ON ...。
• 統計資料過期導致誤估：大表變更後 UPDATE STATISTICS 或啟用自動更新。
• 避免標量函數包裝子查詢：dbo.fn_GetMaxOrder(EmployeeID) 會逐列執行，改用 APPLY 或內嵌表值函數。

結語

子查詢優化的核心：優先用集合式視窗函數 (ROW_NUMBER)，次選 TOP 1 相關子查詢/APPLY，配合覆蓋索引。避免多層巢狀相關子查詢、二進位技巧、標量函數封裝。SQL Server 2005+ 視窗函數成熟度極高，應成為處理「每組 Top-N」問題的首選模式。