안티 조인

안티란 말에서 유추할 수 있듯이 안티 조인(ANTI-JOIN)은 서브 쿼리의 B 테이블에는 없는 메인 쿼리의 A 테이블의 데이터만 추출하는 조인 방법이다. 한쪽 테이블에만 있는 데이터를 추출하는 것이므로 조회 조건에서 NOT IN이나 NOT EXISTS 연산자를 사용한다. 즉 세미 조인과 반대 개념이다.

입력

    SELECT a.employee_id, a.emp_name, a.department_id, b.department_name
      FROM employees a,
           departments b
     WHERE a.department_id = b.department_id
       AND a.department_id NOT IN ( SELECT department_id
                                      FROM departments
                                     WHERE manager_id IS NULL) ;

결과

    EMPLOYEE_ID EMP_NAME          DEPARTMENT_ID DEPARTMENT_NAME
    ----------- ----------------- ------------- --------------------------------
    198         Donald OConnell   50            배송부
    199         Douglas Grant     50            배송부
    200         Jennifer Whalen   10            총무기획부
    201         Michael Hartstein 20            마케팅
    ...
    106개의 행이 선택됨.

앞의 쿼리는 manager_id 값이 있는 부서에 속한 사원 테이블을 조회한 것이다. 여기서 주의할 점은 “departments b”를 조인한 부분은 해당 사원에 대한 부서명을 가져오기 위한 조인이고, 안티 조인은 NOT IN 절 이하의 서브 쿼리에 명시했다.

위 쿼리를 다음과 같이 NOT EXISTS로도 변환할 수 있다.

입력

    SELECT count(*)
      FROM employees a
     WHERE NOT EXISTS ( SELECT 1
                          FROM departments c
                         WHERE a.department_id = c.department_id
                           AND manager_id IS NULL) ;

결과

    COUNT(*)
    ----------
    107

그런데 좀 이상하다. 왜 106건이 아닌 107건일까? 이유는 사원 테이블에서 부서번호가 NULL인 사원이 1명 존재(178번 사원)하기 때문이다. 사실 이 한 건을 제외하고 사원 테이블에는 부서 테이블에서 manager_id 값이 있는 부서만 들어가 있다. 따라서 manager_id 값이 NULL인 부서에 속하는 사원은 단 한 명도 존재하지 않으며 IN이나 EXISTS를 사용했다면 위의 두 쿼리 결과 건수는 모두 0건이다. 이 논리대로 한다면 IN 대신 NOT IN을 사용한다면 모든 사원이 조회되어야 한다. 하지만 조인 조건에 참여하는 사원 테이블의 department_id 값이 NULL이면 NOT IN과의 연산 결과가 FALSE가 되어 106건이 된 것이다. 반면 NOT EXISTS는 조인 조건이 서브 쿼리 내에 존재하고 178 번 사원도 조인 결과가 FALSE가 되며 NOT으로 인해 쿼리 전체는 TRUE가 되므로 107건이 조회된 것이다.