② 외부 조인

입력

    SELECT a.department_id, a.department_name, b.job_id, b.department_id
      FROM departments a,
           job_history b
     WHERE a.department_id = b.department_id (+) ;

결과

    DEPARTMENT_ID DEPARTMENT_NAME   JOB_ID     DEPARTMENT_ID
    ------------- ----------------- ---------- -------------
    10            총무기획부
    20            마케팅             MK_REP     20
    30            구매/생산부
    40            인사부
    50            배송부             ST_CLERK   50
    50            배송부             ST_CLERK   50
    60            IT                 IT_PROG    60
    ...
    31개의 행이 선택됨.

10개가 아닌 31개의 결과가 조회되었고 job_history에 없는 부서(10, 30, 40 등)도 모두 조회되었다. 쿼리를 자세히 보면 조인 조건에 (+) 기호가 붙어 있는데, 조인 조건에서 데이터가 없는 테이블의 컬럼에 (+) 기호를 붙이는 것이 바로 외부 조인이다. 당연히 10, 30, 40번 부서는 job_history에 데이터가 없으므로 NULL로 출력되었다. 또 다른 예를 살펴 보자.

입력

    SELECT a.employee_id, a.emp_name, b.job_id, b.department_id
      FROM employees a,
           job_history b
     WHERE a.employee_id = b.employee_id(+)
       AND a.department_id = b.department_id;

결과

    EMPLOYEE_ID EMP_NAME             JOB_ID     DEPARTMENT_ID
    ----------- -------------------- ---------- -------------
    201         Michael Hartstein     MK_REP    20
    122         PayamKaufling         ST_CLERK  50
    176         Taylor                SA_MAN    80
    176         Jonathon Taylor       SA_REP    80
    4개의 행이 선택됨.

데이터는 조회되었는데 뭔가 좀 이상하다. 분명히 (+)를 붙였는데도 모두 조회되어야 하는데 4건만 조회되었다. 왜 그런 것일까? 여기서 또 알아 두어야 할 내용이 있다. 다음과 같이 외부 조인은 조건에 해당하는 조인 조건 모두에 (+)를 붙여야 한다.

입력

    select a.employee_id, a.emp_name, b.job_id, b.department_id
      from employees a,
           job_history b
     where a.employee_id  = b.employee_id(+)
       and a.department_id = b.department_id(+);

결과

    EMPLOYEE_ID EMP_NAME             JOB_ID     DEPARTMENT_ID
    ----------- -------------------- ---------- -------------
    201         MichaelHartstein     MK_REP     20
    122         PayamKaufling        ST_CLERK   50
    176         Jonathon Taylor      SA_REP     80
    176         Jonathon Taylor      SA_MAN     80
    170         Tayler Fox
    150         Peter Tucker
    192         Sarah Bell
    106         ValliPataballa
    134         Michael Rogers
    ...
    108개의 행이 선택됨

원하는 대로 결과가 나왔다. 외부 조인 시 알아야 할 내용을 정리해 보자.

❶ 조인 대상 테이블 중 데이터가 없는 테이블 조인 조건에 (+)를 붙인다

❷ 외부 조인의 조인 조건이 여러 개일 때 모든 조건에 (+)를 붙인다

❸ 한 번에 한 테이블에만 외부 조인을 할 수 있다. 예를 들어, 조인 대상 테이블이 A, B, C 3개이고, A를 기준으로 B 테이블을 외부 조인으로 연결했다면, 동시에 C를 기준으로 B 테이블에 외부 조인을 걸 수는 없다

❹ (+)연산자가 붙은 조건과 OR를 같이 사용할 수 없다

❺ (+)연산자가 붙은 조건에는 IN 연산자를 같이 사용할 수 없다(단 IN절에 포함되는 값이 1개인 때는 사용 가능)