오라클 (+) LEFT OUTER JOIN

이하 참조 : http://egloos.zum.com/ultteky/v/10411192

일반적으로 left outer join 에서

on 절에는 우측(null 값이 포함되는) 테이블의 제약조건을 넣고,

where 절에는 좌측 테이블의 제약조건을 넣는다.

1. 원하는 결과값

SELECT *  --(184개 행 적용됨)
FROM TBHZM200 AS A LEFT OUTER JOIN MST_TKT_STA_STOCK AS B
ON  a.Extn_Sta_No = b.Extn_Sta_No
AND B.Oper_DT = '20090713'    <-- 우측의 추가 제약조건
WHERE A.Transp_Oper_Org_CD = '1'   <-- 좌측의 추가제약조건

2. 잘못된 예 -- INNER JOIN의 결과가 나오게 됨.

SELECT *  --(176개 행 적용됨)
FROM TBHZM200 AS A LEFT OUTER JOIN MST_TKT_STA_STOCK AS B
ON  a.Extn_Sta_No = b.Extn_Sta_No
AND A.Transp_Oper_Org_CD = '1'
WHERE b.Oper_DT = '20090713'

3. 잘못된 예

SELECT * --(503개 행 적용됨)
FROM TBHZM200 AS A LEFT OUTER JOIN MST_TKT_STA_STOCK AS B
ON a.Extn_Sta_No = b.Extn_Sta_No
AND B.Oper_DT = '20090713' <--둘다 ON 절에 위치함.
AND A.Transp_Oper_Org_CD = '1'

4. 잘못된 예

SELECT * --(176개 행 적용됨)
FROM TBHZM200 AS A LEFT OUTER JOIN MST_TKT_STA_STOCK AS B
ON a.Extn_Sta_No = b.Extn_Sta_No
WHERE B.Oper_DT = '20090713' <--둘다 where절에 위치
AND A.Transp_Oper_Org_CD = '1'

갯수 503 SELECT count(*) FROM TBHZM200 AS A
갯수 184 SELECT count(*) FROM TBHZM200 AS A WHERE A.Transp_Oper_Org_CD = '1'
갯수 13838 SELECT count(*) FROM MST_TKT_STA_STOCK AS B
갯수 178 SELECT count(*) FROM MST_TKT_STA_STOCK AS B WHERE B.Oper_DT = '20090713'

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

그리고, (+) 는 오라클 outer join 에 해당한다.

등호의 오른쪽=(+)에 붙으면 오른쪽 테이블에 null 허용이 되어 left outer join 이되고

right 는 그 반대(+)=다.

그리고, ansi 에서 on 절안에 필터 조건절은

T2.y (+) > 5

이렇게 표현한다. 그러면, outer join 전에 T2 테이블에 위의 조건이 먼저 실행된다.

즉, 위 조건이 실행된 후 join 된다.

아래는 ansi sql 과의 비교다.

Oracle-Specific 문법

Oracle 문법으로 left outerjoin을 표현한 쿼리 A를 생각해보자. 여기서 T1은 left 테이블이고, 조인되지 않는 row도 유지될 것이며 T2의 조인되지 않은 row는 NULL이 추가 될것이다.
```
A.

SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+);
 
```

ANSI Left Outerjoin

ANSI outer join 문법에서 쿼리 A는 쿼리 B처럼 표현 할 수 있다.
```
B.

SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x);
 
```

Equivalence

다 음 쿼리를 보자. Oracle에서는 필터 조건절(filter predicate, 쿼리 C에서 T2.y (+) > 5 )에서 (+) 기호가 나타났다는 것은, 필터가 반드시 outer join이 일어나기 전에 T2테이블에 적용되어야함을 나타낸다.
C.

SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y (+) > 5;
ANSI left outer join 쿼리 D 는 쿼리 C와 동일하다. left outer join에서 right 테이블에 필터를 적용하는 것은 필터와 조인 조건을 결합하는 것과 같다.
```
D.

SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x and T2.y > 5);
 
```
동일하게, 필터 조건절에 (+)기호가 나타는 것, 쿼리 E에서 'T2.y (+) IS NULL' 구문은 outer join이 일어나기 전에 T2 테이블에 적용되어야 함을 나타낸다.
```
E.

SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y (+) IS NULL;
 
```

