Oracle 進階查詢：多表關聯、子查詢與分頁技巧全解析

多表查詢核心機制

在 Oracle 中，多表查詢是處理關聯數據的基石。當我們從多張表獲取數據時，理解其底層運作原理至關重要。

笛卡爾積與避免策略

若不加連接條件直接查詢兩張表（如 SELECT * FROM emp, dept），會產生笛卡爾積。以 emp 14 筆、dept 4 筆為例，結果會暴增至 56 筆。這是因為資料庫將一張表的每一行與另一張表的所有行逐一配對。

避免原則：連接條件數量至少為「表數 - 1」。兩張表至少需一個等值連接條件（如 emp.deptno = dept.deptno）。

等值連接實戰範例

-- 查詢員工姓名、薪資及所屬部門名稱
SELECT e.ename, e.sal, d.dname 
FROM emp e, dept d 
WHERE e.deptno = d.deptno;

-- 篩選 10 號部門並按部門名排序
SELECT e.ename, e.sal, d.dname 
FROM emp e, dept d 
WHERE e.deptno = d.deptno AND e.deptno = 10 
ORDER BY d.dname;

非等值連接：薪資等級判斷

利用 BETWEEN ... AND ... 將員工薪資對應至 salgrade 表的區間：

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

自連接：同表關聯查詢

自連接將單表視為兩張邏輯表，常用於層級關係（如員工與主管）。

查詢員工及其主管姓名

-- 內連接：僅顯示有主管的員工（13 筆，KING 無主管不顯示）
SELECT e.ename AS 員工, m.ename AS 主管 
FROM emp e, emp m 
WHERE e.mgr = m.empno;

-- 左外連接：顯示所有員工，無主管者主管欄位為 NULL
SELECT e.ename AS 員工, m.ename AS 主管 
FROM emp e, emp m 
WHERE e.mgr = m.empno(+);

關鍵點：(+) 標記在右表，表示左表全量輸出（左外連接）。

子查詢深度解析

單行與多行子查詢

• 單行子查詢：返回單值，配合 =、> 等運算子。例：查詢與 SMITH 同部門員工。
• 多行子查詢：返回多值，必須用 IN、ANY、ALL。例：查詢崗位與 10 號部門任一崗位相同的員工。

ALL / ANY 運算子實戰

-- 薪資高於 30 號部門「所有人」的員工（等同 > MAX）
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE deptno = 30);

-- 薪資高於 30 號部門「任一人」的員工（等同 > MIN）
SELECT * FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE deptno = 30);

多列子查詢與 FROM 子查詢

多列子查詢可同時比對多個欄位（如部門+崗位）。FROM 子句中的子查詢（內聯視圖）常用於複雜聚合：

-- 查詢薪資高於本部門平均薪資的員工
SELECT e.ename, e.sal, d.avg_sal, e.deptno 
FROM emp e,
     (SELECT deptno, AVG(sal) AS avg_sal FROM emp GROUP BY deptno) d 
WHERE e.deptno = d.deptno AND e.sal > d.avg_sal;

集合運算：UNION、INTERSECT、MINUS

Oracle 分頁查詢：ROWNUM 三層嵌套

Oracle 無 LIMIT，需用 ROWNUM 偽欄位實現分頁。關鍵限制：ROWNUM 不能直接用 > 比較。

-- 查詢第 7-10 筆（按入職日期排序）
SELECT * FROM (
  SELECT t.*, ROWNUM rn FROM (
    SELECT * FROM emp ORDER BY hiredate
  ) t WHERE ROWNUM < 10
) WHERE rn > 7;

結構解析：

1. 最內層：基礎查詢+排序
2. 中間層：賦予 ROWNUM 並截取前 N 筆
3. 最外層：過濾起始位置

內連接與外連接完整對比

語法演進

-- 傳統 Oracle 語法
SELECT e.ename, d.dname FROM emp e, dept d WHERE e.deptno = d.deptno;

-- ANSI SQL:99 標準語法（推薦）
SELECT e.ename, d.dname FROM emp e INNER JOIN dept d ON (e.deptno = d.deptno);

外連接類型總結

實戰練習：列出所有部門及其員工（含無人部門）

SELECT d.dname, e.* 
FROM dept d LEFT JOIN emp e ON (e.deptno = d.deptno) 
ORDER BY d.deptno;

綜合練習題

-- 1. 員工總數 vs 有補助人數
SELECT COUNT(*), COUNT(comm) FROM emp;

-- 2. 管理者人數（去重 MGR）
SELECT COUNT(DISTINCT mgr) FROM emp;

-- 3. CUBE 多維分析：部門×崗位平均薪資
SELECT AVG(sal), deptno, job FROM emp GROUP BY CUBE(deptno, job);