Oracle 表連接兩大核心方式：等值連接與外部連接 (舊式 (+) 與 ANSI LEFT/RIGHT/FULL) 完整解析

四大連接類型概覽

Oracle 支援：等值連接 (Inner Join)、外部連接 (Outer Join)、不等值連接、自我連接。本文聚焦前兩類核心用法。

測試資料：EMP / DEPT 經典範例

-- EMP: EMPNO, ENAME, DEPTNO, SAL, MGR
-- DEPT: DEPTNO, DNAME, LOC

一、等值連接

透過雙方具有相同意義的欄位 (如 DEPTNO) 建立連接條件，僅回傳雙方皆匹配的列。

舊式語法 (Oracle Proprietary)

-- 隱式笛卡兒積再過濾 (不推薦)
SELECT * FROM emp, dept;

-- 等值連接
SELECT * FROM emp, dept 
WHERE emp.deptno = dept.deptno;

-- 額外條件：薪資 > 2000
SELECT e.ename, e.sal, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
  AND e.sal > 2000;

ANSI SQL-92 標準語法 (推薦)

SELECT e.ename, e.sal, d.dname
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno
WHERE e.sal > 2000;

二、外部連接

除了顯示匹配列，**保留單方 (或雙方) 無匹配的列**，無匹配處以 NULL 填補。

Oracle 8i 舊式 (+) 語法

關鍵規則：(+) 標記在 **「可為 NULL 的那一方」** 欄位上。

ANSI SQL-99 標準語法 (9i+ 完整支援，強烈推薦)

-- LEFT OUTER JOIN：保留 EMP 全部
SELECT e.ename, d.dname
FROM emp e
LEFT OUTER JOIN dept d ON e.deptno = d.deptno;

-- RIGHT OUTER JOIN：保留 DEPT 全部
SELECT e.ename, d.dname
FROM emp e
RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;

-- FULL OUTER JOIN：保留雙方全部 (8i 無法單一語法完成)
SELECT e.ename, d.dname
FROM emp e
FULL OUTER JOIN dept d ON e.deptno = d.deptno;

結果對照 (假設 EMP 有 10,20,30 部門；DEPT 有 10,20,30,40 部門)

遷移建議與最佳實務

1. 全面棄用 (+) 語法：ANSI 語法可讀性高、可移植、支援 FULL JOIN、避免複雜查詢中 (+) 位置錯誤導致邏輯錯誤。
2. 複雜多表連接：使用 CTE (WITH) 逐步建構，再以 ANSI JOIN 串接。
3. 效能無差異：Oracle 內部優化器將 ANSI 語法轉換為內部表示，執行計劃相同。
4. 外部連接轉內部連接陷阱：WHERE 條件若引用外部連接保留側的欄位 (如 WHERE d.dname = 'SALES')，會隱性轉為 INNER JOIN。解法：將條件移至 ON 子句 ON e.deptno = d.deptno AND d.dname = 'SALES'。