To take out those dept who has no employees assigned to it

Question

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.

Table Structure:

EMP
EMPNO   ENAME    DEPTNO

DEPT
DEPTNO   DNAME

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

you may either use a left join and check for NULLs or you can use a sub query — bernd_k, Dec 28 '10 at 11:42

gbn · Accepted Answer · 2010-12-28T11:48:49.627

9

It's only correct with 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

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

So generally one should use NOT EXISTS or EXCEPT

edited Dec 28 '10 at 11:48

answered Dec 28 '10 at 11:42

gbn

422,506
82
585
676

Could you add info about which dbms you tried? On Oracle, the first query will generate an anti join (hash or nl depending on data distribution) whereas the second query causes results in two sorts. – Ronnis Dec 28 '10 at 12:28
@Ronnis: SQL server. Based on experience and this http://stackoverflow.com/questions/4249891/combining-datasets-with-except-versus-checking-on-is-null-in-a-left-join/4249958#4249958 – gbn Dec 28 '10 at 12:31

score 4 · Answer 2 · answered Dec 28 '10 at 11:40

4

select dname from dept where deptno not in (select deptno from emp)

answered Dec 28 '10 at 11:40

Oli

2,370
2
26
42

1

Let's hope every employee has a department. CEO may not have a department and deptno could be NULL = fail... – gbn Dec 28 '10 at 11:51

Marek Grzenkowicz · Answer 3 · 2010-12-28T12:13:57.097

4

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 DISTINCT is not necessary (SELECT ~~DISTINCT~~ D.DNAME ...) in this case - even without it no duplicate departments will be returned.

edited Dec 28 '10 at 12:13

answered Dec 28 '10 at 11:41

Marek Grzenkowicz

17,024
9
81
111

1

@bernd_k: you'd get one row per EMP. So you need DISTINCT. Which was added after on a quick update so does not show as an edit... – gbn Dec 28 '10 at 11:45
1

@gbn but those where excluded by the where clause. I get no duplicates for the unjoined departments. DISTINCT is not needed – bernd_k Dec 28 '10 at 11:51
1

@bernd_k: this is actually a special case. Generally, you usually need DISTINCT to compensate for multiple rows caused by the JOIN. – gbn Dec 28 '10 at 11:52
@gbn In those cases, when I only select columns from the left table I prefer the where in ... construct, to avoid DISTINCT – bernd_k Dec 28 '10 at 11:56
@gbn admitted, but the case to avoid distinct is the where x in (select y ...) case and that is reliable. Possible Nulls in subselect don't matter. – bernd_k Dec 28 '10 at 12:09

score 1 · Answer 4 · edited Jul 02 '13 at 09:18

1

SELECT D.DEPTNO
FROM EMP E
JOIN DEPT D ON D.DEPTNO = E.DEPTNO (+)
WHERE E.EMPNO IS NULL;

edited Jul 02 '13 at 09:18

Radim Köhler

122,561
47
239
335

answered Jul 02 '13 at 09:02

nuebe

11
1

score 1 · Answer 5 · edited Mar 13 '20 at 09:52

1

Select DName 
from DEPT
where DName NOT IN (Select Distinct EMP.DName from EMP);

edited Mar 13 '20 at 09:52

Marek Grzenkowicz

17,024
9
81
111

answered Nov 02 '15 at 06:30

Lucky Rana

1,030
1
12
20

"In" clause has the limit which vary around the different databases. – Vaibs Apr 26 '21 at 08:43

Karol Borkowski · Answer 6 · 2016-11-01T22:13:22.597

1

You can select these departments from dept table whom numbers are not present in emp table:

SELECT dname
FROM dept 
WHERE deptno 
NOT IN (SELECT DISTINCT deptno 
FROM emp);

edited Nov 01 '16 at 22:13

answered Nov 01 '16 at 21:45

Karol Borkowski

532
7
19

1

Please update with a short explanation for what your code does) – junkfoodjunkie Nov 01 '16 at 22:07
@junkfoodjunkie – Karol Borkowski Nov 01 '16 at 22:34

score 0 · Answer 7 · edited Feb 21 '18 at 22:12

0

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 in will give the employees who do not belong to any department.

edited Feb 21 '18 at 22:12

Markus

2,071
4
22
44

answered Feb 21 '18 at 07:14

Sonia Jain

143
2
10

Thank you for this code snippet, which might provide some limited short-term help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its long-term value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please [edit] your answer to add some explanation, including the assumptions you've made. – Toby Speight Feb 21 '18 at 09:33

score 0 · Answer 8 · edited Mar 13 '20 at 09:51

0

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

edited Mar 13 '20 at 09:51

Marek Grzenkowicz

17,024
9
81
111

answered Feb 22 '18 at 06:33

Sonia Jain

143
2
10

score 0 · Answer 9 · edited Mar 15 '19 at 18:08

0

SELECT ID,NAME,SAL,DEPTNAME,DEPTID
FROM emp
FULL JOIN
DEPT
ON EMP.departmentid=DEPT.DEPTID
WHERE DEPTID IS NULL

edited Mar 15 '19 at 18:08

Dmitry

6,716
14
37
39

answered Mar 15 '19 at 17:46

SIVA KRISHNA

1

1

Please provide a more detailed answer rather than just a piece of code. – phen0menon Mar 15 '19 at 17:56

score 0 · Answer 10 · answered Apr 18 '22 at 10:10

0

Find department_id for departments that do not contain the job_id ST_MAN

answered Apr 18 '22 at 10:10

Gem Ali

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Apr 18 '22 at 10:38

To take out those dept who has no employees assigned to it

10 Answers10

Linked