조인: Cross, Equi , Non-Equi , Outer , Self , ANSI Join

조인

조인의 필요성

데이터베이스에서는 데이터가 중복되면 여러가지 이상현상이 발생하기 때문에 데이터를 중복되지 않도록 2개 이상의 테이블로 나누어서 정보를 저장한다. 원하는 정보를 얻어오려면 여러개의 테이블을 조인해야 한다.

SELECT * FROM EMP;

사원 정보 테이블 EMP를 보면 EMPNO, ENAME, JOB, MGR, HIREDATE, SALL, COMM, DEPTNO(부서번호)만 있고 부서명은 없다. 부서번호에 대한 부서이름은 부서테이블 DEPT에 있다.

예를 들어 SCOTT 사원이 소속된 부서명을 알고 싶으면 사원테이블 EMP에서 SCOTT이 소속된 부서번호를 확인 후, 부서 테이블에서 해당 부서번호에 대한 부서명을 얻어와야 한다.

SELECT DEPTNO FROM EMP WHERE ENAME = 'SCOTT'; -- SCOTT의 부서번호 확인

결과) 20

SELECT * FROM DEPT WHERE DEPTNO = 20;

결과) DETPNO는 20, DNAME은 RESEARCH, LOC는 DALLAS라고 출력된다.

조인의 종류

CROSS JOIN : 테이블 전체 데이터에 대한 조인

EQUI JOIN : 동일 컬럼을 기준으로 조인

NON-EQUI JOIN : 동일 컬럼 없이 다른 조건을 사용하여 조인

SELF JOIN : 한 테이블 내에서 조인

OUTER JOIN : 조인 조건에 만족하지 않는 로우도 나타낸다. (+)는 오라클만 쓰는 비표준 방식이므로 표준 방식인 ANSI OUTER JOIN의 [LEFT, RIGHT, FULL]을 사용해야 한다.

ANSI JOIN : 표준 조인 방식으로 위의 JOIN 방식들에 대한 표준 규격을 제공한다. 

1) 동일컬럼인 경우 INNER JOIN~ON절, USING(공통컬럼), NATURAL JOIN을 쓸 수 있다.

2) 어느 한쪽엔 데이터가 없는 OUTER JOIN인 경우 [LEFT, RIGHT, FULL] OUTER JOIN을 쓸 수 있다.

1. CROSS JOIN

2개 이상의 테이블이 조인될 때 WHERE절에 의해 공통되는 컬럼에 의한 결합이 발생하지 않는다.

SELECT * FROM EMP, DEPT;

-- 사원테이블 EMP와 부서테이블 DEPT를 한꺼번에 출력

SELECT * FROM EMP DEPT;

-- 표준 규격인 ANSI 크로스 조인으로 쉼표(,) 없이 사용 가능.

2. EQUI JOIN

두 테이블에서 공통적으로 존재하는 컬럼의 값이 일치하는 행을 연결하여 결과를 생성

SELECT * FROM EMP, DEPT 

WHERE EMP.DEPTNO = DEPT.DEPTNO

- EMP 테이블의 부서번호와 DEPT 테이블의 부서번호가 같은 것을 출력

SELECT EMP.ENAME, DEPT.DNAME FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO

AND EMP.ENAME = 'SCOTT'; -- AND 연산자를 통해 검색조건을 추가할 수 있다.

- EMP테이블의 사원명, DEPT테이블의 부서명을 출력하는데,

- EMP테이블과 DEPT테이블에서 부서명이 같을 때,

- EMP테이블 중 SCOTT 이라는 사람

SELECT E.ENAME, D.DNAME 

FROM EMP E, DEPT D -- 각 테이블에 별칭 부여

WHERE E.DEPTNO = D.DEPTNO -- 점(.) 앞에 붙여야 할 테이블명 대신 별칭 사용이 가능해진다.

- E나 D처럼 테이블명에 별칭(Alias)을 줘서 사용할 수 있다.

1) ANSI INNER JOIN

동일 컬럼을 기준으로 조인하기 위한 Equi Join을 표준 규격 ANSI Join에서는 

FROM 다음에 INNER JOIN이란 단어를 사용하여 조인할 테이블 이름을 명시하고, ON 절을 사용하여 조인 조건을 명시한다.

SELECT *FROM table1 INNER JOIN table2

ON table1.column1 = table2.column2

예제 1) 

SELECT ENAME, DNAME 

FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO;

위의 Equi Join을 표준으로 바꾸면 ANSI INNER JOIN이 된다.

SELECT ENAME, DNAME 

FROM EMP INNER JOIN DEPT -- FROM절에서 조인할 테이블을 콤마 대신 INNER JOIN으로 연결

