Oracle 外部連接語法全解：舊式 (+) 與 ANSI JOIN 對照

內連接回顧

SELECT * FROM a, b WHERE a.id = b.id;
-- 僅回傳雙方均有匹配的行

Oracle 專有舊式語法 (+)

在 WHERE 條件的「缺邊」欄位加 (+)，表示保留另一邊所有行。

限制：不支援 FULL OUTER JOIN；(+) 不能用於 OR 條件、IN 子查詢、比較運算子兩側同時出現。

ANSI SQL:1999 標準語法（9i+ 全支援，強烈推薦）

LEFT OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
  ON (e.department_id = d.department_id);
-- 等價於 WHERE e.department_id = d.department_id(+)
-- 結果：所有員工 + 對應部門，無部門員工部門欄位為 NULL

RIGHT OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
RIGHT OUTER JOIN departments d
  ON (e.department_id = d.department_id);
-- 等價於 WHERE e.department_id(+) = d.department_id
-- 結果：所有部門 + 對應員工，無員工部門員工欄位為 NULL

FULL OUTER JOIN

SELECT e.last_name, e.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d
  ON (e.department_id = d.department_id);
-- 結果：所有員工 + 所有部門，雙邊無匹配皆保留，對方欄位為 NULL
-- 舊式 (+) 無法直接表達，需 UNION 兩邊外連接

多表外連接鏈式寫法

SELECT e.last_name, d.department_name, l.city
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN locations l ON d.location_id = l.location_id;
-- 依序保留 employees → departments → locations

外連接轉內連接陷阱

若在 WHERE 條件中對外連接保留表的欄位加入非空過濾（d.department_name = 'Sales'），外連接將隱性轉為內連接。正確做法：將條件移至 ON 子句。

-- ❌ 錯誤：變成內連接
SELECT * FROM e LEFT JOIN d ON e.id = d.id WHERE d.name = 'Sales';

-- ✅ 正確：保留外連接語義
SELECT * FROM e LEFT JOIN d ON e.id = d.id AND d.name = 'Sales';

效能與最佳實踐

1. 全面採用 ANSI JOIN：可讀性高、跨平台移植無障礙、優化器處理更穩定。
2. 驅動表選擇：小表驅動大表，配合索引巢狀迴圈；大表驅動大表考慮雜湊連接。
3. 統計資訊：外連接基數估算依賴準確統計，定期 DBMS_STATS.GATHER_TABLE_STATS。
4. 避免不必要的外連接：業務邏輯允許時改用內連接，減少 NULL 處理與執行計畫複雜度。

快速對照卡

外連接是多表關聯查詢的核心技能，掌握 ANSI 標準語法並理解語義邊界，是寫出正確、高效 SQL 的關鍵。