Oracle 表聯結完全指南：等值、外部、非等值與自聯結

聯結分類總覽

1. 等值聯結

-- 舊語法
SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno;

-- ANSI 標準 (推薦)
SELECT * FROM emp e JOIN dept d ON e.deptno = d.deptno;

-- 多條件
SELECT * FROM emp e JOIN dept d ON e.deptno = d.deptno AND e.sal > 2000;

2. 外部聯結

Oracle 專有 (+) 語法

-- 右外聯結：顯示所有部門(含無員工)
SELECT * FROM emp e, dept d WHERE e.deptno(+) = d.deptno;

-- 左外聯結：顯示所有員工(含無部門)
SELECT * FROM emp e, dept d WHERE e.deptno = d.deptno(+);

ANSI 標準語法 (9i+ 支援)

-- 右外聯結
SELECT * FROM emp e RIGHT OUTER JOIN dept d ON e.deptno = d.deptno;

-- 左外聯結
SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno;

-- 全外聯結
SELECT * FROM emp e FULL OUTER JOIN dept d ON e.deptno = d.deptno;

3. 非等值聯結

-- 員工薪資對應等級 (salgrade: losal ~ hisal)
SELECT e.empno, e.ename, e.sal, s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;

-- 三表非等值聯結
SELECT e.empno, e.ename, e.sal, s.grade, d.dname
FROM emp e, dept d, salgrade s
WHERE e.deptno = d.deptno AND e.sal BETWEEN s.losal AND s.hisal;

4. 自聯結

將同一張表視為兩個邏輯表，透過別名區分：

-- 查詢員工及其主管姓名
SELECT w.ename 員工, w.mgr 主管編號, m.empno 主管編號, m.ename 主管姓名
FROM emp w, emp m
WHERE w.mgr = m.empno;

• w (Worker)：員工角色
• m (Manager)：主管角色
• 聯結條件：員工的 mgr = 主管的 empno

最佳實踐建議

1. 優先使用 ANSI JOIN 語法，語意清晰、可移植、支援 FULL OUTER JOIN
2. 大表聯結務必確認聯結欄位有索引
3. 外部聯結時，(+) 只能出現在單邊，不可用於多表鏈式外部聯結
4. 自聯結需給予語意化別名 (如 w/m、child/parent)