ANSI left outer join 쿼리 F는 쿼리 E와 같다.

F.

SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x and T2.y IS NULL);

쿼 리 G를 보자. 쿼리 G에서 Oracle은 'T2.y IS NULL' 필터를 outer join이 일어난 이후에 적용할것이다. 쿼리 G는 T2가 T1과 조인되지 않은 row이거나 T2.y 의 값이 NULL인 경우만 리턴할 것이다.
```
G.

SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y IS NULL;
 
```
ANSI left outer join 쿼리 H는 쿼리 G와 같다. 쿼리 H에 있는 WHERE절은 ON절에 명시된 조건을 바탕으로 outer join이 실행된 이후에 적용될 것이다.
```
H.

SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x)
WHERE T2.y IS NULL;
 
```
쿼리 I를 보자. left 테이블의 필터 조건은 outer join이 적용되기전이나 혹은 적용된 이후 적용될 것이다.
```
I.

SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T1.Z > 4;
 
```

ANSI left outer join 쿼리 J는 쿼리 I와 같다.

J.

SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x)
WHERE T1.Z > 4;

Lateral Views

Oracle 에서는, ANSI left outerjoin과 right outerjoin은 내부적으로 left outerjoined lateral views로 표현된다. 많은 경우에서, left outerjoined lateral view는 결합될 수 있다. 그리고 ANSI left (또는 right) outerjoin은 Oracle native left outerjoin 연산자로 표현할 수 있다. ('lateral view'는 FROM절에서 앞서 나타난 다른 테이블들을 참조하는 관계를 포함한 inline view이다.)
ANSI left outer join 쿼리 K를 보자. 이것은 처음에 내부적으로 쿼리 L처럼 표현된다.

K.

SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x and T2.k = 5);

L.

SELECT T1.d, LV.c
FROM T1,
LATERAL (SELECT T2.C
FROM T2
WHERE T1.x = T2.x and T2.k = 5)(+) LV;

쿼리 L에서 Lateral view 는 쿼리 M으로 나타내기 위해 병합된다.
```
M.

SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.k (+)= 5;
 
```
ANSI join 문법으로 left outerjoin 을 표현한 쿼리 N을 보자. 현재 쿼리 N은 Oracle native left outer join 연산자로 표현할 수 없다.
```
N.

SELECT T1.m, T2.n
FROM T1 LEFT OUTER JOIN T2
ON (T1.h = 11 and T1.y = T2.y)
WHERE T1.q > 3;
 
```
쿼 리 N 은 left outer-joined lateral view를 사용하여 쿼리 O로 변환된다. 쿼리 O의 lateral view는 병합될 수 없다. 왜냐하면, ON절에 정의된 left 테이블의 필터는 반드시 left outerjoin join 조건의 일부이어야 하기 때문이다.
```
O.

SELECT T1.m, LV.n
FROM T1,
LATERAL(SELECT T2.n
FROM T2
WHERE T1.h = 11 and T1.y = T2.y)(+) LV
WHERE T1.q > 3;
 
```
Outer join 조건에 disjunction(논리합, OR)이 있는 쿼리 P를 보자. 현재 쿼리 N은 Oracle native left outer join로 표현할 수 없었다.
```
P.

SELECT T1.A, T2.B
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x OR T1.Z = T2.Z);
 
```
쿼리 P는 disjunctive join 조건을 포함한 left outer-joined lateral view를 가지고 쿼리 Q로 변환할 수 있다.
```
Q.

SELECT T1.A, LV.B
FROM T1,
LATERAL (SELECT T2.B
FROM T2
WHERE T1.x = T2.x OR T1.Z = T2.Z) (+) LV;
 
```

ANSI Full Outerjoin

Oracle 11gR1 전까지는 모든 ANSI full outerjoin은 두 branch를 가지는 UNION ALL쿼리로 변환된다. 하나의 branch는 left outer-joined lateral view를 가지고, 다른 하나의 branch는 NOT EXISTS 서브쿼리를 가진다. 이 문제를 해결하기위해 11gR1에서 hash full outerjoin에 대한 'native' 지원이 추가되었다. native full outerjoin에서는 11gR1이전의 정책으로 사용될 수 없다.
ANSI full outerjoin을 기술한 쿼리 R을 보자.
R. SELECT T1.c, T2.d FROM T1 FULL OUTER JOIN T2 ON T1.x = T2.y;

