2개 이상의 테이블에서 원하는 데이터를 얻기위해 사용. 찾고자 하는 데이터가 다른 테이블에 존재할 때 기본 테이블의 주키를 이용하여 다른 테이블의 컬럼을 참고할 수 있다.
JOIN을 위해 주키와 외래키에 대해 잘 알아야 한다.
오라클에 연습용 테이블을 이용해 실습을 해보자.
문제> 'King'의 부서이름을 출력하시오.
King의 부서이름은 departments라는 테이블에 department_name으로 확인할 수 있다. 그러나 'King'이라는 이름은 employees테이블의 last_name을 통해 알 수 있으니 두 테이블의 조인을 통하여 원하는 값을 얻을 수 있다. 따라서 명령문은 다음과 같다.
select department_name from employees, departments
where employees.department_id = departments.department_id
and employees.last_name = 'King'
department_id를 서로 공유하는 컬럼이기 때문에 이를 통하여 서로의 값을 참조하는 조인테이블을 만들 수 있다.
테이블 명이 길 경우 이름을 변경하는 것도 가능하다. 위와 같은 결과 같을 나타내는 명령문이다.
select d.department_name from employees e, departments d
where e.department_id = d.department_id
and e.last_name = 'King'
JOIN의 또다른 방법이 존재한다. 명령어는 다음과 같다. 이 방법을 ANSI 조인이라 한다.
select d.department_name from employees
join departments on employees.department_id = departments.department_id
where employees.last_name = 'King'
* 조인 사용방법
1. 원하는 데이터는 무엇인지 명확히하라.
2. 원하는 데이터가 어느 테이블에 있는지 확인하라.
3. 원하는 데이터가 다른 테이블에 있다면 공통된 컬럼이 무엇인지 확인하라.(department_id)
mission> 'Sales'부서에 소속사원의 이름, 입사일과 부서이름을 출력하라.
select last_name, hire_date, department_name from employees
join departments on employees.department_id = departments.department_id
where departments.department_name = 'Sales'
* 3개 조인
사원이름, 부서번호, 부서이름, 직종번호, 직종이름, 도시이름
select e.last_name, e.department_id, d.department_name, j.job_id, j.job_title,
l.city from employees e, departments d, jobs j, locations l
where e.department_id = d.department_id and e.job_id = j.job_id
and d.location_id = l.location_id
* ANSI 조인
select e.last_name, e.department_id, d.department_name, j.job_id, j.job_title,
l.city from employees e
join departments d on e.department_id = d.department_id
join jobs j on e.job_id = j.job_id
join locations l on d.location_id = l.location_id
여기까지가 INNER 조인이다. INNER 조인은 두 개 이상의 테이블의 조인시 테이블에서 공통된 값을 출력한다. 그러나 공통이 되지 않은 데이터지만 확인이 필요한 데이터를 출력할 때가 있다. 이때 OUTER 조인이 필요하다.
* OUTER 조인
데이터가 존재하는 곳을 기준으로 공통되지 않는 데이터까지 출력한다.
즉, INNER JOIN에서 누락되는 데이터를 출력하도록 할 때 사용한다.
다음의 명령문을 보자.
select employee_id, last_name, hire_date from employees
where TO_CHAR(hire_date, 'YYYY-MM') between '1999-01' and '1999-06'
그러면 11개의 데이터가 출력된다. 여기서 부서이름까지 출력한다면 다음과 같다.
select employee_id, last_name, hire_date, department_name
from employees e, departments d
where e.department_id = d.department_id and
TO_CHAR(hire_date, 'YYYY-MM') between '1999-01' and '1999-06'
이렇게되면 10개의 데이터가 출력되면서 하나의 데이터가 빠진다. 그 이유는 department_id는 존재하나 department_name이 없는 경우 INNER JOIN에 의해 데이터가 제외되기 때문이다. 즉 공통된 데이터가 아니기 때문에 제외되는 것이다. 따라서 다음과 같이 OUTER 조인을 하면 모든 데이터를 확인 할 수 있다.
select employee_id, last_name, hire_date, department_name
from employees e, departments d
where e.department_id = d.department_id
(+) and
TO_CHAR(hire_date, 'YYYY-MM') between '1999-01' and '1999-06'
또는
select employee_id, last_name, hire_date, department_name
from employees e
left join departments d on e.department_id = d.department_id
where TO_CHAR(hire_date, 'YYYY-MM') between '1999-01' and '1999-06'
이렇게 누락되는 컬럼에 +를 시키던지 LEFT JOIN이라고 표현하여 오른쪽 테이블을 기준으로 왼쪽 테이블에 누락된 데이터를 JOIN시킴으로써 데이터를 누락시키지 않도록한다.
그러나 실무에서는 OUTER 조인은 조심스럽다. LEFT 조인을 하게되면 full 스캔을 하게된다.
따라서 성능면에서 비용이 발생한다.
* SELF 조인
자기자신을 참조하는 조인
select A.last_name, B.last_name from employees A, employees B
where A.manager_id = B.employee_id
A의 매니저는 B의 직원아이디를 뜻한다.
SELF 조인에서 INNER 조인을 하였다. 그렇기 때문에 두 테이블의 공통의 데이터만을 출력한다.
