kaibuki kaibuki - 1 month ago 8
SQL Question

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.

Thanks

gbn gbn
Answer

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