SQL Server 進階查詢技巧：多表連接、子查詢、集合運算與 TOP 分頁完全指南

掌握多表連接、子查詢、集合運算與結果限制，是寫出高效、可維護 T-SQL 的核心能力。本文系統講解 ANSI/舊式連接語法、三類連接模式、四種子查詢形態、EXISTS 語意、UNION 集合運算、TOP 分頁與排程關聯，並提供效能優化建議。

一、表連接

1. ANSI SQL-92 標準語法 (推薦)

SELECT o.OrderID, c.CustomerName, o.OrderDate
FROM Sales.Orders o
INNER JOIN Sales.Customers c ON o.CustomerID = c.CustomerID
WHERE o.OrderDate >= '2024-01-01';

• INNER JOIN：僅回傳雙方皆符合條件的列 (預設)
• LEFT OUTER JOIN：保留左表所有列，右表無對應則 NULL
• RIGHT OUTER JOIN：保留右表所有列
• FULL OUTER JOIN：保留雙方所有列
• CROSS JOIN：笛卡兒積，無 ON 條件

2. 舊式 SQL Server 語法 (不推薦)

SELECT o.OrderID, c.CustomerName
FROM Sales.Orders o, Sales.Customers c
WHERE o.CustomerID = c.CustomerID
  AND o.OrderDate >= '2024-01-01';

缺點： 連接條件與篩選條件混雜在 WHERE，易寫錯外部連接 (*=、=* 已棄用)，可讀性差，無法表達 FULL JOIN。

二、子查詢

子查詢是嵌套在外層查詢中的 SELECT，可出現在 SELECT、FROM、WHERE、HAVING 子句。

1. 衍生表 / 內嵌檢視表

SELECT d.CustomerID, d.OrderCount
FROM (
    SELECT CustomerID, COUNT(*) AS OrderCount
    FROM Sales.Orders
    GROUP BY CustomerID
) d
WHERE d.OrderCount > 10;

必須加別名；外層視為普通表，可 JOIN、WHERE、ORDER BY。

2. 純量子查詢 (回傳單值)

SELECT 
    OrderID,
    (SELECT AVG(TotalAmount) FROM Sales.Orders) AS AvgAmount,
    TotalAmount - (SELECT AVG(TotalAmount) FROM Sales.Orders) AS DiffFromAvg
FROM Sales.Orders
WHERE CustomerID = 'ALFKI';

若回傳多列/多行 → 執行錯誤。建議用 CROSS APPLY 或連接替代。

3. 相關子查詢

SELECT c.CustomerName
FROM Sales.Customers c
WHERE EXISTS (
    SELECT 1 FROM Sales.Orders o
    WHERE o.CustomerID = c.CustomerID
      AND o.OrderDate >= DATEADD(MONTH, -1, GETDATE())
);

外層每列執行一次內層，適合「存在性」判斷。現代優化器常轉為 Semi Join，效能極佳。

4. EXISTS / NOT EXISTS 模式

-- 同時訂購產品 A 和 B 的客戶
SELECT c.CustomerName
FROM Sales.Customers c
WHERE EXISTS (SELECT 1 FROM Sales.OrderDetails od JOIN Sales.Orders o ON od.OrderID=o.OrderID WHERE o.CustomerID=c.CustomerID AND od.ProductID='A')
  AND EXISTS (SELECT 1 FROM Sales.OrderDetails od JOIN Sales.Orders o ON od.OrderID=o.OrderID WHERE o.CustomerID=c.CustomerID AND od.ProductID='B');

-- 所有訂單金額均 > 100 的客戶 (無低價訂單)
SELECT c.CustomerName
FROM Sales.Customers c
WHERE NOT EXISTS (SELECT 1 FROM Sales.Orders o WHERE o.CustomerID=c.CustomerID AND o.TotalAmount <= 100);

三、集合運算

SELECT CustomerID FROM Sales.Orders WHERE OrderDate >= '2024-01-01'
UNION        -- 去重
SELECT CustomerID FROM Sales.Orders WHERE OrderDate < '2024-01-01';

SELECT CustomerID FROM Sales.Orders WHERE OrderDate >= '2024-01-01'
UNION ALL    -- 保留重複，效能較好
SELECT CustomerID FROM Sales.Orders WHERE OrderDate < '2024-01-01';

SELECT CustomerID FROM Sales.Orders
EXCEPT       -- 差集：第一集合有、第二集合無
SELECT CustomerID FROM Sales.Orders WHERE IsCancelled=1;

SELECT CustomerID FROM Sales.Orders
INTERSECT    -- 交集：兩集合皆有
SELECT CustomerID FROM Sales.Customers WHERE IsActive=1;

規則： 所有查詢欄位數、順序、資料型別必須相容；ORDER BY 僅能放在最後一個查詢；結果欄位名來自第一個查詢。

四、TOP 與分頁

TOP 基礎用法

SELECT TOP 10 * FROM Sales.Orders ORDER BY OrderDate DESC;           -- 前 10 筆
SELECT TOP 10 PERCENT * FROM Sales.Orders ORDER BY TotalAmount DESC; -- 前 10%
SELECT TOP 10 WITH TIES * FROM Sales.Orders ORDER BY TotalAmount DESC; -- 含並列同分

鐵律： TOP 必須搭配 ORDER BY，否則結果不確定；WITH TIES 強制要求 ORDER BY。

現代分頁：OFFSET/FETCH (SQL 2012+)

DECLARE @PageSize INT = 20, @PageNumber INT = 3;
SELECT OrderID, CustomerID, OrderDate, TotalAmount
FROM Sales.Orders
ORDER BY OrderDate DESC, OrderID
OFFSET (@PageNumber - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

優於舊式 ROW_NUMBER() OVER (...) + WHERE rn BETWEEN ...：語法簡潔、計畫穩定、支援參數化。

效能優化關鍵提醒

• 連接欄位務必建立索引，且資料型別完全一致 (隱式轉換殺手)。
• 子查詢若可改寫為 JOIN 或 APPLY，通常效能更好。
• EXISTS 優於 IN (子查詢)，NOT EXISTS 優於 NOT IN (含 NULL 時語意不同)。
• UNION ALL 無排序去重開銷，明知無重複或允許重複時優先使用。
• 大結果集分頁：鍵集分頁 (WHERE ID > @LastID ORDER BY ID FETCH NEXT ...) 效能恆定，OFFSET 隨頁碼增加而線性遞減。

熟練運用這些結構，能將複雜業務邏輯轉化為集合化、宣告式的高效 SQL。