고급 JOIN유형
Natural join
조인을 하고자 하는 두 테이블에서 컬럼명과 데이터타입이 같은 컬럼을 기준으로 조인을 함.
- employees 테이블 jobs 테이블 조인
select employee_id, first_name, job_id, job_title
from employees natural join jobs;
2개 이상 같은 컬럼이 있으면 조합값으로 조인함.
Using 절을 사용하여 조인
- 조인을 하고자 하는 두 테이블에서 컬럼명은 동일하나 데이터 타입이 다른 경우 사용할 수 있는 조인 유형
- USING절 뒤에 조인에 기준이 되는 컬럼명을 지정함.
- USING절 뒤에 작성된 컬럼은 쿼리 구문에서 접두어(테이블명 또는 테이블 alias)를 사용할 수 없음.
select employee_id, last_name, location_id, department_id
from employees join departments
using (department_id);
select l.city, d.department_name
from locations l join departments d
using (location_id)
where d.location_id =1400;→ ERROR!! using에 사용된 컬럼은 식별자 가지면 안됨
수정)
select l.city, d.department_name
from locations l join departments d
using (location_id)
where location_id =1400;
- 추가조인 유형
| natural join | using절 join | on절 join | |
|---|---|---|---|
| 컬럼명 | 동일 | 동일 | 상관없음 |
| 데이터타입 | 동일 | 상관없음 | 상관없음 |
| 문법 | from departments natural join locations | from departments join locations using(location_id) | from departments join locations on departments.location_id=locations.location_id |
Nonequijoin
| equi join | non-equi join |
|---|---|
| - 조인조건에 동등연산자(=)를 사용한 조인 유형 | - 조인조건에 동등연산자가 아닌 그외 비교연산자를 사용한 조인 유형 |
| - Natural join(=연산자내포) - Using절 join(=연산자내포) - On(=)절 join | - On(=이 아닌 비교연산자)절 join |
- job_grades 테이블 생성
select e.last_name, e.salary, j.grade_level
from employees e join job_grades j
on e.salary between j.lowest_sal and j.highest_sal;
OUTER join
조인 조건이 만족되는 행은 물론 조인 조건이 만족되지 않는 행까지 모두 반환해 주는 조인 유형
| inner join | outer join |
|---|---|
| - 내부조인 | - 외부조인 |
| - 조인조건을 만족하는 행만 반환하는 조인 유형 | - 조인조건을 만족하는 행과 만족하지 않는 행까지 반환하는 조인 유형 |
| - Natural join - Using절 join - On절 join | - left outer join - right outer join - full outer join |
LEFT OUTER join
조인 조건을 만족하지 않는 왼쪽 테이블의 행까지 모두 반환해 주는 조인 유형
select e.last_name, e.department_id, d.department_name
from employees e left outer join departments d
on (e.department_id=d.department_id);
부서가 없는 직원도 출력된다
RIGHT OUTER join
조인 조건을 만족하지 않는 오른쪽 테이블의 행까지 모두 반환해 주는 조인 유형
select e.last_name, d.department_id, d.department_name
from employees e right outer join departments d
on (e.department_id = d.department_id);
직원이 없는 부서도 출력된다
FULL OUTER join
조인 조건을 만족하지 않는 양쪽 테이블의 행까지 모두 반환해 주는 조인 유형
select e.last_name, d.department_id, d.department_name
from employees e full outer join departments d
on (e.department_id = d.department_id);
부서가 없는 직원, 직원이 없는 부서 전부 출력
Cartesian Product
Cross Join
- 조인을 하고자 하는 두 테이블의 모든 행을 조인하는 유형
- 다수의 행이 생성되므로 의미있는 결과는 아님.
- 모든 경우의 수를 만들고자할 때 사용됨.
select last_name, department_name
from employees
cross join departments;
- 2889행 출력됨
연습문제
1번)
select location_id, street_address, city, state_province, country_name
from locations natural join countries;
3번)
select e1.last_name "Employee", e1.employee_id "EMP#", e2.last_name "Manager", e1.manager_id "Mgr#"
from employees e1 left outer join employees e2
on e1.manager_id = e2.employee_id
order by e1.employee_id;
4번)
select e1.last_name, e1.hire_date, e2.last_name, e2.hire_date
from employees e1 join employees e2
on e1.manager_id = e2.employee_id
where e1.hire_date <e2.hire_date;
추가 연습 문제)
employees 테이블과 departments 테이블로부터 각 부서에 대한 부서번호, 부서이름, 위치 id 및 사원 수를 출력하는 쿼리구문을 작성하시오.
단, 사원이 없는 부서도 출력시키시오.
select d.department_id, d.department_name, d.location_id, count(e.employee_id)
from departments d left outer join employees e
on d.department_id = e.department_id
group by d.department_id, d.department_name, d.location_id
order by d.department_id;
