CODEWITHSUNDEEP
sqlselect
Adil Bhatty
Adil Bhatty

Reputation: 17340

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

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.

Upvotes: 4

Views: 92585

Answers (10)

Gem Ali
Gem Ali

Reputation: 1

Find department_id for departments that do not contain the job_id ST_MAN

Upvotes: 0

Lucky Rana
Lucky Rana

Reputation: 1050

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

Upvotes: 1

Sonia Jain
Sonia Jain

Reputation: 153

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

Upvotes: 0

SIVA KRISHNA
SIVA KRISHNA

Reputation: 1

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

Upvotes: 0

Sonia Jain
Sonia Jain

Reputation: 153

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.

Upvotes: 0

Karol Borkowski
Karol Borkowski

Reputation: 742

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);

Upvotes: 1

nuebe
nuebe

Reputation: 11

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

Upvotes: 1

Marek Grzenkowicz
Marek Grzenkowicz

Reputation: 17343

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.

Upvotes: 4

gbn
gbn

Reputation: 432230

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

Upvotes: 9

Oli
Oli

Reputation: 2452

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

Upvotes: 4

Related Questions