SQL Server 外聯接 (OUTER JOIN) 深度解析：LEFT/RIGHT/FULL JOIN 行為差異與謂詞位置陷阱

外聯接是保留單方或雙方所有資料列的關鍵技術。本文針對 LEFT JOIN、RIGHT JOIN、FULL JOIN 三種外聯接，提供官方 pubs 範例資料庫的完整查詢與結果集對照，重點解析 ON 子句與 WHERE 子句謂詞位置對語意的決定性影響，並說明 SQL-92 標準語法優於舊式 *=/=* 的原因。

範例資料結構

• AUTHORS：作者資料，含 CITY 欄位。
• PUBLISHERS：出版社資料，含 CITY 欄位。
• 僅兩位作者 (Abraham Bennet, Cheryl Carson) 與出版社位於同一城市。

一、LEFT OUTER JOIN：保留左表所有列

USE PUBS;
SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAME
FROM AUTHORS A
LEFT OUTER JOIN PUBLISHERS P ON A.CITY = P.CITY
ORDER BY P.PUB_NAME, A.AU_LNAME, A.AU_FNAME;

結果摘要 (23 列)

• 21 位作者無匹配出版社 → PUB_NAME = NULL。
• 2 位作者有匹配 → 顯示出版社名稱 ALGODATA INFOSYSTEMS。

關鍵：左表 (AUTHORS) 所有列必現，右表無匹配處填 NULL。

二、RIGHT OUTER JOIN：保留右表所有列

SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAME
FROM AUTHORS A
RIGHT OUTER JOIN PUBLISHERS P ON A.CITY = P.CITY
ORDER BY P.PUB_NAME, A.AU_LNAME, A.AU_FNAME;

結果摘要 (9 列)

• 8 間出版社無匹配作者 → AU_FNAME/AU_LNAME = NULL。
• 1 間出版社 (ALGODATA) 有 2 位匹配作者。

關鍵：右表 (PUBLISHERS) 所有列必現，左表無匹配處填 NULL。

三、謂詞位置陷阱：ON vs WHERE

-- 正確：ON 子句加入篩選，保留右表所有列
SELECT S.STOR_ID, S.QTY, T.TITLE
FROM SALES S
RIGHT JOIN TITLES T ON S.TITLE_ID = T.TITLE_ID AND S.QTY > 50;

-- 錯誤：WHERE 過濾，NULL 被排除 → 等同 INNER JOIN
SELECT S.STOR_ID, S.QTY, T.TITLE
FROM SALES S
RIGHT JOIN TITLES T ON S.TITLE_ID = T.TITLE_ID
WHERE S.QTY > 50;

規則：外聯接保留端的篩選條件必須放在 ON 子句，否則外聯接退化為內聯接。

四、FULL OUTER JOIN：雙向保留

SELECT A.AU_FNAME, A.AU_LNAME, P.PUB_NAME
FROM AUTHORS A
FULL OUTER JOIN PUBLISHERS P ON A.CITY = P.CITY
ORDER BY P.PUB_NAME, A.AU_LNAME, A.AU_FNAME;

結果摘要 (30 列)

• 21 位無匹配作者 + 8 間無匹配出版社 + 2 位匹配作者 = 31 邏輯列，實際 30 列 (匹配列合併)。
• 等同 LEFT JOIN UNION RIGHT JOIN 去重。

五、SQL-92 vs 舊式語法

舊式語法在複雜條件下易產生語意歧義 (如多表外聯接混合)，SQL-92 明確分離聯接與篩選邏輯。

六、實務建議

1. ✅ 90% 場景僅需 LEFT JOIN，習慣以「主表在左、從表在右」撰寫。
2. ✅ 外聯接篩選條件 一律放在 ON 子句，WHERE 僅用於最終結果過濾。
3. ✅ 避免 RIGHT JOIN，改用 LEFT JOIN 交換表順序，閱讀更直覺。
4. ✅ FULL JOIN 僅用於對帳、差異報表等雙向保留場景，大表慎用。