SQL 去掉那些没有分配到它的员工的部门
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4545742/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
To take out those dept who has no employees assigned to it
提问by kaibuki
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.
我想编写一个 sql 查询，并想从在 EMP 表中没有分配员工的 DEPT 表中获取部门名称。
Table Structure:
表结构：
EMP
EMPNO ENAME DEPTNO
DEPT
DEPTNO DNAME
So I like to know those DEPT who has no employees association.
所以我喜欢认识那些没有员工协会的DEPT。
回答by gbn
It's only correctwith NOT EXISTS
只有NOT EXISTS才是正确的
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
或 EXCEPT，在这种情况下更复杂
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:
关于其他答案的注释：
A LEFT JOIN will give one row per employee. You'd need DISTINCT. Which compromises the plan compared with NOT EXISTS
NOT IN will give false results if there is an Employee who has no Department. NOT IN with a NULL in the list fails
LEFT JOIN 将为每位员工分配一行。你需要 DISTINCT。与 NOT EXISTS 相比，哪个妥协了计划
如果员工没有部门，则 NOT IN 将给出错误结果。NOT IN 与列表中的 NULL 失败
So generallyone should use NOT EXISTS or EXCEPT
所以通常应该使用 NOT EXISTS 或 EXCEPT
回答by Oli
select dname from dept where deptno not in (select deptno from emp)
回答by Marek Grzenkowicz
SELECT D.DNAME
FROM DEPT D
LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO
WHERE E.DEPTNO IS NULL
UPDATE:
更新：
@bernd_k pointed out that DISTINCTis not necessary (SELECT DISTINCTD.DNAME ...) in this case - even without it no duplicate departments will be returned.
@bernd_k 指出在这种情况下DISTINCT没有必要（SELECT DISTINCTD.DNAME ...） - 即使没有它也不会返回重复的部门。
回答by nuebe
SELECT D.DEPTNO
FROM EMP E
JOIN DEPT D ON D.DEPTNO = E.DEPTNO (+)
WHERE E.EMPNO IS NULL;
回答by Lucky Rana
Select DName
from DEPT
where DName NOT IN (Select Distinct EMP.DName from EMP);
回答by Karol Borkowski
You can select these departments from dept table whom numbers are not present in emp table:
您可以从部门表中选择编号不在 emp 表中的部门：
SELECT dname
FROM dept
WHERE deptno
NOT IN (SELECT DISTINCT deptno
FROM emp);
回答by Sonia Jain
select x.DEPTNO from dept x where x.DEPTNO not in
(select d.DEPTNO from department d join
employee e where e.deptid=d.DEPTNO)
The sub query is used to get all the employees who are associated with a department:
子查询用于获取与部门关联的所有员工：
select d.DEPTNO from department d join
employee e where e.deptid=d.DEPTNO
and using select x.DEPTNO from dept x where x.DEPTNO
not inwill give the employees who do not belong to any department.
not in会给不属于任何部门的员工。
回答by Sonia Jain
The below is not using any except or not in and performance wise it is better
下面没有使用任何除了或不使用，性能方面它更好
select d.dname
from emp e right
join dept d on e.deptno=d.deptno
group by d.dname
having count(e.empno)=0
回答by SIVA KRISHNA
SELECT ID,NAME,SAL,DEPTNAME,DEPTID
FROM emp
FULL JOIN
DEPT
ON EMP.departmentid=DEPT.DEPTID
WHERE DEPTID IS NULL
