如何优化查询无女性员工部门的SQL语句？

我们有两张表：

• Department（部门表）
• Emp（员工表）

表结构及数据初始化SQL如下：

CREATE TABLE department
( 
    deptno int NOT NULL,
    dname  varchar2(50) NOT NULL,
    loc    varchar2(13)
);

INSERT INTO DEPARTMENT (DEPTNO, DNAME, LOC) 
VALUES (10, 'ACCOUNTING', 'NEW YORK');

INSERT INTO DEPARTMENT (DEPTNO, DNAME, LOC) 
VALUES (20, 'RESEARCH', 'DALLAS');

INSERT INTO DEPARTMENT (DEPTNO, DNAME, LOC) 
VALUES (30, 'SALES', 'CHICAGO');

INSERT INTO DEPARTMENT (DEPTNO, DNAME, LOC) 
VALUES (40, 'OPERATIONS', 'BOSTON');

CREATE TABLE emp
(   
    empno  number(4,0),   
    ename  varchar2(10),
    sex    varchar2(10),
    deptno number(2,0)
);

-- 注：修正原插入语句的字段数量不匹配、重复empno问题
INSERT INTO emp   
VALUES (7839, 'KING', 'Male',  10);
INSERT INTO emp   
VALUES (7898, 'BLAKE', 'Male',  30);
INSERT INTO emp   
VALUES (7782, 'CLARK', 'Female',  10);
INSERT INTO emp   
VALUES (7566, 'JONES', 'Female',  20);
INSERT INTO emp   
VALUES (7788, 'SCOTT', 'Male',  20);
INSERT INTO emp   
VALUES (7902, 'FORD', 'Male',  20);
INSERT INTO emp   
VALUES (7369, 'SMITH', 'Female',  20);
INSERT INTO emp   
VALUES (7499, 'Allen', 'Male',  40);

COMMIT;

你的现有实现SQL：

select deptno 
from emp 
where deptno not in (select deptno 
                     from emp 
                     where sex = 'Female');

更优实现方式 #

1. 使用 NOT EXISTS（推荐，规避NULL陷阱）

NOT IN 在子查询返回NULL值时会导致结果集为空，而NOT EXISTS无此问题，且在emp(deptno, sex)有索引时性能更优：

SELECT DISTINCT e.deptno
FROM emp e
WHERE NOT EXISTS (
    SELECT 1 
    FROM emp e2 
    WHERE e2.deptno = e.deptno 
      AND e2.sex = 'Female'
);

如果需要包含无任何员工的部门（比如部门40无员工时也属于无女性员工范畴），关联department表查询：

SELECT d.deptno, d.dname
FROM department d
WHERE NOT EXISTS (
    SELECT 1 
    FROM emp e 
    WHERE e.deptno = d.deptno 
      AND e.sex = 'Female'
);

2. 分组聚合筛选

通过分组统计各部门女性员工数量，直接筛选数量为0的部门：

SELECT deptno
FROM emp
GROUP BY deptno
HAVING COUNT(CASE WHEN sex = 'Female' THEN 1 END) = 0;

若要包含无员工的部门：

SELECT d.deptno, d.dname
FROM department d
LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno, d.dname
HAVING COUNT(CASE WHEN e.sex = 'Female' THEN 1 END) = 0;

3. 左连接+空值筛选

通过左连接女性员工所在部门，筛选未匹配到的部门：

SELECT DISTINCT d.deptno, d.dname
FROM department d
LEFT JOIN emp e ON d.deptno = e.deptno AND e.sex = 'Female'
WHERE e.deptno IS NULL;

各方式对比 #

• NOT EXISTS：逻辑清晰，性能稳定，彻底避免NULL问题，是工业界常用的最优方案。
• 分组聚合：适合需同时统计部门其他指标的场景，可读性较强。
• 左连接筛选：直观易懂，适合新手理解，有索引时性能表现良好。

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