表連接是多表查詢的核心。本文系統梳理 Oracle 四大連接類型——等值連接、外部連接、不等值連接、自連接——的語法演進（舊式 (+) vs ANSI SQL:1999）、適用場景與效能考量，附實戰範例。
一、 等值連接
透過共同欄位「值相等」關聯兩表，僅回傳雙方都有匹配的列。
1. 舊式語法 (Oracle Proprietary)
SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;
2. ANSI 標準語法 (推薦)
SELECT e.ename, d.dname
FROM emp e
JOIN dept d ON e.deptno = d.deptno; -- INNER JOIN 關鍵字可省略
3. 多表等值連接
SELECT e.ename, d.dname, l.city
FROM emp e
JOIN dept d ON e.deptno = d.deptno
JOIN loc l ON d.loc_id = l.loc_id;
二、 外部連接
除匹配列外，**保留單邊無匹配的列**，未匹配欄位顯示 NULL。
1. 左外部連接 — 保留左表所有列
| 語法風格 | 範例 |
|---|
| 舊式 (+) | WHERE e.deptno = d.deptno(+) -- (+) 在右表，保留左表 |
| ANSI | FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno |
2. 右外部連接 — 保留右表所有列
| 語法風格 | 範例 |
|---|
| 舊式 (+) | WHERE e.deptno(+) = d.deptno |
| ANSI | FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno |
3. 全外部連接 — 保留雙邊所有列
-- 僅 ANSI 支援
FROM emp e FULL JOIN dept d ON e.deptno = d.deptno;
舊式 (+) **無法**直接表達 FULL JOIN，需 UNION 兩個單向外部連接。
4. 實戰：顯示無部門員工與無員工部門
SELECT e.ename, d.dname
FROM emp e
FULL JOIN dept d ON e.deptno = d.deptno
WHERE e.empno IS NULL OR d.deptno IS NULL;
三、 不等值連接
連接條件為非等號運算子（>、<、BETWEEN、!=），常用於區間匹配、薪資等級判斷。
-- 員工薪資落在哪個等級區間
SELECT e.ename, e.sal, s.grade
FROM emp e
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;
四、 自連接
同一張表以不同別名自我關聯，解決層級結構（員工-主管）、同輩比較等問題。
-- 查詢員工及其直屬主管姓名
SELECT e.ename AS employee, m.ename AS manager
FROM emp e
LEFT JOIN emp m ON e.mgr = m.empno; -- 含無主管的總經理
五、 連接類型選擇決策表
| 業務需求 | 連接類型 | 關鍵字 | 舊式寫法 |
|---|
| 僅需雙方都有資料的匹配列 | 等值/內部連接 | JOIN / INNER JOIN | WHERE a.id = b.id |
| 保留主表所有列，從表無匹配顯示 NULL | 左外部連接 | LEFT JOIN | WHERE a.id = b.id(+) |
| 保留從表所有列，主表無匹配顯示 NULL | 右外部連接 | RIGHT JOIN | WHERE a.id(+) = b.id |
| 保留雙表所有列 | 全外部連接 | FULL JOIN | 不支援 (需 UNION) |
| 區間/範圍匹配 | 不等值連接 | JOIN ... ON BETWEEN | WHERE a.val BETWEEN b.lo AND b.hi |
| 同表層級/同輩關聯 | 自連接 | 自訂別名 JOIN | WHERE e.mgr = m.empno |
六、 效能與最佳實務
- 統一使用 ANSI 語法：可讀性高、支援 FULL JOIN、與其他資料庫相容
- 外部連接駆動表選擇：小表駆動大表，必要時用
/*+ LEADING(d e) */ 指定
- 避免舊式 (+) 與 ANSI 混用：會導致語意模糊或報錯
- 自連接建立索引：
CREATE INDEX idx_emp_mgr ON emp(mgr);
- 不等值連接通常走 NESTED LOOPS，大資料量考慮改寫為範圍分區或 Materialized View
七、 進階：側向連接 / 交叉連接
CROSS JOIN：笛卡兒積，極少業務直接使用，測試資料生成常用LATERAL (12c+)：子查詢可引用前序表欄位，替代舊式 TABLE() 集合函數
掌握四大基礎連接類型與 ANSI 標準語法，覆蓋 95% 多表查詢場景，剩餘 5% 由進階語法補足。
- 上一篇文章： Oracle 表欄位修改全操作指南：增、改、刪、重命名與註解管理
- 下一篇文章： Oracle 記憶體架構深度解析：SGA 七大元件、參數控制與調校策略
Copyright ©
程式碼之家 All Rights Reserved
