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

I want to writ开发者_运维知识库e 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.

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

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

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.

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

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

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

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.

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

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

Find department_id for departments that do not contain the job_id ST_MAN