좌측의 추가 제약조건은 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'
참고
http://mixellaneous.tistory.com/tag/Outer%20join
-----------------------------------------------------------------------------------------------
//참고 사이트 글
-----------------------------------------------------------------------------------------------
Outerjoins in Oracle
- 김영우 (warwithin@daumcorp.com)
Oracle Optimizer Development Group 블로그에 포스팅 된 'Outerjoins in Oracle'을 번역하고 정리한 글이다. 원문은 http://optimizermagic.blogspot.com/2007/12/outerjoins-in-oracle.html 이다.
Outerjoins in Oracle
- Oracle 은 릴리즈 6부터 Oracle-specific한 문법으로 사용되는 left outerjoin의 특별한 형식을 지원한다. 9i에서, Oracle은 ANSI SQL 92/99 inner join과 다양한 outerjoin 문법을 지원하기 시작했다. oracle의 left outerjoin 문법과 ANSI SQL 92/99의 문법은 동일하지 않다.
- Oracle outerjoin 문법과 ANSI outer join문법간의 동일함(equivalence)에 대한 혼란이 존재한다. 다음 예제는 이 두 문법간의 같은 부분과 다른 부분에 대해 설명한다.
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/