Oracle 子查詢完整教學：四大位置、多行操作符與效能優化

子查詢是 SQL 處理複雜邏輯的關鍵工具。本文系統講解子查詢在 WHERE、HAVING、FROM、SELECT 四大位置的行為差異、多行操作符（IN/ANY/ALL）語意，並對比多表連接與子查詢效能取捨。

子查詢位置與回傳特性

WHERE 子查詢實戰

單行單列：標量比較

-- 低於平均薪資員工
SELECT * FROM emp WHERE sal < (SELECT AVG(sal) FROM emp);

-- 最早入職員工
SELECT * FROM emp WHERE hiredate = (SELECT MIN(hiredate) FROM emp);

單行多列：元組比較

-- 與 SCOTT 同職同薪
SELECT * FROM emp
WHERE (job, sal) = (SELECT job, sal FROM emp WHERE ename = 'SCOTT')
  AND ename <> 'SCOTT';

多行單列：IN / NOT IN

-- 薪資等於任一經理薪資
SELECT * FROM emp WHERE sal IN (SELECT sal FROM emp WHERE job = 'MANAGER');

-- 陷阱：子查詢含 NULL 導致 NOT IN 無結果
SELECT * FROM emp WHERE sal NOT IN (SELECT sal FROM emp WHERE job = 'MANAGER');

多行操作符：ANY / ALL

-- = ANY 等價 IN
SELECT * FROM emp WHERE sal = ANY (SELECT sal FROM emp WHERE job = 'MANAGER');

-- > ANY：大於子查詢最小值
SELECT * FROM emp WHERE sal > ANY (SELECT sal FROM emp WHERE job = 'MANAGER');

-- < ANY：小於子查詢最大值
SELECT * FROM emp WHERE sal < ANY (SELECT sal FROM emp WHERE job = 'MANAGER');

-- > ALL：大於子查詢最大值
SELECT * FROM emp WHERE sal > ALL (SELECT sal FROM emp WHERE job = 'MANAGER');

-- < ALL：小於子查詢最小值
SELECT * FROM emp WHERE sal < ALL (SELECT sal FROM emp WHERE job = 'MANAGER');

HAVING 子查詢

-- 平均薪資高於公司整體平均的職位
SELECT job, COUNT(empno), AVG(sal)
FROM emp
GROUP BY job
HAVING AVG(sal) > (SELECT AVG(sal) FROM emp);

SELECT 子查詢（標量子查詢）

-- 查每位員工部門名稱（1+N 問題，效能差）
SELECT e.empno, e.ename, e.job,
       (SELECT d.dname FROM dept d WHERE d.deptno = e.deptno)
FROM emp e;

FROM 子查詢（內聯視圖）—— 核心優化手段

場景：各部門名稱、位置、人數

-- 多表連接寫法（笛卡爾積風險）
SELECT d.dname, d.loc, COUNT(e.empno)
FROM emp e, dept d
WHERE e.deptno(+) = d.deptno
GROUP BY d.dname, d.loc;

-- 子查詢優化：先聚合再連接
SELECT d.deptno, d.dname, d.loc, t.cnt
FROM dept d,
     (SELECT deptno, COUNT(empno) cnt FROM emp GROUP BY deptno) t
WHERE d.deptno = t.deptno(+);

多表連接 vs 子查詢效能取捨

• 子查詢優勢：避免中間結果集膨脹、可獨立優化聚合邏輯、適合「大表聚合後再連接小表」
• 連接優勢：優化器可整體規劃連接順序、Hash Join/Merge Join 效能穩定
• 實務建議：以 EXPLAIN PLAN 驗證，複雜報表優先考慮 FROM 子查詢預聚合

複雜查詢分解公式

複雜查詢 = 簡單查詢 + 限定查詢 + 多表連接 + 分組統計 + 子查詢

掌握子查詢四大位置語意與多行操作符邏輯，能將業務需求精準轉化為高效 SQL。