-- 醫生表
CREATE TABLE Doctor (Id INT IDENTITY, DocNumber NVARCHAR(50), Name NVARCHAR(50));
INSERT INTO Doctor VALUES ('007','Tom'),('008','John'),('009','Jim');
-- 號源表
CREATE TABLE NoSource (Id INT IDENTITY, DocNumber NVARCHAR(50), WorkTime DATETIME);
INSERT INTO NoSource VALUES ('007','2012-08-19'),('007','2012-08-20'),('007','2012-08-21'),('008','2012-08-21');
需求：列出**所有醫生**及其**有效號源數量** (Jim 無號源應顯示 0)。
SELECT COUNT(n.Id) AS Cnt, d.Id, d.Name, d.DocNumber, n.WorkTime
FROM Doctor d
LEFT JOIN NoSource n ON d.DocNumber = n.DocNumber
WHERE DATEDIFF(DAY, GETDATE(), n.WorkTime) > 0 -- 這裡過濾右表
GROUP BY d.Id, d.Name, d.DocNumber, n.WorkTime;
結果：Jim 連同其基本資料一起消失！
原因：`WHERE` 在 `LEFT JOIN` 產生的虛擬表上執行，`n.WorkTime` 為 NULL 的列 (Jim) 不滿足條件 → 被過濾掉，**LEFT JOIN 退化為 INNER JOIN**。
SELECT COUNT(n.Id) AS Cnt, d.Id, d.Name, d.DocNumber, n.WorkTime
FROM Doctor d
LEFT JOIN (
SELECT * FROM NoSource
WHERE DATEDIFF(DAY, GETDATE(), WorkTime) > 0
) n ON d.DocNumber = n.DocNumber
GROUP BY d.Id, d.Name, d.DocNumber, n.WorkTime;
邏輯：先在右表內部完成過濾，產生「僅含有效號源」的虛擬表，再 LEFT JOIN，左表所有列保留，無匹配右表列為 NULL，`COUNT(n.Id)` 正確得到 0。
SELECT
SUM(CASE WHEN DATEDIFF(DAY, GETDATE(), n.WorkTime) > 0 THEN 1 ELSE 0 END) AS Cnt,
d.Id, d.Name, d.DocNumber
FROM Doctor d
LEFT JOIN NoSource n ON d.DocNumber = n.DocNumber
GROUP BY d.Id, d.Name, d.DocNumber;
優點：單次掃描、無衍生表、執行計畫通常更優、邏輯直觀 (聚合層面判斷有效性)。
| 位置 | 語法 | 語意 | 適用場景 |
|---|---|---|---|
| ON 子句 | `LEFT JOIN T2 ON T1.Id = T2.Id AND T2.Flag = 1` | **關聯條件的一部分**；不滿足 → 右表列為 NULL，左表保留 | 關聯邏輯本身包含過濾 (如只關聯啟用狀態的子表) |
| 子查詢/衍生表 | `LEFT JOIN (SELECT * FROM T2 WHERE Flag=1) T2 ON ...` | **預先過濾右表**；同 ON 效果，可讀性更好、可重用 | 複雜過濾、需在右表上加索引提示、多次 JOIN 同一過濾集 |
| WHERE 子句 | `LEFT JOIN ... WHERE T2.Flag = 1` | **最終結果過濾**；會過濾掉右表為 NULL 的左表列 → 變 INNER JOIN | **極少用於 LEFT JOIN 右表**；除非確定要將無匹配的左表列也排除 |
`INNER JOIN` 時，`ON` 與 `WHERE` 對結果集**邏輯等價** (優化器會自動下推)，但 `ON` 更能表達關聯語意，`WHERE` 表達業務過濾，建議**語意分離**：關聯條件寫 `ON`，業務過濾寫 `WHERE`。
「LEFT JOIN 後 WHERE 過濾右表會過濾掉左表」是 SQL 開發者必踩的經典坑。**將右表過濾條件移至 ON 子句、子查詢、或改用條件聚合**，即可保留左表完整性並獲得正確統計。下次寫 `LEFT JOIN` 時，請先問自己：這個條件是「關聯規則」還是「最終過濾」？ 答案決定它屬於 `ON`、子查詢、還是 `WHERE`。
