Oracle 子查詢實戰 (一)：WHERE 子句中單行/多行/多列子查詢與操作符應用

Oracle 子查詢實戰 (一)：WHERE 子句中單行/多行/多列子查詢與操作符應用

子查詢是構建複雜查詢的核心工具。依返回結果集結構，分為單行單列、單行多列、多行單列 三類。本文以 EMP 表為例，系統演示各類子查詢在 WHERE 子句中的寫法、適用操作符 (IN/ANY/ALL/EXISTS) 及空值陷阱。

一、單行單列子查詢 — 使用單值操作符 (=, <>, >, <, >=, <=)

1. 查詢薪資最低的員工完整資訊

SELECT * FROM emp e 
WHERE e.sal = (SELECT MIN(sal) FROM emp);

2. 查詢薪資低於 ALLEN 的員工

SELECT * FROM emp e 
WHERE e.sal < (SELECT sal FROM emp WHERE ename = 'ALLEN');

3. 查詢薪資高於公司平均薪資的員工

SELECT * FROM emp e 
WHERE e.sal > (SELECT AVG(sal) FROM emp);

二、單行多列子查詢 — 使用元組比較 (col1, col2) = (subquery)

4. 查詢與 ALLEN 同職位且薪資高於 7521 號員工的人員

SELECT * FROM emp e 
WHERE e.job = (SELECT job FROM emp WHERE ename = 'ALLEN')
  AND e.sal > (SELECT sal FROM emp WHERE empno = 7521);

5. 查詢與 SCOTT 同職位且同薪資的員工 (排除 SCOTT 本人)

SELECT * FROM emp e 
WHERE (e.job, e.sal) = (SELECT job, sal FROM emp WHERE ename = 'SCOTT')
  AND e.ename <> 'SCOTT';

6. 查詢與 7521 號員工同職位且同主管的人員

SELECT * FROM emp e 
WHERE (e.job, e.mgr) = (SELECT job, mgr FROM emp WHERE empno = 7521);

7. 查詢與 ALLEN 同職位且同年入職的人員 (含 ALLEN)

SELECT * FROM emp e 
WHERE (e.job, TO_CHAR(e.hiredate, 'YYYY')) = 
      (SELECT job, TO_CHAR(hiredate, 'YYYY') FROM emp WHERE ename = 'ALLEN');

三、多行單列子查詢 — 使用集合操作符 (IN, ANY, ALL)

8. 查詢薪資等於各部門最低薪資的員工 (IN)

SELECT * FROM emp e 
WHERE e.sal IN (SELECT MIN(sal) FROM emp GROUP BY deptno);

9. 查詢薪資不等於各部門最低薪資的員工 (NOT IN)

SELECT * FROM emp e 
WHERE e.sal NOT IN (SELECT MIN(sal) FROM emp GROUP BY deptno);
-- ⚠️ 陷阱：若子查詢結果含 NULL，NOT IN 永遠返回 FALSE (無資料)。建議用 NOT EXISTS。

四、ANY / SOME 操作符 — 與集合中「任一」值比較

• = ANY 等價於 IN
• > ANY 大於集合中最小值 (即 > MIN)
• < ANY 小於集合中最大值 (即 < MAX)

10. 查詢薪資等於任一部門經理薪資的員工

SELECT * FROM emp 
WHERE sal = ANY (SELECT MIN(sal) FROM emp WHERE job = 'MANAGER' GROUP BY deptno);

11. 查詢薪資大於任一部門經理薪資的員工

SELECT * FROM emp 
WHERE sal > ANY (SELECT MIN(sal) FROM emp WHERE job = 'MANAGER' GROUP BY deptno);
-- 即薪資 > 最低經理薪資

12. 查詢薪資小於任一部門經理薪資的員工

SELECT * FROM emp 
WHERE sal < ANY (SELECT MIN(sal) FROM emp WHERE job = 'MANAGER' GROUP BY deptno);
-- 即薪資 < 最高經理薪資

五、ALL 操作符 — 與集合中「所有」值比較

• <> ALL 等價於 NOT IN (但 = ALL 不等價於 IN)
• > ALL 大於集合中最大值 (即 > MAX)
• < ALL 小於集合中最小值 (即 < MIN)

13. 查詢薪資不等於所有部門經理薪資的員工

SELECT * FROM emp 
WHERE sal <> ALL (SELECT MIN(sal) FROM emp WHERE job = 'MANAGER' GROUP BY deptno);

14. 查詢薪資小於所有部門經理薪資的員工

SELECT * FROM emp 
WHERE sal < ALL (SELECT MIN(sal) FROM emp WHERE job = 'MANAGER' GROUP BY deptno);

15. 查詢薪資大於所有部門經理薪資的員工

SELECT * FROM emp 
WHERE sal > ALL (SELECT MIN(sal) FROM emp WHERE job = 'MANAGER' GROUP BY deptno);

六、EXISTS / NOT EXISTS — 關聯子查詢判斷結果集是否為空

• EXISTS (subquery)：子查詢返回至少一行 → TRUE。常用於關聯子查詢 (相關子查詢)。
• NOT EXISTS：子查詢無資料返回 → TRUE。不受 NULL 影響，是 NOT IN 的安全替代。

16. 驗證 EXISTS (子查詢無資料 → 無輸出)

SELECT * FROM emp 
WHERE EXISTS (SELECT * FROM emp WHERE empno = 9999); -- 無此員工，無輸出

17. EXISTS 返回 TRUE (子查詢有資料 → 全表輸出)

SELECT * FROM emp 
WHERE EXISTS (SELECT * FROM emp); -- 有資料，返回所有員工

18. NOT EXISTS 返回 FALSE (子查詢有資料 → 取反無輸出)

SELECT * FROM emp 
WHERE NOT EXISTS (SELECT * FROM emp); -- 有資料，取反無輸出

關鍵總結

1. 子查詢返回類型決定可用操作符：單值用比較運算子，多值用 IN/ANY/ALL，存在性判斷用 EXISTS。
2. NOT IN 遇 NULL 失效，務必改用 NOT EXISTS 或 <> ALL。
3. ANY/ALL 常配合聚合函數 (MIN/MAX) 簡化邏輯：> ANY = > MIN, < ALL = < MIN。
4. 相關子查詢 (EXISTS 子查詢引用外部表欄位) 通常效能優於非相關 IN，因可利用半連結優化。