오라클 OUTER JOIN 3가지 종류에 대해 알아보자(LEFT, RIGHT, FULL)

데이터베이스에서 데이터를 관리하고 분석하는 데에는 여러 테이블에 저장된 데이터를 효율적으로 결합해야 하는 경우가 있습니다. SQL에서는 이러한 데이터 결합을 위해 여러 조인(Join) 방법을 사용하는데요, JOIN은 크게 INNER JOIN, OUTER JOIN으로 구분할 수 있고 OUTER JOIN는 (LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN) 3가지로 나눌 수 있습니다, 이번 글을 통해서 OUTER JOIN에 대해 알아보도록 하겠습니다.

오라클 OUTER JOIN 조인의 경우 (INNER JOIN과는 다르게)모두 일치하는 데이터만 가져오는 것이 아닌 조인의 대상이 되는 테이블의 자료가 기준테이블과 매칭되는게 없어도 조인하여 두테이블의 자료를 모두 가져 옵니다, 아래의 예제들을 통해 자세히 알아보겠습니다.

이번글의 예제로 사용할 테이블(데이터)

WITH student AS (
SELECT 1 studno, 'sKim' sname, 11 profno FROM dual UNION ALL
SELECT 2 studno, 'sPark' sname, 12 profno FROM dual UNION ALL
SELECT 3 studno, 'sLee' sname, 13 profno FROM dual UNION ALL
SELECT 4 studno, 'sKang' sname, NULL profno FROM dual UNION ALL
SELECT 5 studno, 'sKoo' sname, NULL profno FROM dual
), professor AS (
SELECT 11 profno, 'proKim' , 'sJin' pname FROM dual UNION ALL
SELECT 12 profno, 'proPak' , 'sSoo' pname FROM dual UNION ALL
SELECT 13 profno, 'proLee' , 'sHoon' pname FROM dual UNION ALL
SELECT 14 profno, 'proKang' , 'sJoo' pname FROM dual UNION ALL
SELECT 15 profno, 'proJung' , 'sDo' pname FROM dual
)

1. LEFT OUTER JOIN

LEFT OUTER JOIN은 FROM 절의 왼쪽에 위치한 (기준)테이블의 데이터는 모두 출력하고, FROM절의 오른쪽에 위치한 (조인)테이블의 값은 매칭되는 값이 있으면 출력, 없으면 null로 출력

1-1.LEFT OUTER JOIN

지도교수가 배정되지 않은 학생명단을 출력해보자

WITH student AS (
SELECT 1 studno, 'sKim' sname, 11 profno FROM dual UNION ALL
SELECT 2 studno, 'sPark' sname, 12 profno FROM dual UNION ALL
SELECT 3 studno, 'sLee' sname, 13 profno FROM dual UNION ALL
SELECT 4 studno, 'sKang' sname, NULL profno FROM dual UNION ALL
SELECT 5 studno, 'sKoo' sname, NULL profno FROM dual
), professor AS (
SELECT 11 profno, 'proKim' , 'sJin' pname FROM dual UNION ALL
SELECT 12 profno, 'proPak' , 'sSoo' pname FROM dual UNION ALL
SELECT 13 profno, 'proLee' , 'sHoon' pname FROM dual UNION ALL
SELECT 14 profno, 'proKang' , 'sJoo' pname FROM dual UNION ALL
SELECT 15 profno, 'proJung' , 'sDo' pname FROM dual
)

SELECT studno, sname, s.profno, pname
FROM student s
LEFT OUTER JOIN professor p
ON s.profno = p.profno;

출력 결과)

1-2.WHERE절에 ‘+’ 기호를 사용한 LEFT OUTER JOIN

1-1의 내용에서 LEFT OUTER JOIN을 제거 후 ON대신 WHERE절을 사용하여 ‘+’ 기호를 추가.

WITH student AS (
SELECT 1 studno, 'sKim' sname, 11 profno FROM dual UNION ALL
SELECT 2 studno, 'sPark' sname, 12 profno FROM dual UNION ALL
SELECT 3 studno, 'sLee' sname, 13 profno FROM dual UNION ALL
SELECT 4 studno, 'sKang' sname, NULL profno FROM dual UNION ALL
SELECT 5 studno, 'sKoo' sname, NULL profno FROM dual
), professor AS (
SELECT 11 profno, 'proKim' , 'sJin' pname FROM dual UNION ALL
SELECT 12 profno, 'proPak' , 'sSoo' pname FROM dual UNION ALL
SELECT 13 profno, 'proLee' , 'sHoon' pname FROM dual UNION ALL
SELECT 14 profno, 'proKang' , 'sJoo' pname FROM dual UNION ALL
SELECT 15 profno, 'proJung' , 'sDo' pname FROM dual
)

SELECT studno, sname, s.profno, pname
FROM student s, professor p
WHERE s.profno = p.profno(+);

출력결과)

1-1, 1-2 모두 동일한 결과를 확인 할 수 있다.

2.RIGHT OUTER JOIN

RIGHT OUTER JOIN은 LEFT OUTER JOIN과 반대로, FROM 절의 오른쪽에 위치한 (기준)테이블 데이터는 모두 출력하고, FROM절의 왼쪽에 위치한 (조인)테이블의 값은 매칭되는 값이 있으면 출력, 없으면 null로 출력

2-1.RIGHT OUTER JOIN

