ANSI OUTER JOIN 과 ORACLE OUTER JOIN 의 차이점

ANSI OUTER JOIN 과 ORACLE OUTER JOIN 의 차이점

오라클에서 흔히 OUTER JOIN을 이용하는 경우 WHERE 조건에서 입력 값이 부족한 부분에 (+) 이용해서 하는 방법과 ANSI OUTER JOIN 문법인 LEFT(RIGHER) OUTER JOIN ~ ON 절을 이용하는 방법이 있다. 이 두가지 방식의 문법적인 차이첨을 확인해보고 사용상 오류를 방지하기 위해 이 문서를 작성한다.

1. 테스트 테이블의 형태

SQL> select * from tmp_20110727_1;

  APT_CODE APT_NAME

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

         1 신구파인힐

         2 인텔빌라

         3 청운벽산빌리지

SQL> select * from tmp_20110727_2;

  APT_CODE BULD_OK_DATE

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

         1 20000929

         2 19901127

         3 19881111

         4 20001002

2. tmp_20110727_1 과 tmp_20110727_2를 기준으로 조인하되 tmp_20110727_2에만 있는 필드도 출력하는 경우

(오라클 OUTER JOIN 방식)

SQL> select b.apt_code, a.apt_name, b.buld_ok_date

  2  from tmp_20110727_1 a, tmp_20110727_2 b

  3  where a.apt_code(+) = b.apt_code;

  APT_CODE APT_NAME                                 BULD_OK_

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

         1 신구파인힐                               20000929

         2 인텔빌라                                 19901127

         3 청운벽산빌리지                           19881111

         4                                          20001002

(ANSI OUTER JOIN 방식)

SQL> select b.apt_code, a.apt_name, b.buld_ok_date

  2  from tmp_20110727_1 a right outer join tmp_20110727_2 b

  3  on a.apt_code = b.apt_code;

  APT_CODE APT_NAME                                 BULD_OK_

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

         1 신구파인힐                               20000929

         2 인텔빌라                                 19901127

         3 청운벽산빌리지                           19881111

         4                                          20001002

3. APT_CODE가 3인 것을 빼고 1,2,4만 출력해야 하는 경우(오라클 OUTER JOIN 기준)

SQL> select b.apt_code, a.apt_name, b.buld_ok_date

  2  from tmp_20110727_1 a, tmp_20110727_2 b

  3  where a.apt_code(+) = b.apt_code

  4  and a.apt_code <> 3;

  APT_CODE APT_NAME                                 BULD_OK_

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

         1 신구파인힐                               20000929

         2 인텔빌라                                 19901127

(문제점 발생) 생각에도 없는 APT_CODE = 4 가 없어졌다.

(원인) OUTER 가 되는 쪽에 조건이 들어가면 OUTER JOIN 이 수행되지 않고 EQUAL 조인이 수행 됩니다.

OUTER 를 하는 쪽에 조건이 들어가는 경우

OUTER 가 되는 쪽에 조건이 들어가는 경우

/*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('10.2.0.4')       ALL_ROWS       OUTLINE_LEAF(@"SEL$9E43CB6E")       MERGE(@"SEL$58A6D7F6")       OUTLINE(@"SEL$3")       OUTLINE(@"SEL$58A6D7F6")       MERGE(@"SEL$1")       OUTLINE(@"SEL$2")       OUTLINE(@"SEL$1")       FULL(@"SEL$9E43CB6E" "B"@"SEL$2")       FULL(@"SEL$9E43CB6E" "A"@"SEL$1")       LEADING(@"SEL$9E43CB6E" "B"@"SEL$2" "A"@"SEL$1")       USE_HASH(@"SEL$9E43CB6E" "A"@"SEL$1")       END_OUTLINE_DATA   */

/*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('10.2.0.4')       ALL_ROWS       OUTLINE_LEAF(@"SEL$3BAA97A7")       MERGE(@"SEL$58A6D7F6")       OUTLINE(@"SEL$23D58506")       ELIMINATE_OUTER_JOIN(@"SEL$3")       OUTLINE(@"SEL$58A6D7F6")       MERGE(@"SEL$1")       OUTLINE(@"SEL$3")       OUTLINE(@"SEL$2")       OUTLINE(@"SEL$1")       FULL(@"SEL$3BAA97A7" "A"@"SEL$1")       FULL(@"SEL$3BAA97A7" "B"@"SEL$2")       LEADING(@"SEL$3BAA97A7" "A"@"SEL$1" "B"@"SEL$2")       USE_HASH(@"SEL$3BAA97A7" "B"@"SEL$2")       END_OUTLINE_DATA   */

 

(해결책) OUTER를 하는 쪽에서 조건을 넣음

SQL> select b.apt_code, a.apt_name, b.buld_ok_date

  2  from tmp_20110727_1 a, tmp_20110727_2 b

  3  where a.apt_code(+) = b.apt_code

  4  and b.apt_code <> 3;

  APT_CODE APT_NAME                                 BULD_OK_

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

         1 신구파인힐                               20000929

         2 인텔빌라                                 19901127

         4                                          20001002

(정상 출력)

4. APT_CODE가 3인 것을 빼고 1,2,4만 출력해야 하는 경우(ANSI OUTER JOIN 기준)

