[ORACLE] NOT IN vs NOT EXISTS 차이점

 

DO IT 오라클 책을 공부하다 아래 문제가 나왔다. 

10 번 부서에서 근무하는 사원 중 30번 부서에는 존재하지 않는 직책을 가진 사원들의 사원 정보, 부서 정보를 출력하는 sql문을 작성하라.

 

 

존재하지 않는다라,, not exists 사용하면 되지 않을까? 생각했었는데 not in을 사용했어야 했다. 둘은 어떤 차이가 있을까? 

 

select * 
from emp e
where e.deptno = 10 
and not EXISTS (select job
                from emp
                where deptno = 30)
;

 

 

일단 in과 exists는 다중행 연산자이다. 다중행 연산자의 종류는 아래와 같다.

 

다중행 연산자	설명
IN	메인쿼리의 데이터가 서브 쿼리의 결과 중 하나라도 일치한 데이터가 있다면 true  (즉, 서브쿼리의 결과가  조건으로 들어가 조건이 일치하는 데이터를 출력해준다. )
ANY, SOME	메인쿼리의 조건식을 만족하는 서브쿼리의 결과가 하나 이상이면 true
ALL	메인쿼리의 조건식을 서브쿼리의 결과 모두가 만족하면 true
EXISTS	서브쿼리의 결과가 존재하면 true

 

 

 

📑 IN : 레코드가 IN 절의 결과 리스트와 동일한 값을 가지는지로 T/F 반환

 

동작 순서 : 서브 -> 메인 

 

메인 쿼리 IN ( 서브쿼리 ) : 서브 쿼리의 결과 리스트가 메인 쿼리의 레코드와 동일한 값을 가지면 TRUE, 없으면 FALSE를 반환한다. 메인쿼리에 먼저 접근하여 컬럼값들을 가져와 리스트로 IN 이하에 뿌려주고, 그 이후에 서브쿼리에서 하나의 레코드씩 IN 이하의 요소들과 일치하는지 비교한다. 

 

IN 뒤에 있는 괄호 서브쿼리를 먼저 실행해서 그에 대한 요소를 가져온다. 메인 쿼리에서 하나의 레코드를 가져오며 그 레코드의 값이 앞에서 가져온 IN 이하의 요소들에 포함되어 있는지 체크한다.

 

만약 IN 이하의 요소들 중 하나라도 일치한다면 그 레코드를 출력한다. 

 

 

 

 

🎈 NOT IN 

 

: NOT IN 안의 서브쿼리가 모두 리스트로 연산되고 나면 메인 쿼리의 레코드를 가져올 것이고 IN이 아닌 NOT IN이기 때문에 요소들과 일치하지 않아야 결과로 반환된다. 

 

 

 

📑 EXISTS : 단순히 EXISTS 절의 결과 존재 유무만으로 T/F 반환

 

동작 순서 : 메인 -> 서브

 

메인 쿼리 EXISTS ( 서브쿼리 ) : 서브 쿼리의 결과가 한 건이라도 존재하면 TRUE 없으면 FALSE를 리턴한다. 

먼저 메인쿼리에 접근하여 하나의 레코드를 가져오고 그 레코드에 대해 EXISTS 이하의 서브 쿼리를 실행하고 서브쿼리에 대한 결과가 존재하는지 확인한다.

 

만약 값이 존재한다면 메인쿼리의 모든 행을 출력한다. 

 

EXISTS는 서브 쿼리에 일치하는 결과가 한 건이라도 있으면 쿼리를 더 이상 수행하지 않는다.

 

 

IN은 서브 쿼리 결과를 모두 수행하고 서브쿼리의 값과 일치하는 레코드를 출력한다 , EXISTS는 일치하는 결과가 있으면 더 이상 수행하지 않고 해당 테이블의 모든 데이터를 출력한다. 

 

 

 

 

🎈 NOT EXISTS 

 

: EXISTS 구문은 값이 존재할 때 해당 레코드를 출력하지만, NOT EXISTS 구문은 서브쿼리의 값이 존재하지 않으면 해당 레코드를 출력한다. 

 

 

 

 

---

 

10 번 부서에서 근무하는 사원 중 30번 부서에는 존재하지 않는 직책을 가진 사원들의 사원 정보, 부서 정보를 출력하는 sql문을 작성하라.

 

 

따라서 위의 문제는  10번 부서에는 존재하는 직책이지만, 30번 부서에는 존재하지 않는 직책을 가진 사원 정보를 출력해야 하므로 조건과 일치하면 메인쿼리의 값을 출력하는 IN을 사용해야 한다. 

 

 

SELECT E.EMPNO, E.ENAME, E.JOB, E.DEPTNO, D.DNAME, D.LOC
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO
   AND E.DEPTNO = 10
   AND JOB NOT IN (SELECT DISTINCT JOB
                     FROM EMP
                    WHERE DEPTNO = 30);