ON EMP.DEPTNO = DEPT.DEPTNO; -- 공통컬럼을 WHERE 대신 ON으로 조인 조건 명시

예제 2)

SELECT EMP.ENAME, DEPT.DNAME FROM EMP, DEPT

WHERE EMP.DEPTNO = DEPT.DEPTNO

AND EMP.ENAME = 'SCOTT';

위의 Equi Join도 ANSI INNER JOIN으로 바꾸면 다음과 같다.

SELECT ENAME, DNAME 

FROM EMP INNER JOIN DEPT

ON EMP.DEPTNO = DEPT.DEPTNO

WHERE ENAME = 'SCOTT';

조인 정보를 ON절에 기술하여 조인 조건을 명확하게 지정하고, 다른 조건을 WHERE문에서 지정하면 된다.

2) USING을 이용한 조인 조건 지정하기

동등 조인에서는 USING으로 공통컬럼을 묶어 ON절보다 더 간단하게 표현할 수 있다.

SELECT * FROM table1 INNER JOIN table2

USING (공통컬럼)

SELECT EMP.ENAME, DEPT.DNAME 

FROM EMP INNER JOIN DEPT

USING (DEPTNO); 

2) NATURAL JOIN

조건절을 생략하고 NATURAL JOIN을 사용하면 자동적으로 모든 컬럼을 대상으로 공통 컬럼을 조사하여 내부적으로 조인문을 생성한다.

SELECT * FROM table1 NATURAL JOIN table2

SELECT ENAME, DNAME 

FROM EMP NATURAL JOIN DEPT;

3. Non-EQUI JOIN

조인 조건이 범위 내에 있는지 조사. 동일 컬럼 없이 다른 조건을 사용하여 조인한다.

다음 급여동급 테이블의 예를 보자.

SELECT * FROM SALGRADE; 

1등급(700~1200), 2등급(1201~1400), 3등급(1401~2000), 4등급(2001~3000), 5등급(3001~9999)

급여등급을 위와 같이 5개로 나눠놓은 SALGRADE 테이블의 정보를 가져와 사원테이블 EMP에서 각 사원의 급여 등급을 지정할 수 있다.

SELECT ENAME, SAL, GRADE FROM EMP, SALGRADE

WHERE SAL BETWEEN LOSAL AND HISAL;

EMP 테이블에서 ENAME과 SAL, SALGRADE 테이블에서 GRADE를 가져온다.

조건은 EMP테이블의 SAL이 SALGRADE 테이블의 LOSAL부터 HISAL 사이일 때.

이렇게 하면 조건에 따라 LOSAL부터 HISAL 사이의 GRADE값이 SAL에 맞춰 부여된다.

위의 쿼리문을 다음과 같이 별칭을 부여하고, BETWEEN 연산자 대신 일반연산자를 써서 표현해도 동일한 결과가 나온다.

SELECT E.ENAME, E.SAL, S.GRADE

FROM EMP E, SALGRADE S

WHERE E.SAL >= S.LOSAL AND E.SAL <= S.HISAL;

예제) 한 명의 사원 이름과 소속부서, 급여 등급을 출력한다. 

사원 테이블 EMP에는 부서 이름과 급여 등급은 없고 부서번호와 급여만 있다. 

사원 테이블의 부서번호로 부서테이블 DEPT를 참조하여 부서의 이름을 얻어와야 한다. 

다음 사원테이블의 급여로 급여등급 테이블 SALGRADE를 참조하여 등급을 얻어와야 한다.

SELECT E.ENAME, D.DNAME, S.GRADE

FROM EMP E, DEPT D, SALGRADE S

WHERE E.DEPTNO = D.DEPTNO

AND E.SAL BETWEEN S.LOSAL AND S.HISAL;

EMP, DEPT, SALGRADE 3개의 테이블을 조인하여 결과를 출력한다.

4. SELF JOIN

한 테이블 내에서 조인. 자기 자신과 조인.

SELECT ENAME, MGR FROM EMP;

사원명(ENAME) SMITH의 매니저 사원번호(MGR)가 7902인데, 사원번호(EMPNO)가 7902인 사원명(ENAME)으로 SMITH의 매니어 이름을 알아내야 한다. 이 정보는 모두 사원 테이블인 EMP 테이블 내에 존재한다.

SELECT EMPLOYEE.ENAME, MANAGER.ENAME

FROM EMP EMPLOYEE, EMP MANAGER 

WHERE EMPLOYEE.MGR = MANAGER.EMPNO;

-- EMP 테이블을 사원테이블(EMPLOEE)과 매니저테이블(MANAGER)이란 별칭으로 지정