SQL> select b.apt_code, a.apt_name, b.buld_ok_date

  2  from tmp_20110727_1 a right outer join tmp_20110727_2 b

  3  on a.apt_code = b.apt_code

  4  where b.apt_code <> 3;

  APT_CODE APT_NAME                                 BULD_OK_

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

         1 신구파인힐                               20000929

         2 인텔빌라                                 19901127

         4                                          20001002

(정상 출력) ANSI SQL도 마찬가지로 OUTER를 하는 쪽에 조건을 넣어야만 정상적으로 결과가 출력 된다.

5. APT_CODE가 1,2,3,4 모두 출력 하되 3인 경우 tmp_20110726_1 의 정보를 null 로 출력하고자 하는 경우 (즉 tmp_20110726_1 테이블에는 1과 2만 들어있는 상황)일반적인 오라클 OUTER JOIN으로는 작업하기 위해서는 인라인뷰를 사용해야 한다. 하지만 ANSI OUTER JOIN은 ON 절에서 해당 컬럼을 조절 할 수 있다.

SQL> select b.apt_code, a.apt_name, b.buld_ok_date

  2  from tmp_20110727_1 a right outer join tmp_20110727_2 b

  3  on a.apt_code = b.apt_code  and b.apt_code <> 3;

  APT_CODE APT_NAME                                 BULD_OK_

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

         1 신구파인힐                               20000929

         2 인텔빌라                                 19901127

         3                                          19881111

         4                                          20001002

(확인) where 조건이 아닌 on 절에 해당 조건을 추가 함 이때 on 이하 조건은 어느쪽을 넣어도 같은 결과이나 출력 순서에 영향을 줌

OUTER 를 하는 쪽에 조건이 들어가는 경우

OUTER 가 되는 쪽에 조건이 들어가는 경우

/*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('10.2.0.4')       ALL_ROWS       OUTLINE_LEAF(@"SEL$1")       OUTLINE_LEAF(@"SEL$64EAE176")       MERGE(@"SEL$2")       OUTLINE(@"SEL$3")       OUTLINE(@"SEL$2")       FULL(@"SEL$64EAE176" "B"@"SEL$2")       NO_ACCESS(@"SEL$64EAE176" "from$_subquery$_004"@"SEL$2")       LEADING(@"SEL$64EAE176" "B"@"SEL$2" "from$_subquery$_004"@"SEL$2")       USE_NL(@"SEL$64EAE176" "from$_subquery$_004"@"SEL$2")       FULL(@"SEL$1" "A"@"SEL$1")       END_OUTLINE_DATA   */

/*+       BEGIN_OUTLINE_DATA       IGNORE_OPTIM_EMBEDDED_HINTS       OPTIMIZER_FEATURES_ENABLE('10.2.0.4')       ALL_ROWS       OUTLINE_LEAF(@"SEL$9E43CB6E")       MERGE(@"SEL$58A6D7F6")       OUTLINE(@"SEL$3")       OUTLINE(@"SEL$58A6D7F6")       MERGE(@"SEL$1")       OUTLINE(@"SEL$2")       OUTLINE(@"SEL$1")       FULL(@"SEL$9E43CB6E" "B"@"SEL$2")       FULL(@"SEL$9E43CB6E" "A"@"SEL$1")       LEADING(@"SEL$9E43CB6E" "B"@"SEL$2" "A"@"SEL$1")       USE_HASH(@"SEL$9E43CB6E" "A"@"SEL$1")       END_OUTLINE_DATA   */

 

(오라클 OUTER JOIN으로 같은 결과 값 만들기)

SQL> select b.apt_code, a.apt_name, b.buld_ok_date

  2  from (select * from tmp_20110727_1 where apt_code <> 3) a, tmp_20110727_2 b

  3  where a.apt_code(+) = b.apt_code;

  APT_CODE APT_NAME                                 BULD_OK_

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

         1 신구파인힐                               20000929

         2 인텔빌라                                 19901127

         4                                          20001002

         3                                          19881111


-- 2011.8.5 추가사항 

오라클 OUTER JOIN 시에 컬럼에 아우터 연산자를 사용하면 인라인뷰를 사용하지 않고 같은 결과 출력이 가능함

SQL> select b.apt_code, a.apt_name, b.buld_ok_date

  2  from tmp_20110727_1 a, tmp_20110727_2 b

  3  where a.apt_code(+) = b.apt_code

  4  and a.apt_code(+) <> 3;

  APT_CODE APT_NAME                                 BULD_OK_

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

         1 신구파인힐                               20000929

         2 인텔빌라                                 19901127

         4                                          20001002

         3                                          19881111 

-- 3개이상의 테이블 아우터 조인 방법(2011.8.12)

1. 오라클 아우터 조인 이용시

select *

from tmp_20110727_1 a, tmp_20110727_2 b, tmp_20110727_3 c

where a.apt_code(+) = c.apt_code

and b.apt_code(+) = c.apt_code

2. ANSI 아우터 조인 이용시

select *

from (tmp_20110727_1 a right outer join tmp_20110727_3 c on a.apt_code = c.apt_code) left outer join tmp_20110727_2 b

on c.apt_code = b.apt_code