Oracle 表連接全解析（上）：等值連接與外連接語法、原理與性能優化

連接分類總覽

Oracle 支援四大連接類型：等值連接（內連接）、外連接、非等值連接、自連接。本文聚焦前兩類。

一、等值連接

1. 定義

通過兩表具有相同意義的列（通常為 PK-FK）建立 WHERE a.col = b.col 條件，僅返回兩表中連接列值匹配的行。

2. 語法演進

3. 實戰示例

-- 查詢員工及其部門信息
SELECT e.empno, e.ename, e.sal, d.dname, d.loc
FROM emp e
INNER JOIN dept d ON e.deptno = d.deptno;

-- 顯示薪資 > 2000 的員工及部門名
SELECT e.ename, e.sal, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno
WHERE e.sal > 2000;

4. 執行計劃關鍵點

• Hash Join：大表連接大表、無索引、並行查詢首選。
• Nested Loops：小表驅動大表、驅動表有高選擇性索引。
• Sort Merge Join：無索引、需排序、RAC 環境常見。
• 優化器根據統計信息、索引、Hint 自動選擇。

二、外連接

1. 定義

除返回匹配行外，還返回一側表中無匹配的行，另一側列填 NULL。分為左外連接、右外連接、全外連接。

2. Oracle 專有語法（+）

-- 左外連接：顯示所有 dept，無員工的 dept 也顯示（emp 列為 NULL）
SELECT d.dname, e.ename
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno;

-- 右外連接：顯示所有 emp，無部門的 emp 也顯示
SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno(+);

規則：(+) 放在允許為 NULL 的一側。(+) 不能用於 OR、IN、比較常量，多表連接時每個連接條件需單獨加 (+)。

3. ANSI 標準語法（推薦，9i+）

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

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

-- FULL OUTER JOIN：雙邊全保留
SELECT e.ename, d.dname
FROM emp e
FULL OUTER JOIN dept d ON e.deptno = d.deptno;

4. 外連接轉內連接陷阱

-- 錯誤：WHERE 條件過濾了外連接保留的 NULL 行，導致退化為內連接
SELECT e.ename, d.dname
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno
WHERE d.loc = 'NEW YORK';  -- d.loc 為 NULL 的行被過濾掉

-- 正確：將條件移至 ON 子句
SELECT e.ename, d.dname
FROM emp e
LEFT JOIN dept d ON e.deptno = d.deptno AND d.loc = 'NEW YORK';

三、性能優化清單

1. 統計信息：DBMS_STATS.GATHER_TABLE_STATS 保持最新。
2. 索引：連接列（FK/PK）建立索引，支援 Nested Loops。
3. 避免 SELECT *：僅選擇所需列，減少投影開銷。
4. Hint 干預：/*+ USE_HASH(e d) */、/*+ LEADING(e) */、/*+ ORDERED */。
5. 分區表連接：分區剪枝 + Partition-Wise Join 消除數據重分佈。

結語

等值連接是核心，外連接是補充。堅持使用 ANSI JOIN 語法，將連接條件放在 ON、過濾條件放在 WHERE，避免 (+) 限制與陷阱。理解 Hash Join/Nested Loops/Sort Merge Join 適用場景，配合索引與統計信息，讓連接查詢在海量數據下依然高效穩定。