去掉那些没有指派员工的部门 [英] To take out those dept who has no employees assigned to it

问题描述

我想编写一个sql查询，并想从没有在EMP表中分配雇员的DEPT表中获取部门名称.

I want to write an sql query , and want to get the dept name from DEPT table who has no employees assigned in EMP table.

表结构:

EMP
EMPNO   ENAME    DEPTNO

DEPT
DEPTNO   DNAME

所以我想认识那些没有员工协会的DEPT.

So I like to know those DEPT who has no employees association.

推荐答案

只有正确且不存在

SELECT D.DNAME
FROM DEPT D
WHERE
 NOT EXISTS (SELECT * FROM EMP E WHERE D.DEPTNO = E.DEPTNO)

或除此以外，在这种情况下更复杂

or EXCEPT, more complex in this case

SELECT D.DNAME
FROM DEPT D
EXCEPT
SELECT D.DNAME
FROM DEPT D
JOIN 
EMP E WHERE D.DEPTNO = E.DEPTNO

两者都应给出相同的计划(带有左半连接)

Both should give the same plan (with a left anti semi join)

关于其他答案的注意事项:

Notes on other answers:

• 左联接将为每位员工分配一行.您需要DISTINCT.与NOT EXISTS相比，这损害了计划

• A LEFT JOIN will give one row per employee. You'd need DISTINCT. Which compromises the plan compared with NOT EXISTS

如果有没有部门的员工，NOT IN不会给出错误的结果.列表中没有NULL的NOT IN失败

NOT IN will give false results if there is an Employee who has no Department. NOT IN with a NULL in the list fails

因此通常应该使用NOT EXISTS或EXCEPT

So generally one should use NOT EXISTS or EXCEPT

这篇关于去掉那些没有指派员工的部门的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！