跟我學 MS SQL Server（四）：多表查詢完全指南 — JOIN、APPLY、集合運算與子查詢

一、多表查詢三大核心模式

二、JOIN 類型完整圖解

-- 內聯結：雙方都有匹配
SELECT o.OrderID, c.CustomerName
FROM Orders o
INNER JOIN Customers c ON o.CustomerID = c.CustomerID;

-- 左外部聯結：保留左表所有列
SELECT c.CustomerName, o.OrderID
FROM Customers c
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;

-- 右外部聯結：保留右表所有列 (可改 LEFT 交換順序)
SELECT o.OrderID, c.CustomerName
FROM Orders o
RIGHT JOIN Customers c ON o.CustomerID = c.CustomerID;

-- 全外部聯結：保留雙方所有列
SELECT c.CustomerName, o.OrderID
FROM Customers c
FULL JOIN Orders o ON c.CustomerID = o.CustomerID;

-- 交叉聯結：笛卡兒積 (無 ON 條件)
SELECT c.CustomerName, p.ProductName
FROM Customers c
CROSS JOIN Products p; -- 小心資料量爆炸

三、APPLY 運算子：表值函數的橫向聯結

`CROSS APPLY` / `OUTER APPLY` 允許右側表達式引用左側資料列欄位：

-- 取得每個客戶最近 3 筆訂單
SELECT c.CustomerName, o.OrderID, o.OrderDate
FROM Customers c
CROSS APPLY (
    SELECT TOP 3 OrderID, OrderDate
    FROM Orders
    WHERE CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) o;

-- OUTER APPLY：右側無結果仍保留左側列 (NULL 填充)
SELECT c.CustomerName, o.OrderID
FROM Customers c
OUTER APPLY (
    SELECT TOP 1 OrderID
    FROM Orders
    WHERE CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) o;

適用場景：逐列計算、取 Top N、拆分分隔字串、調用表值函數。

四、集合運算：UNION / EXCEPT / INTERSECT

-- 所有客戶與員工姓名合併 (去重)
SELECT FirstName, LastName FROM Customers
UNION
SELECT FirstName, LastName FROM Employees;

-- 僅出現在客戶不在員工的姓名
SELECT FirstName, LastName FROM Customers
EXCEPT
SELECT FirstName, LastName FROM Employees;

-- 兩者皆有的姓名
SELECT FirstName, LastName FROM Customers
INTERSECT
SELECT FirstName, LastName FROM Employees;

規則：欄位數、順序、相容型別必須一致；`ORDER BY` 僅能放最後。

五、子查詢：相關 vs 非相關

非相關子查詢 (獨立執行)

-- 找出有訂單的客戶
SELECT * FROM Customers
WHERE CustomerID IN (SELECT DISTINCT CustomerID FROM Orders);

-- 等效 JOIN (通常更快)
SELECT DISTINCT c.* FROM Customers c JOIN Orders o ON c.CustomerID = o.CustomerID;

相關子查詢 (逐外層列執行)

-- 找出每個客戶最新訂單日期
SELECT c.CustomerName,
       (SELECT MAX(OrderDate) FROM Orders o WHERE o.CustomerID = c.CustomerID) AS LatestOrder
FROM Customers c;

-- EXISTS 版本 (半聯結，通常最優)
SELECT c.CustomerName
FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);

六、子查詢 vs JOIN 效能對比

七、CTE (通用資料表運算式) 與遞歸查詢

-- 非遞歸：簡化複雜查詢
WITH TopCustomers AS (
    SELECT CustomerID, SUM(TotalAmt) AS TotalSales
    FROM Orders
    WHERE OrderDate >= DATEADD(YEAR, -1, GETDATE())
    GROUP BY CustomerID
    HAVING SUM(TotalAmt) > 100000
)
SELECT c.CustomerName, tc.TotalSales
FROM Customers c JOIN TopCustomers tc ON c.CustomerID = tc.CustomerID;

-- 遞歸：組織架構/物料清單
WITH OrgChart AS (
    SELECT EmployeeID, ManagerID, EmployeeName, 0 AS Level
    FROM Employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.EmployeeName, oc.Level + 1
    FROM Employees e JOIN OrgChart oc ON e.ManagerID = oc.EmployeeID
)
SELECT * FROM OrgChart ORDER BY Level, EmployeeName;
-- 預設最大遞歸 100 層，可用 OPTION (MAXRECURSION 0) 移除限制

八、多表查詢效能優化檢查清單

1. [ ] 所有 JOIN/WHERE 條件欄位是否有適當索引 (覆蓋索引更佳)
2. [ ] 避免 `SELECT *`，明確列出所需欄位
3. [ ] 使用 `EXISTS` 替代大結果集 `IN`
4. [ ] 標量子查詢移至 `APPLY` 或 `JOIN`
5. [ ] `UNION` 改 `UNION ALL` (確定無重複或業務允許)
6. [ ] 檢查執行計劃：Hash Match vs Nested Loops vs Merge Join 選擇合理性
7. [ ] 統計資訊是否最新：`UPDATE STATISTICS` 或自動更新
8. [ ] 參數化查詢避免計劃快取膨脹

九、常見錯誤修正表

下一篇將講解子查詢合併查詢進階：`IN`/`NOT IN`/`EXISTS`/`NOT EXISTS` 邏輯差異、彙總函數在子查詢中的應用、窗口函數 (ROW_NUMBER/RANK/DENSE_RANK) 替代子查詢模式。