您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 資料庫基礎

Oracle 表連接四大類型完整指南：等值、外連接、非等值、自連接語法與實戰範例

編輯：Oracle 資料庫基礎

表連接是 SQL 查詢的核心。Oracle 支援 ANSI SQL:1999 標準語法 (推薦) 與傳統 Oracle 專有語法。本文系統整理四大連接類型——**等值連接、外連接、非等值連接、自連接**——的語法對照、適用場景、效能考量與實戰範例，適用 9i 至 23c。

1. 等值連接 — 最常見的主外鍵關聯

標準語法 (ANSI 推薦)

SELECT e.empno, e.ename, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno;  -- INNER JOIN 可省略 INNER

傳統 Oracle 語法

SELECT e.empno, e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

多表等值連接

SELECT e.empno, e.ename, d.dname, s.grade
FROM emp e
JOIN dept d ON e.deptno = d.deptno
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;  -- 非等值見下節

特點：僅返回雙方都匹配的行，內部通常採用 Hash Join / Nested Loop Join / Sort-Merge Join。

2. 外連接 — 保留單邊無匹配行

ANSI 標準語法 (全版本推薦)

類型	語法	保留邊	別名
左外連接	`FROM a LEFT JOIN b ON ...`	左表 (a) 全部行	LEFT OUTER JOIN
右外連接	`FROM a RIGHT JOIN b ON ...`	右表 (b) 全部行	RIGHT OUTER JOIN
全外連接	`FROM a FULL JOIN b ON ...`	雙邊全部行	FULL OUTER JOIN

傳統 Oracle (+) 語法 (9i+ 支援 ANSI，建議遷移)

-- 左外連接：保留 emp 無部門員工
SELECT e.empno, e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno(+);  -- (+) 在右側 = 左外連接

-- 右外連接：保留 dept 無員工部門
SELECT e.empno, e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno;  -- (+) 在左側 = 右外連接

-- 全外連接：傳統語法不支援，需 UNION 組合

關鍵限制

(+) 不能用於 OR 條件、IN 子查詢、比較運算子以外的表達式。
多表外連接時，(+) 僅能出現在一邊，易產生語意模糊。
強烈建議全面遷移至 ANSI 語法，語意清晰、支援全外連接、優化器處理更優。

3. 非等值連接 — 範圍/不等式關聯

連接條件使用 >, <, >=, <=, BETWEEN ... AND ... 等非等值運算子。

經典場景：薪資等級對應

-- ANSI
SELECT e.empno, e.ename, e.sal, s.grade
FROM emp e
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

-- 傳統
SELECT e.empno, e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

三表混合：等值 + 非等值

SELECT e.empno, e.ename, e.sal, s.grade, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno          -- 等值
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;  -- 非等值

效能提示：非等值連接無法使用 Hash Join (需等值)，優化器多採用 Nested Loop 或 Sort-Merge，大表關聯時需注意索引與排序成本。

4. 自連接 — 表與自身關聯

透過別名將同一表視為兩個邏輯集合，解決層級/遞歸關係。

經典場景：員工-經理關係

SELECT w.ename AS worker, w.mgr AS mgr_id, m.ename AS manager
FROM emp w
JOIN emp m ON w.mgr = m.empno;  -- w: 員工角色, m: 經理角色

遞歸層級查詢 (11g+ 遞歸 CTE 更優)

-- 傳統自連接只能固定層級
SELECT w.ename AS l1, m1.ename AS l2, m2.ename AS l3
FROM emp w
LEFT JOIN emp m1 ON w.mgr = m1.empno
LEFT JOIN emp m2 ON m1.mgr = m2.empno;

-- 11g+ 遞歸 CTE (標準、無限層級)
WITH RECURSIVE org_chart(empno, ename, mgr, lvl) AS (
  SELECT empno, ename, mgr, 1 FROM emp WHERE mgr IS NULL  -- 根節點
  UNION ALL
  SELECT e.empno, e.ename, e.mgr, oc.lvl + 1
  FROM emp e JOIN org_chart oc ON e.mgr = oc.empno
)
SELECT * FROM org_chart ORDER BY lvl, empno;

連接類型選型與效能速查表

場景	推薦連接	關鍵索引策略	優化器首選
主外鍵精確匹配	等值內連接	FK 欄位索引、PK 索引	Hash Join (大表) / Nested Loop (小驅動大)
需保留主表所有行	左外連接	被保留表駕駛表，FK 索引	Hash Join Outer / Nested Loop Outer
範圍匹配 (薪資段、日期區間)	非等值連接	範圍欄位索引 (losal, hisal)	Sort-Merge / Nested Loop
層級/遞歸關係	自連接 / 遞歸 CTE	父鍵索引 (mgr)	Nested Loop / 遞歸迭代