-- 사원테이블의 매니저 사원번호(MGR)과 매니저테이블의 사원번호(EMPNO)가 같은 경우 사원테이블의 이름과 매니저테이블의 이름을 출력한다.

한 개의 테이블 내에서 별칭을 2개로 잡아 특정 직원을 담당하는 매니저 사원번호(MGR)와 매니저의 사원번호(EMPNO)가 같은 경우를 검색해낼 수 있다.

좀 더 알기쉽게 출력하면 다음과 같다.

SELECT EMPLOYEE.ENAME || '의 매니저는' || MANAGER.ENAME || '입니다.'

FROM EMP EMPLOYEE, EMP MANAGER

WHERE EMPLOYEE.MGR = MANAGER.EMPNO;

문제) 사원테이블 EMP에서 매니저가 KING인 사원의 이름과 직급을 출력

SELECT WORK.ENAME, WORK.JOB

FROM EMP WORK, EMP MANAGER

WHERE WORK.MGR = MANAGER.EMPNO

AND MANAGER.ENAME = 'KING';

- EMP 테이블을 WORK 테이블과 MANAGER 테이블로 별칭 지정

- WORK 테이블의 MGR이 MANAGER 테이블의 EMPNO와 같은 경우에서 MANAGER 테이블의 ENAME이 KING인 경우로 검색

- WORK 테이블의 사원명과 직급을 출력

문제) SCOTT과 동일한 부서에서 근무하는 사원의 이름을 출력하시오.

SELECT WORK.ENAME, FRIEND.ENAME

FROM EMP WORK, EMP FRIEND

WHERE WORK.DEPTNO = FRIEND.DEPTNO

AND WORK.ENAME = 'SCOTT'

AND FRIEND.ENAME <> 'SCOTT';

- EMP 테이블을 WORK 테이블과 FRIEND 테이블로 별칭 지정

- WORK 테이블의 부서명이 FRIEND 테이블의 부서명과 같은 경우로 검색

- WORK의 ENAME은 SCOTT, FIREND의 ENAME은 SCOTT이 아닌 경우로 검색

5. ANSI OUTER JOIN

2개 이상의 테이블이 조인될 때 어느 한쪽에는 해당 데이터가 있는데 다른쪽 테이블에는 데이터가 존재하지 않는 경우 그 데이터는 출력되지 않는 문제를 해결하기 위해 OUTER JOIN을 쓴다. 책에 나온 OUTER JOIN에 쓰는 (+)는 오라클만의 독자규격이라 호환이 안되므로 여기에서는 표준 규격인 ANSI OUTER JOIN으로 설명.

SELECT * FROM table1 [LEFT | RIGHT | FULL] OUTER JOIN table2

예를 들어 다음과 같이 특정 데이터는 중복되고 특정 데이터는 중복되지 않는 테이블이 있다.

SELECT * FROM DEPT01;

SELECT * FROM DEPT02;

1) LEFT OUTER JOIN

ANSI JOIN 에서는 OUTER JOIN절을 기준으로 테이블명을 왼쪽, 오른쪽에 기술하는데, 왼쪽 테이블을 기준으로 잡을 때에는 LEFT OUTER JOIN을 사용한다.

SELECT *

FROM DEPT01 LEFT OUTER JOIN DEPT02

ON DEPT01.DEPTNO = DEPT02.DEPTNO;

10번, 20번의 데이터가 있는 DEPT01을 중심으로, 10번만 있고 20번의 데이터가 없는 DEPT02의 내용도 함께 출력하고자 할 때 OUTER JOIN을 써야 한다. 데이터가 있는 쪽이 왼쪽이므로 LEFT OUTER JOIN이라 쓴다. 

2) RIGHT OUTER JOIN

오른쪽 테이블을 기준으로 잡을 때에는 RIGHT OUTER JOIN을 사용한다.

SELECT * 

FROM DEPT01 RIGHT OUTER JOIN DEPT02

USING(DEPTNO);  -- USING절로 공통컬럼을 표현

10번과 30번이 있는 오른쪽의 DEPT02를 중심으로 결과물이 출력된다.

3) FULL OUTER JOIN

2개의 테이블 사이에서 동일한 데이터는 물론 동일하지 않은 데이터까지 모두 표시할 수 있다. LEFT OUTER JOIN과 RIGHT OUTER JOIN을 합한 형태.

SELECT * 

FROM DEPT01 FULL OUTER JOIN DEPT02

USING(DEPTNO);

10번과 20번이 있는 DEPT01, 10번과 30번이 있는 DEPT02를 모두 함께 출력한다.