[Oracle] EXISTS 연산자의 활용

문제: 부서 번호가 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를 사용하는 것이 훨씬 성능이 좋다.

* 뇌를 자극하는 오라클 프로그래밍 (홍형경 저) 참조함.