[오라클(Oracle) ] 외부 조인 / OUTER JOIN / ANSI JOIN

[오라클(Oracle) ] OUTER JOIN / ANSI JOIN

 

 

 

 

 등가 조인(EQUI JOIN)에서 공통 칼럼으로 테이블을 연결했을 때 일치하는 값이 없는 행은 출력하지 않았던 것과는 달리 외부 조인(OUTER JOIN)은 기준 테이블 중 일부 행이 일치하는 값이 없더라도 모든 행을 출력하는 조인 방법이다. 예를 들어 student 테이블에서 학생별로 지도교수가 있다고 할 때 지도교수가 정해진 학생이 있고 지도교수가 아직 정해지지 않은 학생이 있을 수 있다. 공통 칼럼 교수 번호로 조인을 통해 student 테이블의 학생 이름, 담당 교수번호, professor 테이블의 교수 이름을 출력한다고 할 때 등가 조인(EQUI JOIN)을 사용한다면 지도교수가 정해진 학생만 출력이 될 것이다. 하지만 아직 지도교수가 정해지지 않은 학생까지 학생 전체를 출력하고 싶을 때는 외부 조인(OUTER JOIN을 사용하면 된다. 

 

SELECT 테이블1.칼럼, 테이블2.칼럼, ...
FROM 테이블1, 테이블2
WHERE 테이블1.칼럼 = 테이블2.칼럼(+)     <테이블1이 기준일 때>
     테이블1.칼럼(+) = 테이블2.칼럼      <테이블2가 기준일 때>

이때 등가 조인(EQUI JOIN)과 외부 조인(OUTER JOIN)의 출력 결과를 비교해 보면 다음과 같다. 먼저 일치하는 값이 존재하는 행만 출력하는 등가 조인(EQUI JOIN) 출력 결과이다.

등가 조인(EQUI JOIN) 결과

 

등가 조인(EQUI JOIN) 결과 위와 같이 지도교수가 정해진 학생들만 출력된다. 

 

 

다음은 위에서 작성했던 SQL문을 통해 조회한 기준 테이블(student 테이블)의 모든 행을 출력하는 외부 조인(OUTER JOIN)의 출력 결과이다.

외부 조인(OUTER JOIN)의 출력 결과

 

외부 조인(OUTER JOIN)의 출력 결과 아직 지도교수가 정해지지 않은 학생들(빨간 네모 안의 학생들)도 모두 출력된다. 

 

 

 

 

EX) SUBJECT 테이블과 PROFESSOR 테이블을 조인하여 과목 코드, 과목명, 담당교수 출력(단, 담당교수가 정해지지 않은 수업 과목도 출력)

 

이 경우에는 아직 담당교수가 정해지지 않았더라도 모든 수업 과목을 출력해야 하기 때문에 subject 테이블이 기준 테이블이 된다. 따라서 professor 테이블에 (+)를 붙여준다.

select s.sub_id, s.name, p.name
from subject s, professor p
where s.profno = p.profno(+);                

 

 

 

 

 

 

<안시 조인(ANSI JOIN)>

 

안시 조인(ANSI JOIN)은 국제적 표준 조인 방법으로 데이터베이스 종류에 관계없이 모든 DB에서 사용할 수 있다. (오라클에서는 오라클 9i 버전부터 사용할 수 있다.) 또한 안시 조인(ANSI JOIN)은 조건절로 ON 절을 사용하는 것이 특징이다.

 

(1) INNER JOIN

 INNER JOIN은 두 테이블에서 일치하는 값이 있는 행만 출력하는 조인이다. 즉, 등가 조인(EQUI JOIN)과 동일한 출력 결과를 보인다. 

 

SELECT 테이블1.칼럼, 테이블2.칼럼, ...
FROM 테이블1 INNER JOIN 테이블2
ON 테이블1.칼럼 = 테이블2.칼럼

 

사용법은 위와 같이 FROM 절에 [기준 테이블 INNER JOIN 조인할 테이블]을 적는다.(INNER은 생략 가능하다.) 그리고 ON 절을 통해 조인 조건을 입력한다.

 

아래는 등가 조인(EQUI JOIN)과 ANSI INNER JOIN의 SQL문과 출력 결과 비교이다.

 

EX) student 테이블과 department 테이블을 조인하여 학번, 학생 이름, 학과번호, 학과명 출력

 

● 등가 조인(EQUI JOIN)

select s.studno, s.name, s.deptno, d.dname
from student s, department d
where s.deptno = d.deptno;

 

등가 조인(EQUI JOIN) 결과

 

 

● INNER JOIN

select s.studno, s.name, s.deptno, d.dname, d.loc
from student s inner join department d
on s.deptno = d.deptno;

 

INNER JOIN 결과

 

 

 

 

(1) OUTER JOIN

 OUTER JOIN은 기준 테이블 중 일부는 일치하는 값이 없더라도 모든 행을 출력하는 조인이다. 크게 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN으로 나뉘며 FROM 절에 LEFT / RIGHT / FULL과 함께 기준 테이블과 조인 대상 테이블을 입력한다. LEFT OUTER JOIN은 왼쪽 테이블이 기준 테이블이고 오른쪽 테이블이 대상 테이블이다. 즉, 일치하는 값이 없더라도 왼쪽 테이블은 모든 행이 다 출력된다(일치하는 값이 없으면 NULL값으로 표기). 반대로 RIGHT OUTER JOIN은 오른쪽 테이블이 기준 테이블이고 왼쪽 테이블이 대상 테이블이다. 오른쪽 테이블은 일치하는 값이 없더라도 다 출력된다.

SELECT 테이블1.칼럼, 테이블2.칼럼, ...
FROM 테이블1 [LEFT / RIGHT / FULL] OUTER JOIN  테이블2
ON 테이블1.칼럼 = 테이블2.칼럼

 

 

 

EX) student 테이블과 department 테이블을 조인하여 학번, 이름, 지도교수 번호, 지도교수명 출력(지도학생이 아직 없는 교수도 출력)

 

select s.studno, s.name, s.profno, p.name
from student s, professor d
where s.profno(+) = p.profno;                        ---- 기준테이블이 아닌 테이블(대상테이블)에 (+)입력
 
 
select s.studno, s.name, s.profno, p.name
from student right outer join professor p            ---- 기준테이블이 오른쪽
on s.profno = p.profno;