[FULL OUTER JOIN] 예문으로 이해하기

OUTER JOIN 절은 기준에 따라 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN 이 있습니다.
오늘은 FULL OUTER JOIN 함수에 대해서 분석해 보겠습니다.

 

테이블 생성 및 데이터 인설트

CREATE TABLE b1
(
    c1 NUMBER
    ,c2 VARCHAR2(10)
);

CREATE TABLE b2
(
    c1 NUMBER
    ,c2 VARCHAR2(10)
);

--데이터 삽입
INSERT INTO b1 VALUES (1,'A');
INSERT INTO b1 VALUES (2,'B');
INSERT INTO b1 VALUES (3,'C');
INSERT INTO b1 VALUES (4,'D');

INSERT INTO b2 VALUES (2,'B');
INSERT INTO b2 VALUES (2,'B');
INSERT INTO b2 VALUES (3,'C');
INSERT INTO b2 VALUES (5,'C');
COMMIT;

SELECT * FROM B1;
C1 C2
1 A
2 B
3 C
4 D


SELECT * FROM B2;
C1 C2
2 B
2 B
3 C
5 C

풀 아우터 조인은 LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합친 결과입니다.
여기서 주의할 점은 조인에 성공한 행들은 한번만 결과에 포함시킨다는 것입니다.

 

LEFT OUTER JOIN

SELECT A.C1,A.C2,B.C1,B.C2 
FROM B1 A LEFT OUTER JOIN B2 B
ON B.C1=A.C1;
--------------
C1 C2 C1 C2
2 B 2 B
2 B 2 B
3 C 3 C
4 D
1 A

 

RIGHT OUTER JOIN

SELECT A.C1,A.C2,B.C1,B.C2 
FROM B1 A RIGHT OUTER JOIN B2 B
ON B.C1=A.C1;
--------------
C1 C2 C1 C2
2 B 2 B
2 B 2 B
3 C 3 C
    5 C

 

FULL OUTER JOIN 

SELECT A.C1,A.C2,B.C1,B.C2 
FROM B1 A FULL OUTER JOIN B2 B
ON A.C1=B.C1;
--------------
C1 C2 C1 C2
2 B 2 B
2 B 2 B
3 C 3 C
4 D
1 A
    5 C

 

결과중  3개의 행은 LEFT OUTER JOIN과 RIGHT OUTER JOIN의 중복 값이므로 한 번만 출력됩니다.

2 B 2 B
2 B 2 B
3 C 3 C