[Oracle] 06. 자기참조조인(Self Join), 외부조인(Outer Join), 자연조인(Natural Join)

자기 참조 조인(Self Join)

동일한 테이블에 다른 별명을 붙여 마치 여러 개의 테이블 간에 조인하는 것처럼 만들어, 자기 자신을 참조하는 조인 방법. 테이블에 반드시 별명을 붙여야 함으로 '별명을 이용한 조인'이라고 부른다.

예제1) 사원을 관리하는 관리자 검색

각 사원을 담당하는 관리자가 존재하는데, 그 관리자는 사원의 담당자이자 동시에 회사의 입장에서는 하나의 사원임으로 사원 테이블에서 자기 참조를 이용해 검색할 수 있다.

SELECT e1.eno 사번, e1.ename 이름, e2.ename 관리자 FROM emp4 e1, emp4 e2
WHERE e1.mgr = e2.eno;

예제2) 학생 중 동명이인 검색

같은 학생 테이블 안에서 이름은 같으나 학번은 다른 학생을 검색해야 하므로 학생 테이블 안에서 자기 참조를 사용한다.

SELECT DISTINCT s1.sno 학번, s1.sname 학생명 FROM student s1, student s2 
WHERE s1.sname = s2.sname AND s1.sno != s2.sno;

외부 조인(Outer Join)

여러 개의 테이블을 조인할 때 한 쪽 테이블에 NULL인 데이터가 존재할 때 조인조건이 일치하지 않아 그 데이터는 검색되지 않을 것이다. 이 때 정확한 결과물 해석을 위해 NULL 값을 포함한 모든 데이터를 확인하고 싶을 때 외부조인을 사용한다. 데이터가 부족한 쪽에 (+) 기호를 기술한다.

예제) 전체 교수 명단과 교수가 담당하는 과목의 이름을 학과 순으로 검색

일반조인을 했을 때의 결과는 p.pno=cs.pno의 조인조건을 만족하는 데이터만 확인할 수 있으나, 하단의 쿼리처럼 외부조인을 하게 되면 담당과목이 없는 교수들을 포함한 전체 교수명단을 확인할 수 있게 된다.

일반 조인 결과

SELECT p.pno 교수번호, p.pname 교수명, cs.cname 과목명, p.section 학과
FROM professor p, course cs
WHERE p.pno=cs.pno(+)
ORDER BY 학과 ASC;

외부 조인 결과

자연 조인(Natural Join)

SELECT  컬럼, ...
FROM    테이블1
NATURAL JOIN  테이블2
WHERE 검색_조건;

동일한 타입과 이름을 가진 컬럼을 조인조건으로 이용하는 조인문장을 좀 더 간단히 표현하는 조인 방법. 일반 조인 구문과는 달리, 조인조건을 쓰지 않아도 자연스럽게~ 조인이 된다. 코드가 훨씬 간결해지는 것을 알 수 있다.

--각 사원의 근무부서를 검색한다(일반조인)
SELECT eno 사번, ename 이름, dept.dno 부서번호, dname 부서명
FROM dept, emp WHERE dept.dno=emp.dno;

--각 사원의 근무부서를 검색한다(자연조인)
SELECT eno 사번, ename 이름, dno 부서번호, dname 부서명 
FROM  emp NATURAL JOIN dept;