Oracle 表連接全解析（下）：非等值連接與自連接實戰模式

一、非等值連接

1. 定義

連接條件使用 非等號比較運算符：>、<、>=、<=、BETWEEN ... AND ...、!= 等。典型場景：數值範圍匹配、區間歸類。

2. 經典案例：薪資等級表（SALGRADE）

-- SALGRADE 結構
-- GRADE | LOSAL | HISAL
-- 1     | 700   | 1200
-- 2     | 1201  | 1400
-- ...

-- 查詢員工編號、姓名、薪資、薪資等級
SELECT e.empno, e.ename, e.sal, s.grade
FROM emp e
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

• BETWEEN ... AND 包含邊界值。
• 薪資等級表通常較小，優化器傾向 Nested Loops（全表掃描 SALGRADE 驅動 EMP 索引範圍掃描）。

3. 三表非等值連接：員工-部門-薪資等級

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;  -- 非等值連接

二、自連接

1. 定義

表與自身連接，通過表別名區分不同角色。本質是將同一表視為兩個邏輯表，常用於層級結構（員工-經理、分類樹、BOM 物料清單）。

2. 經典案例：查詢員工及其經理姓名

SELECT w.ename AS worker, w.mgr AS mgr_empno, m.ename AS manager
FROM emp w
JOIN emp m ON w.mgr = m.empno;

• w（Worker）：員工角色，mgr 存儲經理的 empno。
• m（Manager）：經理角色，通過 empno 匹配。
• 無經理的員工（如 KING，mgr IS NULL）不會顯示（內連接特性）。

3. 外連接保留無經理員工

SELECT w.ename AS worker, m.ename AS manager
FROM emp w
LEFT JOIN emp m ON w.mgr = m.empno;

4. 遞歸層級查詢（CONNECT BY，Oracle 專有）

-- 從 KING 開始，顯示完整管理鏈
SELECT LPAD(' ', 2*(LEVEL-1)) || ename AS org_chart
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

輸出縮進層級結構圖。11g+ 推薦 ANSI WITH RECURSIVE（CTE）跨平台遞歸。

三、綜合實戰：多類型連接混合

-- 查詢：員工姓名、部門名、薪資等級、經理姓名
SELECT e.ename AS emp_name,
       d.dname AS dept_name,
       s.grade AS sal_grade,
       m.ename AS mgr_name
FROM emp e
JOIN dept d       ON e.deptno = d.deptno                -- 等值
JOIN salgrade s   ON e.sal BETWEEN s.losal AND s.hisal  -- 非等值
LEFT JOIN emp m   ON e.mgr = m.empno;                   -- 自連接(外連接保留無經理)

四、性能與寫法建議

結語

非等值連接解決「範圍歸類」，自連接解決「層級關係」。兩者結合 ANSI JOIN 標準語法，配合 BETWEEN、LEFT JOIN、CONNECT BY/WITH RECURSIVE，即可優雅表達複雜業務邏輯。關鍵在於：理解數據分佈、預估結果集大小、選擇正確連接類型、驗證執行計劃。