[Oracle] 조인(join)

1. 조인

두 개 이상의 테이블을 연결하여 하나의 결과 테이블로 처리하는 조인 기능은 관계형 데이터 베이스가 갖는 특징 중에 하나이며, 이는 관계형 데이터베이스가 널리 사용되는 이유 중 하나입니다. 조인이랑 연관된 두개 이상의 테이블에서 관련있는 열을 이용하여 새로운 테이블을생성하는데 사용되는 연산입니다.

 

오라클에 조인의 종류는 Cross 조인, Equi 조인, Non-Equi 조인, Self 조인, Outer 조인 5가지 유형을 지원하고 있습니다. 

 

2. CROSS 조인

크로스 조인은 관계시스템의 관계 대수 8가지 연산 중 카티션 프로덕트를 구현한 것으로 2개 이상의 테이블을 조건 없이 결합하는 조인 연산을 의미합니다.

SELECT emp.*. dept.*

From emp cross join dept;

EMP 테이블의 데이터 수가 14개이고, DEPT 테이블의 개수가 4개 이므로 결과는 56개가 됩니다.

 

3. EQUI 조인

이퀴 조인은 조인 중 가장 많이 사용되는 조인으로, 2개 이상의 테이블에 관련 있는 공통 열의 값을 이용하여 논리적으로 결합하는 연산이 수행되는 조인으로, WHERE절에서 '='의 연산자를 사용합니다.

 

다음은 EMP, DEPT 테이블을 이퀴 조인하여 한 번에 사원의 정보와 부서명을 검색하는 질의문입니다. 이 때 EMP, DEPT 테이블에 부서번호가 존재함으로 테이블의 이름을 이용하여 한정시켜 주어야 합니다. 

SELECT empno, ename, job, emp.deptno, dname, dept.deptno

FROM emp, dept

WHERE emp.deptno = dept.deptno;

 

결과를 보면 emp.deptno와 dept.deptno 두 열이 같은 값을 가지고 있는 행만을 결과로 추출하게 됩니다. 이 때 부서번호는 두 개 테이블에 같은 이름으로 존재하므로 테이블의 이름을 이용하여 한정시켜 사용합니다.

 

다음은 CHICAGO와 DALLAS에 근무하는 사원들의 이름을 검색하는 예입니다.

SELECT ename, deptno, dname, loc

FROM emp, dept

WHERE emp.deptno = dept.deptno and

(loc = 'DALLAS or loc = "CHICAGO');

 

다음은 위의 질의문을 다른 문법의 형식으로 표현한 것입니다.

SELECT ename, emp.deptno, dname, loc

FROM emp natural join dept

WHERE loc = 'CHICAGO' or loc = 'DALLAS';

SELECT ename, emp.deptno, dname, loc

FROM emp join dept using(deptno)

WHERE loc = 'CHICAGO' or loc = 'DALLAS';

SELECT ename, emp.deptno, dname, loc

FROM emp join dept on emp.deptno = dept.deptno

WHERE loc = 'CHICAGO' or loc = 'DALLAS';

 

4. Non-Equi 조인

Non-Equi은 이퀴 조인에서 WHERE절에서 사용하는 '='이 아닌 연산자를 사용하는 경우입니다. 다음의 예에서처럼 WHERE절에서처럼 '='이 아닌 범위(BETWEEN ~ AND)를 이용한 질의문입니다. 

 

다음은 EMP 테이블의 각 SAL 열값이 SALGRADE 테이블의 LOSAL과 HISAL 사이의 범위에 포함될 때 사원의 급여 등급이 결정되는 질의문입니다. 이렇게 조인 조건에 '='외의 비교 연산자를 사용한 조인을 Non-Equi 조인이라 합니다.

SELECT empno, ename, sal, grade

FROM emp, salgrade

WHERE sal between losal and hisal;

 

5. SELF 조인

때때로 자기 자신 테이블과의 조인이 요구되어질 때가 있으며, 이런 경우 자기 자신과의 조인이 사용됩니다. 결국 FROM절에 같은 테이블의 이름이 두 개 존재하게 되며, 이를 구별하기 위해 다른 테이블의 별칭을 사용하여 조인을 구현하게 됩니다.

SELECT a.empno as 사원번호, a.ename as 사원이름,

       b.empno as 상급자사원번호, b.ename as 상급자이름

FROM emp a, emp b

WHERE a.mgr = b.empno;

질의문에서 보듯이 FROM절에 EMP 테이블이 두 번 사용되어, WHERE 절에서 이를 구분하기 위해 a, b로 별칭을 주고, 마치 다른 테이블인 것처럼 표현하게 됩니다. 이 때 4번째 라인의 WHERE의 조건이 비교 연산자 '='을 사용하였다면 결국 이퀴 조인과 같게 됩니다.

 

6. OUTER 조인

OUTER JOIN은 조인 조건을 만족하지 않는 행들도 질의 결과에 포함하기 위한 조인입니다. 즉 2개 이상의 테이블이 조인될 때 어느 한쪽의 테이블에는 해당하는 데이터가 있으나 다음 테이블에는 없는 경우 그 데이터는 검색되지 않는 문제를 해결하기 위한 조인입니다. 다음 문장은 모든 사원의 사원 번호, 사원 이름, 부서 번호, 부서이름, 부서 위치를 검색하는 질의문입니다.

SELECT empno, ename, emp.deptno, dname, loc

FROM emp, dept

WHERE emp.deptno = dept.deptno;

 

결과를 보면, emp 테이블에서는 모든 사원 정보가 모두 검색되었지만 DEPT 테이블에서는 10,20,30번 부서만이 검색되었습니다. 조인 조건에서는 "=" 연산자가 사용되었기 때문에 소속하는 사원이 없는 40번부서는 검색되지 않았습니다.

 

이러한 경우 DEPT 테이블의 모든 정보를 출력하고자 한다면 다음과 같은 문장을 실행하면 됩니다. 

SELECT empno, ename, dept.deptno, dname, loc

FROM emp right outer join dept

on emp.deptno = dept.deptno;

 

결과에서 보듯이 OUTER 조인이란 조인 조건이 만족하는 행이 없는 행도 출력하는 조인입니다. OUTER 조인은 다른 조인과 같이 추가 조건을 함께 사용할 수 있습니다. 

 

OUTER 조인에는 RIGHT OUTER JOIN, LEFT OUTER JOIN, FULL OUTER JOIN 세 가지가 있습니다.

 

다음은 SELF 조인과 OUTER 조인을 결합한 예문입니다.

SELECT a.empno as 사원번호, a.ename as 사원이름,

b.empno as 상급자사원번호, b.ename as 상급자이름

FROM emp a left outer join emp b on a.mgr = b.empno