Oracle 表連接進階：不等值連接與自我連接實戰應用

承接前文：ANSI JOIN 標準語法快速回顧

-- INNER JOIN
SELECT * FROM emp JOIN dept ON emp.deptno = dept.deptno;

-- LEFT OUTER JOIN：保留 emp 全部
SELECT * FROM emp LEFT OUTER JOIN dept ON emp.deptno = dept.deptno;

-- RIGHT OUTER JOIN：保留 dept 全部
SELECT * FROM emp RIGHT OUTER JOIN dept ON emp.deptno = dept.deptno;

-- FULL OUTER JOIN：保留雙方全部
SELECT * FROM emp FULL OUTER JOIN dept ON emp.deptno = dept.deptno;

一、不等值連接

連接條件使用非等號運算子：>、<、>=、<=、BETWEEN ... AND ...、!= 等。常用於**區間匹配**、**範圍歸類**。

經典場景：薪資等級表 (SALGRADE)

DESC salgrade;
-- GRADE  LOSAL  HISAL
-- 1      700    1200
-- 2      1201   1400
-- 3      1401   2000
-- 4      2001   3000
-- 5      3001   9999

查詢：員工薪資對應等級

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)。

多表結合：員工 + 部門 + 薪資等級

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;

二、自我連接

同一張表在查詢中出現兩次 (或多次)，視為不同別名，**將自身表的一個鏡像當作另一張表**，用於解決**同表內部關聯**問題 (如員工-主管、節點-父節點、帳戶-轉帳對手)。

經典場景：查詢員工及其主管姓名

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

• w (Worker) 代表員工
• m (Manager) 代表主管 (同表不同別名)
• 連接條件：員工的 MGR = 主管的 EMPNO

結果範例

KING 為社長無主管，MGR 為 NULL，若需保留 KING 列需改用 LEFT JOIN。

保留無主管員工：LEFT SELF JOIN

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

自我連接進階：遞歸層級展開 (配合 CONNECT BY)

SELECT LPAD(' ', 2*(LEVEL-1)) || ename AS org_chart, LEVEL
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

此為遞歸查詢，非單純自我連接，但本質相同：表自我關聯形成樹狀結構。

效能與最佳實務