Oracle 查詢每部門最低薪資員工：關聯子查詢 vs IN 陷阱深度解析

Oracle 查詢每部門最低薪資員工：關聯子查詢 vs IN 陷阱深度解析

需求

查詢每個部門薪資最低的員工完整信息。

方法一：關聯子查詢（標準正確寫法）

步驟 1：部門最低薪資

SELECT deptno, MIN(sal) min_sal FROM emp GROUP BY deptno;

步驟 2：關聯主表取員工信息

SELECT e.*
FROM emp e,
     (SELECT deptno, MIN(sal) min_sal FROM emp GROUP BY deptno) s
WHERE e.deptno = s.deptno AND e.sal = s.min_sal;

方法二：IN 子查詢（隱含陷阱）

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

陷阱重現：跨部門同薪資導致錯誤結果

1. 修改員工 1234 部門為 20，薪資保持不變（假設等於部門 10 的最低薪資）。
2. 再次執行 IN 查詢：

員工 1234 屬於部門 20，但薪資等於部門 10 的最低薪資，被錯誤選中。

關聯子查詢免疫該陷阱

關聯條件 e.deptno = s.deptno 確保「部門內最低薪資」與「員工所在部門」強綁定，跨部門同薪值不會誤匹配。

根因本質

最佳實踐原則

1. 分組極值查詢必用關聯子查詢 / 窗口函數，拒絕 IN (SELECT 聚合函數 ...)。
2. 窗口函數寫法 (Oracle 9i+)：

   SELECT * FROM (
       SELECT e.*, ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal) rn
       FROM emp e
   ) WHERE rn = 1;

   性能最優、語義清晰、天然去重（同薪取第一行）。
3. 同薪資多員工業務決策：取第一個 / 全部列出 / 聚合姓名，在窗口函數 ORDER BY sal, empno 或 LISTAGG 中體現。

擴展：最高薪資、前 N 名

• 最高薪資：ORDER BY sal DESC 或 MAX(sal) 關聯子查詢。
• 每部門前 3 高薪：ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) <= 3。

「關聯子查詢綁定維度，IN 子查詢丟失維度」——這一認知能避開 90% 分組極值查詢的邏輯坑。