'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.
Solution 1:[1]
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
Solution 2:[2]
select dname from dept where deptno not in (select deptno from emp)
Solution 3:[3]
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.
Solution 4:[4]
SELECT D.DEPTNO
FROM EMP E
JOIN DEPT D ON D.DEPTNO = E.DEPTNO (+)
WHERE E.EMPNO IS NULL;
Solution 5:[5]
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);
Solution 6:[6]
Select DName
from DEPT
where DName NOT IN (Select Distinct EMP.DName from EMP);
Solution 7:[7]
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.
Solution 8:[8]
SELECT ID,NAME,SAL,DEPTNAME,DEPTID
FROM emp
FULL JOIN
DEPT
ON EMP.departmentid=DEPT.DEPTID
WHERE DEPTID IS NULL
Solution 9:[9]
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
Solution 10:[10]
Find department_id for departments that do not contain the job_id ST_MAN
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow