Oracle中INTERSECT集合运算符的替代方案及查询效率优化咨询

方案1：和原查询逻辑完全一致的优化版 #

你原来的INTERSECT写法需要对emp1表执行2次全表扫描、2次分组运算，再对两个结果集做交集计算，数据量越大性能损耗越高。
可以直接用一次GROUP BY + HAVING聚合实现完全相同的逻辑，仅需扫描1次表，执行效率提升明显：

SELECT empno, deptno
FROM emp1
GROUP BY empno, hiredate, deptno
HAVING MIN(sal) < 2000 AND MAX(sal) > 2000;

逻辑说明：只要同一员工存在至少一次薪资低于2000的记录、同时存在至少一次薪资高于2000的记录，就会被筛选出来，和你原SQL的返回结果完全一致。

方案2：严格匹配「入职时薪资低于2000、当前薪资高于2000」的业务逻辑 #

如果你的业务要求不是只要存在高低薪资记录，而是严格限定低薪资是入职时的首笔薪资、高薪资是当前最新薪资，可以用窗口函数实现，同样仅需扫描1次表：

WITH emp_sal_rank AS (
    SELECT 
        empno,
        deptno,
        sal,
        ROW_NUMBER() OVER(PARTITION BY empno ORDER BY hiredate ASC) AS rn_hire, -- 入职记录标记为1
        ROW_NUMBER() OVER(PARTITION BY empno ORDER BY hiredate DESC) AS rn_current -- 当前最新记录标记为1
    FROM emp1
)
SELECT empno, deptno
FROM emp_sal_rank
WHERE (rn_hire = 1 AND sal < 2000) OR (rn_current = 1 AND sal > 2000)
GROUP BY empno, deptno
HAVING COUNT(*) = 2;

额外性能优化建议 #

可以给emp1表创建联合索引 (empno, hiredate, deptno, sal)，实现查询的索引覆盖，避免回表查询数据，执行效率会进一步提升。

内容的提问来源于stack exchange，提问作者Lara