11gR1 전까지, Oracle은 내부적으로 쿼리 R을 쿼리 S로 변환한다.

S.

SELECT T1.c, T2.d
FROM T1, T2
WHERE T1.x = T2.y (+)
UNION ALL
SELECT NULL, T2.d
FROM T2
WHERE NOT EXISTS
(SELECT 1 FROM T1 WHERE T1.x = T2.y);

Hash full outerjoin에 대한 'native'지원으로, 쿼리 R 은 간단히 쿼리 T로 표현된다. 그리고 view 'VFOJ'는 병합될 수 없는 것으로 간주된다.
```
T.

SELECT VFOJ.c, VFOJ.d
FROM (SELECT T1.c, T2.d
FROM T1, T2
WHERE T1.x F=F T2.y) VFOJ;
 
```

Conversion of Outerjoin into Inner Join

쿼 리 U를 보자. outer-joined 테이블 T2의 필터 조건절에는 outerjoin 연산자 - (+)가 없다. 그래서 left outerjoin이 일어난 이후에 적용될 것이다. 이것은 T2에서 NULL이 추가된 row를 제거하는 결과는 가져온다. 따라서, 오라클은 outer join을 inner join으로 변환한다.
```
U.

SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x (+) and T2.y > 5;
 
```
ANSI left outer join 쿼리 V는 쿼리 U와 동일하다. 쿼리 V의 WHERE 절은 ON절에 명시된 조건을 바탕으로 left outer join이 실행된 이후에 적용되기 때문이다.
```
V.

SELECT T1.d, T2.c
FROM T1 LEFT OUTER JOIN T2
ON (T1.x = T2.x)
WHERE T2.y > 5;
 
```
Oracle은 이 쿼리 U나 쿼리 V를 inner join으로 쿼리 W와 같이 변환한다.

W.

SELECT T1.d, T2.c
FROM T1, T2
WHERE T1.x = T2.x and T2.y > 5;

결론

Oracle 의 outer join 처리 방식을 알아두고 ANSI SQL과의 차이를 이해하도록하자. Oracle 이외의 제품도 ANSI SQL과 다르게 동작하는 부분이 존재한다. 특히 다른 제품으로 마이그레이션하는 경우라면 충분한 테스트가 필요하다.

참고 문헌

Outerjoins in Oracle,
http://optimizermagic.blogspot.com/2007/12/outerjoins-in-oracle.html (Optimizer Development Group blog)
Back to basics: outer joins,
http://awads.net/wp/2006/03/22/back-to-basics-outer-joins/

논외로,

- inner join 의 on 절안에 filter 조건은 where 조건에 걸어도 상관없다.

필요에 따라 오라클이 알아서 where 조건안에 조건을 조인 전에 실행한다.

plan 으로 확인 ...

- 중첩쿼리에 distinct 를 걸면 오라클 자체적으로 view table 을 생성해서 메모리에

올라와 빠르게 처리된다.

- 색인이 20% 의 row 를 검색할 경우는 full scan 하는 것보다 비용이 많이 들어

오라클에서 그럴 경우 색인 안타고 full scan 함...

'개발 > 데이타베이스' 카테고리의 다른 글

[ORACLE] RANK OVER() / ROW_NUMBER() OVER() (0)	2017.02.06
[ORACLE] LISTAGG ... WITHIN ... (0)	2017.02.06
oracle 문자열 line feed, carrage return 개행 문자 삭제 (0)	2017.01.16
SQL JOIN 종류 (0)	2017.01.16
oracle 년도별 group by (0)	2017.01.16

뽕

오라클 (+) LEFT OUTER JOIN

Oracle-Specific 문법

ANSI Left Outerjoin

Equivalence

Lateral Views

ANSI Full Outerjoin

Conversion of Outerjoin into Inner Join

참고 문헌

'개발 > 데이타베이스' 카테고리의 다른 글

댓글

티스토리툴바

오라클 (+) LEFT OUTER JOIN

Oracle-Specific 문법

ANSI Left Outerjoin

Equivalence

Lateral Views

ANSI Full Outerjoin

Conversion of Outerjoin into Inner Join

참고 문헌

'개발 > 데이타베이스' 카테고리의 다른 글

관련글

댓글

티스토리툴바