WITH student AS (
SELECT 1 studno, 'sKim' sname, 11 profno FROM dual UNION ALL
SELECT 2 studno, 'sPark' sname, 12 profno FROM dual UNION ALL
SELECT 3 studno, 'sLee' sname, 13 profno FROM dual UNION ALL
SELECT 4 studno, 'sKang' sname, NULL profno FROM dual UNION ALL
SELECT 5 studno, 'sKoo' sname, NULL profno FROM dual
), professor AS (
SELECT 11 profno, 'proKim' , 'sJin' pname FROM dual UNION ALL
SELECT 12 profno, 'proPak' , 'sSoo' pname FROM dual UNION ALL
SELECT 13 profno, 'proLee' , 'sHoon' pname FROM dual UNION ALL
SELECT 14 profno, 'proKang' , 'sJoo' pname FROM dual UNION ALL
SELECT 15 profno, 'proJung' , 'sDo' pname FROM dual
)

학생이 배정된 지도교수들의 자료를 출력해보자

SELECT studno, sname, p.profno, pname
FROM student s
RIGHT OUTER JOIN professor p
ON s.profno = p.profno;

출력결과)

2-2.WHERE절에 ‘+’ 기호를 사용한 RIGHT OUTER JOIN

2-1의 내용에서 RIGHT OUTER JOIN을 제거 후 ON대신 WHERE절을 사용하여 ‘+’ 기호를 추가.

WITH student AS (
SELECT 1 studno, 'sKim' sname, 11 profno FROM dual UNION ALL
SELECT 2 studno, 'sPark' sname, 12 profno FROM dual UNION ALL
SELECT 3 studno, 'sLee' sname, 13 profno FROM dual UNION ALL
SELECT 4 studno, 'sKang' sname, NULL profno FROM dual UNION ALL
SELECT 5 studno, 'sKoo' sname, NULL profno FROM dual
), professor AS (
SELECT 11 profno, 'proKim' , 'sJin' pname FROM dual UNION ALL
SELECT 12 profno, 'proPak' , 'sSoo' pname FROM dual UNION ALL
SELECT 13 profno, 'proLee' , 'sHoon' pname FROM dual UNION ALL
SELECT 14 profno, 'proKang' , 'sJoo' pname FROM dual UNION ALL
SELECT 15 profno, 'proJung' , 'sDo' pname FROM dual
)

SELECT studno, sname, p.profno, pname
FROM student s, professor p
WHERE s.profno(+) = p.profno;

출력결과)

2-1, 2-2 모두 동일한 결과를 확인 할 수 있다.

3. FULL OUTER JOIN

FULL OUTER JOIN은 LEFT OUTER JOIN과 RIGHT JOIN을 합친 조인이라고 보면 된다. 즉, 양쪽 테이블의 모든 데이터를 출력하고, 매칭되는 값이 없는 경우에도 해당 테이블의 모든 데이터를 출력한다.

WITH student AS (
SELECT 1 studno, 'sKim' sname, 11 profno FROM dual UNION ALL
SELECT 2 studno, 'sPark' sname, 12 profno FROM dual UNION ALL
SELECT 3 studno, 'sLee' sname, 13 profno FROM dual UNION ALL
SELECT 4 studno, 'sKang' sname, NULL profno FROM dual UNION ALL
SELECT 5 studno, 'sKoo' sname, NULL profno FROM dual
), professor AS (
SELECT 11 profno, 'proKim' , 'sJin' pname FROM dual UNION ALL
SELECT 12 profno, 'proPak' , 'sSoo' pname FROM dual UNION ALL
SELECT 13 profno, 'proLee' , 'sHoon' pname FROM dual UNION ALL
SELECT 14 profno, 'proKang' , 'sJoo' pname FROM dual UNION ALL
SELECT 15 profno, 'proJung' , 'sDo' pname FROM dual
)

SELECT studno, sname, p.profno, pname
FROM student s
FULL OUTER JOIN professor p
ON s.profno = p.profno;

출력결과)

여기까지, 이번 글을 통해서 SQL 조인중 OUTER JOIN의 세 가지 방법(LEFT, RIGHT, FULL)OUTER JOIN에 대해 알아보았는데요, 우리가 데이터베이스를 사용하는데 있어서 이러한 조인 방법들을 앞으로 잘 활용한다면 더욱 효율적인 작업을 수행할 수 있을 것입니다. 이러한 사용법들을 익히고 데이터베이스 활용에 도움이 되셨으면 좋겠습니다.

이전 글 – [오라클] 조인 INNER JOIN(EQUI JOIN과 NON-EQUI JOIN)에 대해 알아보자

이전 글 – 오라클 데이터 조작어 DML(INSERT, UPDATE, DELETE) 활용 방법

이전 글 – 오라클 데이터 무결성 제약조건 정리 (기본키,외래키,참조키, 제약조건 생성, 추가, 삭제, 비활성화)

이전 글 – 오라클 인덱스에 대해 알아보자 (데이터베이스 실무에서 효율적으로 사용 할 수 있는 Oracle Index)

이전 글 – 오라클 뷰(VIEW) 조회 및 사용방법 (개념과 특징,종류, 장단점, 인라인뷰)

이전 글 – 오라클 사용자 권한 제어 (4가지 – 시스템 권한, 객체 권한, 롤, 동의어)

이전 글 – 오라클(데이터 웨어하우스) 분석 함수(효과적인 데이터 다차원 분석을 위한 강력한 도구)

이전 글 – 오라클 스케줄러와 JOB 관리 방법

이전 글 – 오라클 테이블 복구(테이블 데이터 복원하기)