Oracle 高級子查詢完全指南：多列、相關、WITH、EXISTS 實戰

Oracle 高級子查詢完全指南：多列、相關、WITH、EXISTS 實戰

子查詢是 SQL 編寫的核心技能。本文系統講解多列子查詢、FROM 子句子查詢 (內聯視圖)、標量子查詢表達式、相關子查詢、EXISTS/NOT EXISTS、相關更新/刪除、WITH 子句 (CTE) 七大進階模式，附帶成對/非成對比較、性能陷阱與改寫建議。

一、 多列子查詢：成對 vs 非成對比較

場景：找出與員工 141 或 174 的 MANAGER_ID 和 DEPARTMENT_ID 同時匹配的其他員工。

1. 成對比較 — 推薦，語義精確

SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN (
  SELECT manager_id, department_id
  FROM employees
  WHERE employee_id IN (141, 174)
)
AND employee_id NOT IN (141, 174);

特點：元組比較，要求兩列同時匹配同一行來源數據。

2. 非成對比較 — 邏輯不同，結果可能更多

SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN (SELECT manager_id FROM employees WHERE employee_id IN (174,141))
  AND department_id IN (SELECT department_id FROM employees WHERE employee_id IN (174,141))
  AND employee_id NOT IN (174,141);

差異：MANAGER_ID 匹配員工 A，DEPARTMENT_ID 匹配員工 B 也算滿足，可能產生笛卡爾積式結果。

二、 FROM 子句子查詢 (內聯視圖 / 派生表)

場景：查詢薪資高於部門平均薪資的員工，並顯示部門平均值。

寫法 1：標量子查詢 (Select 列表中)，執行多次，效率低

SELECT last_name, department_id, salary,
       (SELECT AVG(salary) FROM employees e3 WHERE e1.department_id = e3.department_id) avg_sal
FROM employees e1
WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e1.department_id = e2.department_id);

寫法 2：內聯視圖 + JOIN — 推薦，單次掃描聚合

SELECT a.last_name, a.salary, a.department_id, b.salavg
FROM employees a
JOIN (
  SELECT department_id, AVG(salary) salavg
  FROM employees
  GROUP BY department_id
) b ON a.department_id = b.department_id
WHERE a.salary > b.salavg;

三、 標量子查詢表達式 (單行單列)

可用於 CASE、ORDER BY、SELECT 列表等幾乎所有子句 (9i+)。

1. CASE 表達式中動態計算

SELECT employee_id, last_name,
       CASE WHEN department_id = (SELECT department_id FROM departments WHERE location_id = 1800)
            THEN 'Canada' ELSE 'USA' END location
FROM employees;

2. ORDER BY 子句中引用關聯表列排序

SELECT employee_id, last_name
FROM employees e
ORDER BY (SELECT department_name FROM departments d WHERE e.department_id = d.department_id);

四、 相關子查詢：逐行驅動，外部引用內部

-- 查詢薪資高於所在部門平均薪資的員工
SELECT last_name, salary, department_id
FROM employees outer
WHERE salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = outer.department_id
);

機制：外層每行觸發一次內層執行，內層引用 outer.列。適用於「每行邏輯不同」的場景，但大表下性能隱患大。

五、 EXISTS / NOT EXISTS：半連接，存在即停止

1. 找出有下屬的管理者

SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS (
  SELECT 'X' FROM employees WHERE manager_id = outer.employee_id
);

2. 找出無員工的部門

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (
  SELECT 'X' FROM employees WHERE department_id = d.department_id
);

六、 相關更新 / 刪除：跨表同步數據

相關更新

UPDATE table1 alias1
SET col = (SELECT expression FROM table2 alias2 WHERE alias1.key = alias2.key);

相關刪除

DELETE FROM table1 alias1
WHERE col OPERATOR (SELECT expression FROM table2 alias2 WHERE alias1.key = alias2.key);

七、 WITH 子句 (CTE)：模塊化、複用、避免重複掃描

WITH dept_costs AS (
  SELECT d.department_name, SUM(e.salary) dept_total
  FROM employees e, departments d
  WHERE e.department_id = d.department_id
  GROUP BY d.department_name
),
avg_cost AS (
  SELECT SUM(dept_total)/COUNT(*) dept_avg FROM dept_costs
)
SELECT * FROM dept_costs
WHERE dept_total > (SELECT dept_avg FROM avg_cost)
ORDER BY department_name;

優勢：dept_costs 只掃描一次、物化到臨時段，後續多次引用零開銷；邏輯清晰、易維護。

八、 性能優化檢查清單

1. 相關子查詢 → 改寫為 JOIN / 內聯視圖 (如部門平均薪資案例)。
2. NOT IN + 子查詢含 NULL → 改用 NOT EXISTS (NOT IN 語義陷阱)。
3. 標量子查詢在 SELECT 列表 → 考慮 JOIN 或物化視圖 (每行執行一次)。
4. 多列 IN 子查詢 → 確保有組合索引，或改寫為 EXISTS。
5. WITH 子句配合 MATERIALIZE HINT 強制物化 (12c+ 默認啟發式物化)。