Oracle 進階查詢（五）：多表關聯、外連接、自連接與層次查詢詳解

連接類型總覽

• 等值連接：主鍵=外鍵，最常用。
• 非等值連接：範圍條件 (BETWEEN)，如薪資等級表。
• 外連接：保留主表/從表無匹配行 (Oracle (+) 語法)。
• 自連接：同表不同別名關聯，如員工-經理關係。
• 層次查詢：CONNECT BY PRIOR 遞歸樹結構。

實戰範例 (SCOTT.EMP/DEPT/SALGRADE)

1. 等值連接 + 別名消除列歧義

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

2. 非等值連接：薪資等級

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

3. 左外連接：統計各部門人數 (含空部門)

SELECT d.deptno, d.dname, COUNT(e.empno) 人數
FROM dept d, emp e
WHERE d.deptno = e.deptno(+)
GROUP BY d.deptno, d.dname
ORDER BY 1;
-- 結果包含 DEPTNO=40, 人數=0

規則：(+) 加在**從表**列上，表示「從表無匹配時也顯示主表行」。

4. 自連接：查詢員工與其經理

SELECT e.ename || '的經理是' || m.ename
FROM emp e, emp m
WHERE e.mgr = m.empno;
-- KING 無經理 (MGR IS NULL) 不會顯示

注意：自連接產生笛卡爾積，僅適用小表。

5. 層次查詢：組織架構樹

SELECT LEVEL, empno, ename, mgr
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH mgr IS NULL
ORDER BY LEVEL;

• LEVEL：根節點=1，每遞歸一層 +1。
• START WITH：指定根節點條件。
• CONNECT BY PRIOR：定義父子關聯方向。

進階技巧

• SYS_CONNECT_BY_PATH(ename, '→') 顯示完整路徑。
• CONNECT_BY_ISLEAF 判斷葉子節點。
• Oracle 12c+ 可用標準 SQL WITH RECURSIVE 替代。