Oracle OUTER JOIN 정리 1

예제 테이블 생성

샘플 테이블 생성

- FULLOUTER1 테이블
ID     NAME
------------
1001	박무영
1002	이무영
1003	김지미
1004	이연희
1005	최무룡

- FULLOUTER2 테이블
ID     NAME
-------------
1001	박무영
1002	이무영
1009	고아라
1010	소녀시대
1015	2PM

1. 오라클 LEFT OUTER JOIN 기본 개념

OUTER JOIN이란 두 테이블을 조인할 경우 조인 컬럼의 값이 NULL인 데이터도 결과에 포함하는 조인방법입니다. 종류로는 LEFT OUTER JOIN. RIGHT OUTER JOIN, FULL OUTER JOIN이 있습니다. 위의 두 예제 테이블을 살펴보면 ID 가 PK인데 2개의 테이블을 일반적인 조인을 하게되면 ID가 1001, 1002인 2개의 레코드만 조회됩니다. 그런데 개발자가 1번 테이블은 2번에 조인되는 값이 없더라도 다 가져오고 2번 테이블은 조인되는 값만 가져오고 싶을때 사용하는것이 LEFT OUTER JOIN 입니다.

- 일반조인
SELECT A.ID, B.ID, A.NAME, B.NAME
FROM FULLOUTER1 A, FULLOUTER2 B
WHERE A.ID=B.ID;

A.ID	B.ID	A.NAME	B.NAME
1001	1001	박무영	박무영
1002	1002	이무영	이무영

• LEFT OUTER JOIN : 오라클은 (+)를 사용하여 OUTER조인을 표시합니다.
  아래예는 A 테이블 데이타는 전부 가져오고 B 테이블의 데이타는 A 테이블과 조인되는 값만 가져오는 SQL입니다. ANSI SQL은 LEFT OUTER JOIN이라 합니다. (+) 기호를 기준 테이블인 A에 붙이지 않고 B에 붙였습니다.

 SELECT A.ID, B.ID, A.NAME, B.NAME
 FROM FULLOUTER1 A,  FULLOUTER2 B
 WHERE A.ID=B.ID(+);

A.ID	B.ID	A.NAME	B.NAME
1001	1001	박무영	박무영
1002	1002	이무영	이무영
1005		최무룡
1003		김지미
1004		이연희

2. 오라클 RIGHT OUTER JOIN 기본 개념

이번에는 B 테이블을 기준으로 B 테이블은 전부 가져오고 A 테이블은 조인되는 레코드만 가져오는 RIGHT OUTER JOIN 입니다.

- RIGHT OUTER JOIN : B 테이블 데이타는 전부 가져오고 A 테이블의 데이타는 B 테이블과 조인되는 
  값만 가져오는 SQL입니다. ANSI SQL은 RIGHT OUTER JOIN이라 합니다. (+) 기호를 기준 테이블인 B에 붙이지 않고 A에 붙였습니다.

SELECT A.ID,B.ID,A.NAME,B.NAME
FROM FULLOUTER1 A,   FULLOUTER2 B
WHERE A.ID(+)=B.ID;

A.ID	B.ID	A.NAME	B.NAME
1001	1001	박무영	박무영
1002	1002	이무영	이무영
	1010		소녀시대
	1015		2PM
	1009		고아라

3. 오라클 FULL OUTER JOIN 기본 개념

오라클은 FULL OUTER JOIN을 ANSI SQL 형태로 지원하고 (+) 표시로는 지원하지 않는다. 양쪽에 (+) 표시를 하면 에러가 발생합니다. 굳이 (+)를 양쪽에 하지않아도 FULL OUTER JOIN을 만들수 있는 방법이 있습니다. 그리고 오라클 DBMS에서도 ANSI SQL을 지원하므로 FULL OUTER 문구로 조인을 하면 됩니다.

- FULL OUTER JOIN 수행. A테이블기준으로, B테이블 기준으로 OUTER조인을 각각수행함
SELECT A.ID,B.ID,A.NAME,B.NAME
FROM FULLOUTER1 A FULL OUTER JOIN  FULLOUTER2 B
ON A.ID = B.ID;

A.ID	B.ID	A.NAME	B.NAME
1001	1001	박무영	박무영
1002	1002	이무영	이무영
	1009		고아라
	1010		소녀시대
	1015		2PM
1005		최무룡
1003		김지미
1004		이연희

- A.ID, B.ID를 보기좋게 통합
SELECT  (CASE WHEN A.ID IS NOT NULL THEN A.ID    
             ELSE B.ID END) AS ID, A.NAME,B.NAME
FROM FULLOUTER1 A FULL OUTER JOIN FULLOUTER2 B
ON A.ID = B.ID
ORDER BY 1;

ID	A.NAME	B.NAME
1001	박무영	박무영
1002	이무영	이무영
1003	김지미
1004	이연희
1005	최무룡
1009		고아라
1010		소녀시대
1015		2PM

4. 오라클 OUTER JOIN 제한사항

오라클은 ANSI 표준SQL 형태와 오라클 독점표시인 (+) 기호등 2가지 형태로 OUTER JOIN을 사용할수 있습니다. (+) 기호를 사용할때 주의점은 아래와 같습니다.

가. 조인조건과 일반조건 모두 (+) 기호를 붙여야 합니다. 하나라도 빠지게 되면 오라클 내부적으로 일반조인으로 변경하여 처리합니다. 

SELECT e1.ename,e2.ename 
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno(+)
AND e2.deptno = 20;        <--(+) 기호가 생략됨.

나. 10G 버젼 DBMS 제한 : IN, OR절을 사용할수 없다. 11G부터 가능함.

SELECT e1.ename,e2.ename 
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno(+)
AND e2.deptno(+) in (10, 20)  <--IN절 사용하여 에러 발생함.

ORA-01719: 포괄 조인 운영 (+)는 OR 또는 IN의 연산수를 허용하지 않습니다.

다. 조인 조건의 한쪽에만 (+) 기호를 붙일 수 있음.

SELECT e1.ename,e2.ename 
FROM emp e1, emp e2
WHERE e1.mgr(+) = e2.empno(+); <--양쪽에 (+) 기호 표시하여 에러발생

ORA-01468: outer-join된 테이블은 1개만 지정할 수 있습니다.

5. 결언

OUTER JOIN 기능은 모든 RDBMS에서 많이 지원하는 표준기능이며 오라클에서는 독점표시인 (+) 기호와 ANSI SQL을 사용하여 표현할수 있습니다. OUTER JOIN 개념은 개발자가 필수적으로 알고있어야 할 내용입니다. 다음 포스팅에서 OUTER JOIN과 여러가지 SQL형태를 비교하여 어떤 형태가 가장 좋은 성능을 낼수 있는지 알아보도록 합니다.