FAQ
[SQL] Outer Join 예
분류 SQL 등록일 2013-07-09 조회수 3397
okseop7 2004-03-09 76


Outer Join 예
+++++++++++++++++++++++++++++++++++++++
오라클의 Outer Join을 SQL92 표준으로 바꾸어 보았는데..
올려봅니다.
테스트는 mmdb인 알티베이스로 했지만 PostgreSQL과 동일할겁니다.

SQL>... 은 오라클
바로밑줄의 iSQL>은 SQL92로 이해하시면.. 되고요..

컬럼은 모두 integer형
샘플데이타는

SQL> select * from out1A;
.......C1.........C2
---------- ----------
.........1
.........2
....................1
....................2

SQL> select * from out1B
.......C1.........C2
---------- ----------
.........1
.........2
....................1
....................2

SQL> select * from out1C;
.......C1.........C2
---------- ----------
........11
이고..

변환예는 아래..

SQL> select a.*, b.* from out1A a, out1B b where a.c1(+) = b.c1;
iSQL> select a.*, b.* from out1B b left outer join out1A a on a.c1 = b.c1;

SQL> select a.*, b.* from out1A a, out1B b, out1C c where a.c1(+) = b.c1 and
c.c1 = 11;
iSQL> select a.*, b.* from out1B b left outer join out1A a on a.c1 = b.c1, out1C
c where c.c1 = 11;

SQL> select a.*, b.* from out1A a, out1B b where a.c1(+) = b.c1 AND a.c2(+) = b.c1;
iSQL> select a.*, b.* from out1B b left outer join out1A a on a.c1 = b.c1 AND
a.c2 = b.c2;

SQL> select a.*, b.*, c.* from out1A a, out1B b, out1C c where a.c1(+) = b.c1
AND a.c2(+) = b.c1 and c.c1 = 11;
iSQL> select a.*, b.*, c.* from out1B b left outer join out1A a on A.C1 = B.C1
AND A.C2 = B.C2, out1C c where c.c1 = 11;

SQL> select a.*, b.*, c.* from out1A a, out1B b, out1C c where b.c1(+) = a.c1
and c.c1(+) = a.c1;
iSQL> select A.*, B.*, C.* from OUT1A A left outer join OUT1B B on B.C1 = A.C1
left outer join out1C c on c.c1 = a.c1

SQL> select a.*, b.* from out1A a, out1B b where a.c1(+) = 1;
iSQL> select a.*, b.* from out1B b left outer join out1A a on a.c1 = 1;

제가 Outer Join을 잘몰라서..
왜
select a.*, b.* from out1A a, out1B b where a.c1(+) = 1;
이런 문장을 쓰나 고민했네요.
select a.*, b.* from out1A a, out1B b where a.c1 = 1;
와 결과가 같거든요..

그러나 outer join 조건이 하나 더 있으면 달라지네요.

SQL> select a.*, b.* from out1a a, out1b b where a.c1 = b.c1(+) and 1 = b.c1(+);
iSQL> select a.*, b.* from out1a a left outer join out1b b on b.c1 = a.c1 AND
b.c1 = 1;

이 보통 의도한 경우고

SQL> select a.*, b.* from out1a a, out1b b where a.c1 = b.c1(+) and 1 = b.c1;
iSQL> select a.*, b.* from out1a a left outer join out1b b on b.c1 = a.c1 where
b.c1 = 1;

이 잘못 사용된 경우 네요.

요약
++++
오라클의 outer join syntex를 SQL92 표준으로 전환하여 봅니다.
목록