Oracle資料庫入門：多表連接與子查詢完全指南

表連接基礎

從多個表查詢資料，需在 WHERE 指定連接條件，否則產生笛卡爾積。

SELECT 欄位清單
FROM table1, table2
WHERE table1.col = table2.col;

• 同名欄位必須加表前綴（或別名）。
• 連接 N 個表至少需 N-1 個連接條件。
• 使用表別名簡化查詢並提升可讀性。

範例：SCOTT 範例結構

-- 笛卡爾積：14 x 5 = 70 筆
SELECT empno, ename, sal, emp.deptno, dname, loc FROM emp, dept;

-- 等值連接：14 筆
SELECT empno, ename, sal, emp.deptno, dname, loc
FROM emp, dept
WHERE emp.deptno = dept.deptno;

Oracle 8i (SQL-92) 連接語法

等值連接

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

非等值連接

場景：查詢員工薪資等級（salgrade 表）。

SELECT empno, ename, sal, grade AS 薪資等級
FROM emp, salgrade
WHERE sal BETWEEN losal AND hisal;

外連接 (Oracle 專有 (+) 語法)

-- 左外連接：顯示 emp 所有列，dept 無對應也顯示
SELECT e.empno, e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno(+);

-- 右外連接：顯示 dept 所有列，emp 無對應也顯示
SELECT e.empno, e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno;

自連接

查詢員工及其主管資訊。

SELECT a.empno, a.ename, a.mgr, b.ename AS mgr_name
FROM emp a, emp b
WHERE a.mgr = b.empno;

SQL:1999 (Oracle 9i+) 標準連接語法

SELECT 欄位清單
FROM table1
[CROSS JOIN table2]
[NATURAL JOIN table2]
[JOIN table2 USING (col_name)]
[JOIN table2 ON (table1.col = table2.col)]
[(LEFT | RIGHT | FULL OUTER) JOIN table2 ON (table1.col = table2.col)]
...;

• 多表連接時，依序指定相鄰兩表連接條件。
• 新舊語法效能無差異，視團隊習慣選擇。

交叉連接

產生笛卡爾積，等同無 WHERE 連接條件。

SELECT empno, ename, dname FROM emp a CROSS JOIN dept b;

自然連接

自動基於所有同名欄位等值連接。

SELECT empno, ename, sal, deptno, dname
FROM emp NATURAL JOIN dept;

• 同名欄位資料型別必須一致。
• 不可在參照欄位加表前綴。

USING 子句

指定特定同名欄位進行等值連接。

SELECT empno, ename, sal, deptno, dname
FROM emp JOIN dept USING (deptno);

• 參照欄位不可加表前綴。

ON 子句

支援任意連接條件，含非同名欄位。

SELECT empno, ename, sal, emp.deptno, dname
FROM emp JOIN dept ON (emp.deptno = dept.deptno);

標準外連接

-- 左外連接
SELECT employee_id, last_name, salary, department_id, department_name
FROM employees LEFT JOIN departments USING (department_id);

-- 右外連接
SELECT ... FROM employees RIGHT JOIN departments ...;

-- 全外連接
SELECT ... FROM employees FULL OUTER JOIN departments ...;

子查詢

基本概念

• 子查詢先執行，主查詢使用其結果。
• 分類：單行子查詢（回傳 1 列）、多行子查詢（回傳多列）。
• 必須用括號括起，建議置於比較運算子右側。
• 除 Top-N 分析外，子查詢中勿用 ORDER BY。

單行子查詢

-- 查詢薪資高於 7654 號員工的所有人員
SELECT * FROM emp
WHERE sal > (SELECT sal FROM emp WHERE empno = 7654);

• 可使用單列比較運算子 (=, >, <, >=, <=, <>)。
• 子查詢回傳 0 列 → 主查詢無結果。

多行子查詢

• 必須使用多列比較運算子：IN, ANY, ALL。
• 不可使用單列比較運算子。

-- 非法：多行結果用單列運算子
SELECT * FROM emp WHERE sal > (SELECT AVG(sal) FROM emp GROUP BY deptno);

-- 合法：使用 IN
SELECT * FROM emp WHERE sal IN (SELECT MAX(sal) FROM emp GROUP BY deptno);

Top-N 查詢與分頁

基本 Top-N

SELECT * FROM (
    SELECT * FROM emp ORDER BY sal DESC
) WHERE ROWNUM <= 5;

ROWNUM 偽欄特性

• 查詢結果隱含欄位，從 1 起算。
• 先過濾後排序：WHERE ROWNUM <= 5 ORDER BY sal DESC 會先取前 5 筆再排序，非預期結果。
• ROWNUM >= 5 永無結果（首列 ROWNUM=1 不符合被過濾，下一列仍為 1）。

分頁查詢模板 (Oracle 11g 及之前)

SELECT * FROM (
    SELECT ROWNUM rn, a.* FROM (
        SELECT * FROM emp ORDER BY sal DESC
    ) a
) WHERE rn BETWEEN 5 AND 10;

關鍵步驟：

1. 內層子查詢先排序。
2. 中層賦予 ROWNUM 別名 (rn)，使其成為實質欄位可比較。
3. 外層套用區間條件。

注意：外層若直接用 ROWNUM 而非別名，仍指向外層查詢新產生的偽欄，無法達到分頁效果。