[SQL 13] 조인 - 자체 조인(Self Join), 외부 조인(Outer Join)

샘플 스키마 : HR

설명하는 과정에서 예제로 주어진 문장은 눈으로 보지만 말고 직접 실행시켜 보길 권한다.

No. 01
자체 조인(Self Join)

자기 참조 조인이라고도 한다.

동일 테이블의 다른 컬럼을 참조하는 컬럼을 이용해서 동일 테이블간 조인하는 방법이다.

기본 구조

SELECT 별명1.컬럼, 별명2.컬럼, …

FROM 테이블 별명1, 테이블 별명2, …

WHERE 조인 조건

[AND 일반 조건];

• 동일 테이블을 자기 참조에 의해 조인한다.
• 테이블에 반드시 별명을 붙여야 한다.

자체 조인은 하나의 테이블에 여러 별명을 붙여서 마치 서로 다른 테이블인 것처럼 조인한다.

◈ 예제

▶ 각 사원을 관리하는 상사의 이름을 검색한다.

select e1.employee_id 사원번호, e1.first_name 사원이름, e1.manager_id 상사번호,

e2.employee_id "상사의 사원번호", e2.first_name 상사이름

from employees e1, employees e2

where e1.manager_id = e2.employee_id;

EMPLOYEES 테이블의 manager_id(상사번호) 컬럼은 자신의 상사의 employee_id(사원번호)이다.

그 상사도 또한 회사의 사원이기 때문에 e1 테이블의 manager_id 컬럼을 e2 테이블의 employee_id 컬럼으로 참조한다.

자주 쓰이지는 않지만, 위와 같은 경우에 자체 조인을 사용한다.

자기 자신을 조인한다고 해서 어려운것이 아니고 서로 다른 별명을 별개의 테이블로 생각하면 보통의 등가 조인이나 비등가 조인과 동일하다.

No. 02
외부 조인(Outer Join)

포괄 조인이라고도 한다.

기본 구조

SELECT 테이블1.컬럼, 테이블2.컬럼, …

FROM 테이블1, 테이블2

WHERE 조인 조건(+)

[AND 일반 조건];

• 조인 조건에 일치하지 않는 데이터까지 모두 출력해준다.
• 출력된 데이터의 신뢰성을 제공해주기 위한 방법이다.
• '+'기호는 데이터가 적은 쪽에 기술한다.
• 부족한 쪽 데이터는 자동으로 NULL 값으로 설정된다.

◈ 예제

▶ 각 부서별로 사원을 검색한다.

1) 일반 조인인 경우

select d.department_id 부서번호, department_name 부서명, first_name 사원이름

from departments d, employees e

where d.department_id = e.department_id

order by 1 desc;

2) 외부 조인의 경우

select d.department_id 부서번호, department_name 부서명, first_name 사원이름

from departments d, employees e

where d.department_id = e.department_id(+)

order by 1 desc;

일반 조인과 외부 조인의 출력 결과를 비교해보자.

DEPARTMENTS 테이블에는 100번부터 270번 부서까지 총 27개의 부서가 있지만, 일반 조인의 경우 모든 부서가 검색되지 않는다. 그 이유는 사원이 한명도 속하지 않은 부서도 있기 때문이다. 부서별로 사원을 검색하는데 사원이 없는 부서까지 함께 검색이 필요한 경우가 있을 것이다.

예를 들어 사원이 없는 120~270번 부서가 내년에 새로 만들어질 부서라고 가정해보자. 다음 년도 예산을 결정하기 위해 일반 조인으로 검색된 결과를 보고서에 제출한다면 120~270번 부서는 없는 부서로 인식되고 내년에 개설되어 업무를 시작할 부서임에도 불구하고 예산이 확보되지 않아 업무상 오류가 발생할 수 있다. 이런 오류를 방지하기 위해 사용하는 것이 외부 조인이다.

외부 조인은 데이터가 부족한 쪽 조인조건에 '(+)' 기호를 추가한다. 데이터가 적은 쪽 테이블과 매칭되지 않는 컬럼들은 자동으로 NULL값으로 설정된다.

select d.department_id 부서번호, department_name 부서명, first_name 사원이름

from departments d, employees e

where d.department_id(+) = e.department_id

order by 1 desc;

이번에는 DEPARTMENTS 쪽에 데이터가 부족하다고 (+)기호를 추가했다.

아까와는 다르게 부서가 없는 사원의 이름까지 모두 검색된다.

결론적으로 말하면

데이터가 부족한 쪽에 (+) 기호를 추가하면, 부족하지 않은 쪽 데이터는 모두 검색되고 매칭되지 않는 행은 NULL값으로 설정된다.

단, (+)기호는 조인 조건 양쪽에 동시에 기술할 수 없다.