Oracle 支援四種基礎連接模式，各有獨特語義、適用場景與效能特徵。掌握標準 SQL:1999 語法 (ANSI JOIN) 與 Oracle 專有語法 (+) 的對應關係，能寫出可讀性強、可移植、效能穩定的查詢。本文以 EMP/DEPT/SALGRADE 經典範例，系統講解四大連接模式。
-- Oracle 專有 (舊式)
SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;
-- ANSI SQL:1999 標準 (推薦)
SELECT * FROM emp JOIN dept ON emp.deptno = dept.deptno;
-- 或 INNER JOIN (同義)
SELECT * FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;SELECT e.ename, e.sal, d.dname
FROM emp e JOIN dept d ON e.deptno = d.deptno
WHERE e.sal > 2000;| 類型 | 語義 | Oracle (+) 語法 | ANSI 標準語法 (推薦) |
|---|---|---|---|
| 左外連接 | 保留左表所有行，右表無匹配則 NULL 填充 | e.deptno = d.deptno(+) | FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno |
| 右外連接 | 保留右表所有行，左表無匹配則 NULL 填充 | e.deptno(+) = d.deptno | FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno |
| 全外連接 | 保留雙邊所有行，無匹配側 NULL 填充 | 不支援 (需 UNION 實現) | FROM emp e FULL JOIN dept d ON e.deptno = d.deptno |
-- 右外連接：DEPT 為主，保留所有部門
SELECT e.ename, d.dname
FROM emp e RIGHT JOIN dept d ON e.deptno = d.deptno;
-- 結果：SALES 部門無員工 → ename 顯示 NULLWHERE e.deptno = d.deptno(+) OR e.mgr = 7839 → 錯誤-- ANSI 標準 (支援 >, <, >=, <=, BETWEEN, != 等)
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, 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;將同一表視為兩個邏輯實體 (別名 A、B)，以不同角色參與連接。
SELECT w.ename AS worker, w.mgr, m.empno AS mgr_empno, m.ename AS manager
FROM emp w JOIN emp m ON w.mgr = m.empno;
-- w (worker) 為員工角色，m (manager) 為經理角色
-- 連接條件：員工的 MGR (經理編號) = 經理的 EMPNO-- Oracle 專有 CONNECT BY
SELECT LPAD(' ', 2*(LEVEL-1)) || ename AS org_tree
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;
-- ANSI 標準 Recursive CTE (11gR2+)
WITH RECURSIVE org_chart AS (
SELECT empno, ename, mgr, 1 AS lvl, CAST(ename AS VARCHAR2(4000)) AS path
FROM emp WHERE mgr IS NULL
UNION ALL
SELECT e.empno, e.ename, e.mgr, oc.lvl+1, oc.path || ' > ' || e.ename
FROM emp e JOIN org_chart oc ON e.mgr = oc.empno
)
SELECT lvl, path FROM org_chart ORDER BY path;| 場景 | 推薦語法 | 關鍵優化點 |
|---|---|---|
| 標準多表等值連接 | ANSI INNER JOIN | 連接欄位建立索引、統計資訊最新、小表驅動大表 |
| 保留無匹配主表 | LEFT JOIN | 主表為驅動表、被連接表索引包含連接鍵 |
| 範圍/區間匹配 | JOIN ON BETWEEN / >= <= | 範圍表較小、考慮物化視圖預聚合 |
| 層級/遞歸關係 | 自連接 + CONNECT BY / Recursive CTE | 遞歸深度限制 (MAX_DEPTH)、索引覆蓋 MGR/EMPNO |
| 全外連接 | FULL JOIN | 避免 UNION ALL 重複掃描、評估是否真正需要 |
四大連接模式覆蓋 99% 關聯查詢需求。堅持 ANSI SQL:1999 語法 是現代 Oracle 開發的鐵律：可讀性高、可移植、優化器友善、避開 (+) 陷阱。理解每種連接的語義邊界 (內/左/右/全/非等值/自連接)，並配合索引、統計資訊、執行計劃驗證，即可寫出既正確又高效的連接查詢。下次寫 JOIN 時，請先問：「我要保留哪一邊的無匹配行？連接條件是等值還是範圍？」——答案決定了 JOIN 類型。
