ANSI OUTER JOIN 마스터하기...

<< 준비>>  아래 테이블은 테스트하기 위해서 일부러 사원테이블에는 부서코드가 있는데

                 부서 테이블에는 존재하지 않는 데이터를 만들었음.

-----------------------------------------------------------------------------------------

create table tmp_emp

(

 empno number(5),

 empnm varchar(10),

 deptno number(5)

 )

 ;

 

 create table tmp_dept

(

 deptno number(5),

 deptnm varchar2(10)

 )

 ;

insert into tmp_emp values(100, '홍길동',10);

insert into tmp_emp values(200, '이순신',20);

insert into tmp_emp values(300, '안중근',30);

insert into tmp_emp values(400, '유관순',40);

insert into tmp_emp values(500, '김유신',50);

commit;

 insert into tmp_dept values(10,'인사팀');

 insert into tmp_dept values(20,'재무팀');

 insert into tmp_dept values(30,'영업팀');

 commit;

-----------------------------------------------------------------------------------------

1. 조인 조건만 있을 경우

select a.empnm, a.empno, b.deptnm, b.deptno

from tmp_emp a left outer join tmp_dept b on a.deptno  = b.deptno

;

홍길동 100 인사팀 10

이순신 200 재무팀 20

안중근 300 영업팀 30

유관순 400

김유신 500

2. OUTER TABLE의 조건이 ON절에 있을 경우

select a.empnm, a.empno, b.deptnm, b.deptno

from tmp_emp a left outer join tmp_dept b on a.deptno  = b.deptno

                                          and a.empno >= 200

ORDER BY 2

홍길동 100

이순신 200 재무팀 20

안중근 300 영업팀 30

유관순 400

김유신 500

==> OUTER TABLE인 TMP_EMP 테이블의 조건인 a.empno >= 200 이 ON 절에 있을 경우에

      OUTER는 모두 출력되고 조건에 해당되는 사원만 조인이 이루어짐.

      즉 T

3. OUTER TABLE의 조건이 WHERE 절에 있을 경우

select a.empnm, a.empno, b.deptnm, b.deptno

from tmp_emp a left outer join tmp_dept b on a.deptno  = b.deptno

WHERE a.empno >= 200

ORDER BY 2

이순신 200 재무팀 20

안중근 300 영업팀 30

유관순 400

김유신 500

==> 보통 개발자가 기대했던 대로 사번이 200번 이상인 사원중에 해당 부서가 존재하면 부서명이

      출력되고 TMP_DEPT에 해당부서 코드가 존재하지 않으면 NULL로 나옴.

4. INNER TABLE의 조건이 ON 절에 있을 경우

select a.empnm, a.empno, b.deptnm, b.deptno

from tmp_emp a left outer join tmp_dept b on a.deptno  = b.deptno 

                                          and b.deptno >= 20

ORDER BY 2

;

홍길동 100

이순신 200 재무팀 20

안중근 300 영업팀 30

유관순 400

김유신 500

==> 예상했던데로 부서코드가 20번 이상인 부서만 조인이 이루어 짐.

5. INNER TABLE의 조건이 where 절에 있을 경우

select a.empnm, a.empno, b.deptnm, b.deptno

from tmp_emp a left outer join tmp_dept b on a.deptno  = b.deptno 

where b.deptno >= 20    

ORDER BY 2

;

이순신 200 재무팀 20

안중근 300 영업팀 30

==> 즉 on 절에 조건을 주는 것은 outer join에 대한 조건이며

      where 절에 조건은 outer join의 결과에 대한 필터 조건이 되는 것임.

             따라서 inner 값이 null인 것도 포함할려면 아래와 같이 is null 조건을 추가로 줘야 함.

select a.empnm, a.empno, b.deptnm, b.deptno

from tmp_emp a left outer join tmp_dept b on a.deptno  = b.deptno 

where (b.deptno >= 20 or b.deptno is null)

ORDER BY 2

;

      

===========================================================================================