문제: 부서 번호가 30, 60, 90인 직원 출력하기
(1) IN 연산자 활용시
select department_id, employee_id from employees
where department_id in (30,60,90)
order by 1, 2;
(2) EXISTS 연산자 활용 시
select emp.department_id, emp.employee_id
from employees emp
where exists
(select dep.department_id
from departments dep
where dep.department_id in (30, 60, 90)
and emp.department_id=dep.department_id)
order by 1, 2;
--> 변환(동일한 결과값 출력됨)
(3) EXISTS 연산자 활용 시
select emp.department_id, emp.employee_id
from employees emp
where exists
(select 1
from departments dep
where dep.department_id in (30, 60, 90)
and emp.department_id=dep.department_id)
order by 1, 2;
<EXISTS의 특징>
(1)EXISTS는 오직 서브쿼리만 쓸 수 있다.
(2)서브쿼리에서 조인을 사용한다.
(3)EXISTS는 서브쿼리의 결과가 있느냐 없느냐만 체크한다.
(IN 이 WHERE 조건절에 해당하는 레코드의 컬럼값을 출력하는 것과는 대비하여-)
*EXISTS는 존재하느냐 존재하지 않느냐의 여부만 체크하기 때문에 서브쿼리의 SELECT 리스트에는 등장하는 값과는 상관없이 서브쿼리의 결과로 반환되는 로우가 있느냐 없느냐만 중요한 것이다.
<NOT IN과 NOT EXISTS>
IN과 EXISTS는 동일한 결과값을 출력한다.
반면 NOT IN과 NOT EXISTS는 결과값이 다를 수 있다. 바로 NOT IN 은 NULL 값을 포함하지 않기 때문이다.
예제)
department_id 가 null인 레코드 1건 존재-->
select employee_id, department_id
from employees
where department_id is null;
1개 recored
NOT IN -->
select department_id, employee_id from employees
where department_id not in (30,60,90)
order by 1, 2;
92개 records
NOT EXISTS -->
select emp.department_id, emp.employee_id
from employees emp
where not exists
(select 1
from departments dep
where dep.department_id in (30, 60, 90)
and emp.department_id=dep.department_id)
order by 1, 2;
93개 records
※차이가 발생하는 이유
IN은 OR이다.
department_id IN (30, 60, 90) --> department_id = 30 or department_id = 60 or department_id = 90 으로 바꿔 쓸 수 있다.
NOT IN의 경우 (AUB)^C 이므로 A^C교집합B^C으로 쓸 수 있다.
department_id NOT IN (30, 60, 90) --> (a)department_id <> 30 and (b)department_id <> 60 and (c)department_id <> 90 으로 바꿔 쓸 수 있다.
문제는 null 값은 '=', '<>' 연산을 했을때 TRUE/FALSE를 반환하지 않고 UNKNOWN을 반환한다는 것에 있다. 따라서 department_id가 null인 178번 사원은 (a), (b), (c) 어디에도 포함되지 않고, 따라서 반환되지 않는다.
반면 EXISTS의 경우 해당 로우의 존재여부만 체크하기 때문에, NOT EXISTS는 department_id 값이 30, 60, 90이 아닌 모든 로우를 조회하게 되며, 따라서 department_id가 null인 값도 출력에 포함되는 것이다.
<IN과 EXISTS의 활용>
컬럼값에 대해 비교할 값이 문자나 숫자 등의 상수 리스트를 사용할 경우 IN을 사용하도록 하고, 만약 서브쿼리를 사용한다면 IN보다는 EXISTS를 사용하는 것이 훨씬 성능이 좋다.
* 뇌를 자극하는 오라클 프로그래밍 (홍형경 저) 참조함.