Pages

Tuesday, November 18, 2014

Mysql Puzzle(Left Join)-Take out those department who have no employees assigned to it

EMP
EMPNO   ENAME    DEPTNO

DEPT
DEPTNO   DNAME
 
 
1st Way using Subquery
 
SELECT D.DNAME
FROM DEPT D
WHERE 
NOT EXISTS (SELECT * FROM EMP E WHERE D.DEPTNO = E.DEPTNO) 

2nd way using Left Join
 
SELECT D.DNAME
FROM DEPT D
LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO
WHERE E.DEPTNO IS NULL 

No comments:

Post a Comment