Oracle查询：如何获取给定deptno集合中不存在于Emp1表的记录数

你之前使用的SELECT * FROM emp1 WHERE deptno NOT IN(deptno1....deptno100)语句逻辑为查询Emp1表中deptno不属于指定集合的记录，和你需要的「统计指定100个deptno中未出现在Emp1表的数量」逻辑完全相反，因此结果不符合预期。

正确实现方法 #

方法1：手动构造临时行集（通用方案，兼容所有Oracle版本） #

SELECT COUNT(*) AS missing_dept_count
FROM (
    -- 构造待校验的100个deptno集合
    SELECT 'deptno1' deptno FROM DUAL UNION ALL
    SELECT 'deptno2' deptno FROM DUAL UNION ALL
    -- 按上述格式补充剩余97个deptno，最后一行不需要加UNION ALL
    SELECT 'deptno100' deptno FROM DUAL
) target_dept
WHERE NOT EXISTS (
    SELECT 1 FROM Emp1 e WHERE e.deptno = target_dept.deptno
);

方法2：内置集合函数简化写法（适合Oracle 10g及以上版本） #

如果待校验的deptno为字符串类型，可直接用SYS.ODCIVARCHAR2LIST批量构造集合：

SELECT COUNT(*) AS missing_dept_count
FROM TABLE(SYS.ODCIVARCHAR2LIST('deptno1','deptno2',...,'deptno100')) t
WHERE NOT EXISTS (
    SELECT 1 FROM Emp1 e WHERE e.deptno = t.column_value
);

如果deptno为数值类型，将SYS.ODCIVARCHAR2LIST替换为SYS.ODCINUMBERLIST，同时去掉集合元素的单引号即可。

说明 #

• 推荐优先使用NOT EXISTS实现，避免Emp1表deptno字段存在空值时，NOT IN返回异常结果的问题
• 若待校验的deptno集合存储在其他业务表中，直接替换上述语句中的待校验集合构造部分即可，无需手动逐个写UNION ALL

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