Oracle LEFT/RIGHT/FULL JOIN 實戰範例：從 DUAL 看外部連接語義

Oracle 外部連接語法與語義完整解析

外部連接保留單側資料表的所有列，另一側無匹配時以 NULL 填充。本文以最簡單的 DUAL 表自連接為例，直觀展示 LEFT/RIGHT/FULL JOIN 的結果差異，並對比傳統 (+) 語法與 ANSI SQL:1999 標準語法。

三種外部連接語義對比

標準 ANSI 語法範例 (以 DUAL 自連接演示)

-- 建立測試資料：DUAL 只有 1 列 'X'
-- LEFT JOIN：左表全保留，右表條件 left.dummy||'d' = right.dummy 不成立 → 右側 NULL
SELECT NVL(l.dummy, '右側全補') AS left_dummy,
       NVL(r.dummy, '左側全補') AS right_dummy
FROM   dual l LEFT JOIN dual r
ON     l.dummy || 'd' = r.dummy;

-- 結果：left_dummy='X', right_dummy='左側全補'

-- RIGHT JOIN：右表全保留，左表條件不成立 → 左側 NULL
SELECT NVL(l.dummy, '右側全補') AS left_dummy,
       NVL(r.dummy, '左側全補') AS right_dummy
FROM   dual l RIGHT JOIN dual r
ON     l.dummy || 'd' = r.dummy;

-- 結果：left_dummy='右側全補', right_dummy='X'

-- FULL JOIN：雙側全保留
SELECT NVL(l.dummy, '右側全補') AS left_dummy,
       NVL(r.dummy, '左側全補') AS right_dummy
FROM   dual l FULL JOIN dual r
ON     l.dummy || 'd' = r.dummy;

-- 結果：兩列皆為補值文字 (因無匹配列)

傳統 Oracle (+) 語法對應

-- 等同 LEFT JOIN：右表欄位加 (+)
SELECT NVL(l.dummy, '右側全補'), NVL(r.dummy, '左側全補')
FROM   dual l, dual r
WHERE  l.dummy || 'd' = r.dummy(+);

-- 等同 RIGHT JOIN：左表欄位加 (+)
SELECT NVL(l.dummy, '右側全補'), NVL(r.dummy, '左側全補')
FROM   dual l, dual r
WHERE  l.dummy(+) || 'd' = r.dummy;

-- FULL JOIN：(+) 語法不支援，必須用 UNION 組合
SELECT ... FROM dual l, dual r WHERE l.dummy||'d' = r.dummy(+)
UNION
SELECT ... FROM dual l, dual r WHERE l.dummy(+)||'d' = r.dummy;

關鍵差異與最佳實踐

實務應用模式

-- 模式 1：主表保留，附屬表可選 (最常見)
SELECT e.emp_name, d.dept_name
FROM   employees e LEFT JOIN departments d
ON     e.dept_id = d.dept_id;

-- 模式 2：雙向補全，發現孤兒資料
SELECT COALESCE(s.student_id, g.student_id) AS student_id,
       s.student_name, g.grade
FROM   students s FULL JOIN grades g
ON     s.student_id = g.student_id
WHERE  s.student_id IS NULL OR g.student_id IS NULL;  -- 找出不匹配

-- 模式 3：條件下推陷阱 (WHERE 會把 LEFT JOIN 變 INNER JOIN)
-- 錯誤
SELECT * FROM employees e LEFT JOIN departments d
ON e.dept_id = d.dept_id
WHERE d.location_id = 100;  -- 等同 INNER JOIN

-- 正確：移至 ON 條件
SELECT * FROM employees e LEFT JOIN departments d
ON e.dept_id = d.dept_id AND d.location_id = 100;

效能提示

• 外部連接通常比內部連接耗資源 (需保留未匹配列、外部連接到內部連接轉換限制)
• 大表 LEFT JOIN 小表優於小表 LEFT JOIN 大表 (驅動表選擇)
• 必要時用 /*+ LEADING(e) USE_NL(d) */ 強制連接順序與方法
• 統計資訊準確對 CBO 選擇正確外部連接執行計劃至關重要

掌握 ANSI 外部連接語法、語義邊界、條件下推陷阱與傳統 (+) 語法對應，是編寫正確、可維護、高效能 SQL 的基礎技能。