[DB이야기] Outer Join

 

1. Outer Join 이란? 

SQL를 작성하면서 Outer Join이 어쩔 수 없이 발생할 때가 있습니다.
모델링 단계에서 가급적 Outer Join이 발생하지 않도록 하는것이 더 좋겠죠.

일단 Outer Join이 꼭 필요해서 사용할 경우 오라클과 ANSI SQL를 사용할때 많이 햇갈리는 경우가 있습니다.

Outer Join이란 두개의 집합(Table)을 Join으로 연결할 경우. 한쪽 집합을 기준으로 다른 집합에는 없어도 보여주고 싶을 경우에
사용합니다. 즉 A, B 집합이 있을 경우 A를 기준으로하고 B에는 A에 있는 집합은 당연히 나오고 없으면 null로 나오도록 하는 것입니다.

이때 기준이 되는 집합을 Outer라 하고 그 반대쪽을 Inner라 합니다.

t_emp가  Outer Table이고  t_dept가 Inner Table일 경우

[오라클]
select a.id, a.name, b.dept_nm
   from t_emp a, t_dept b
where a.dept_cd = b.dept_cd(+)   <-- Outer Sign을 Inner Table쪽에 명시

주) inner table의 컬럼이 조건절에 추가로 나오면 모두 (+) 를 명시해야 합니다.

    조인조건이 아니라 필터 조건에 (+) 붙이기 싫다면 OR로 IS NULL 조건을 추가로 주면 됩니다.

[ANSI SQL] 

// Oracle 9i이부터는 ANSI SQL Outer Join Syntax를 지원합니다.
select a.id, a.name, b.dept_nm
   from t_emp a left outer join  t_dept b 
        on a.dept_cd = b.dept_cd
주)ANSI SQL에서 left outer join을 사용할지 right outer join을 사용할지 햇갈리는 경우가 많은데
   매우 간단하게 알 수 있습니다. 바로 Outer Table이 어느쪽에 있느냐 입니다.
   위경우에 t_emp 테이블이 Outer Table이고 from 절에서 왼쪽에 위치함으로 left outer join으로 한 것입니다.
   ANSI SQL OUTER JOIN에서 주의할 점은 inner table에 대한 조건이 있을 경우 반드시 on 절에 기술해야 합니다
   where 절에 b.dept_nm = '기술본부' 이런식으로 하면 안됩니다. 반드시 on 절에 기술해야 한다는 것을 기억하세요.
   반면에 Outer Table의 컬럼의 조건은 where 절에 기술해야 합니다.

즉 조인조건은  on절에,  outer table의 조건은 where절, inner table의 조건은 on절에 기술해야 합니다.

PS). Outer Join에서 Inner Table은 Driving Table이 될 수 없습니다.
       무조건 Outer Table이 Driving 된다는것도 기억해 두십시오. 

 

2. Full Outer Join

 오라클 8i까지 Full Outer Join에 대한 이슈가 있을 경우 UNION으로 해결했습니다.

SELECT d.dept_id, d.name, l.regional_group
FROM department d, location l
WHERE d.location_id (+) = l.location_id
UNION
SELECT d.dept_id, d.name, l.regional_group
FROM department d, location l
WHERE d.location_id = l.location_id (+) ;

   DEPT_ID NAME                 REGIONAL_GROUP
---------- -------------------- ----------------
        10 ACCOUNTING           NEW YORK
        20 RESEARCH             DALLAS
        30 SALES
        40 OPERATIONS           BOSTON
                                CHICAGO
                                SAN FRANCISCO

그런데 오라클 9i이부터는 Full Outer Join이 지원이 됩니다.

 

SELECT d.dept_id, d.name, l.regional_group
FROM department d FULL OUTER JOIN location l
ON d.location_id = l.location_id;


   DEPT_ID NAME                 REGIONAL_GROUP
---------- -------------------- ----------------
        10 ACCOUNTING           NEW YORK
        20 RESEARCH               DALLAS
        30 SALES
        40 OPERATIONS           BOSTON
                                          CHICAGO
                                          SAN FRANCISCO