[MySQL] Join

Join

• 두개 이상의 테이블을 연결해서 데이터를 검색하는 방법
• 보통 두개 이상의 행(row)들의 공통된 기본키, 외래키를 사용해서 join 한다.
• 목적 : 현재 테이블에서 상대 테이블의 정보(column)를 산출하기 위한 처리

* Primary Key (PK)

테이블에서 중복되지 않는 column

 

* Foreign Key(FK)

다른 테이블에서 기본키로 설정되어 있는 column

 

> Join의 종류

 

Join 종류

 

• Inner join

select employee_id, first_name, e.department_id, d.department_id, d.department_name 
from employees e inner join departments d on e.department_id = d.department_id;

select * from employees e, departments d where e.department_id = d.department_id;

 

• Full outer join

-- full outer join
select e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
from employees e left outer join departments d on e.department_id = d.department_id
union
select e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
from employees e right outer join departments d on e.department_id = d.department_id;

right outer join UNION left outer join

 

• Cross join

-- cross join
select e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
from employees e cross join departments d;

거의 사용되지는 않음

 

• Left Outer join

-- left outer join
select e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
from employees e left outer join departments d on e.department_id = d.department_id;

join 되지 않은 데이터는 null이 들어가게 됨

 

select e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
from employees e left outer join departments d on e.department_id = d.department_id
where e.department_id is null;

left 차집합

 

• Right Outer join

-- right outer join
select e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
from employees e right outer join departments d on e.department_id = d.department_id;

join 되지 않은 데이터는 null이 들어가게 됨

 

• Self join : 동일한 테이블 join

-- self join
select emp.employee_id, emp.first_name, emp.manager_id, mgr.employee_id, mgr.first_name
from employees emp, employees mgr where emp.manager_id = mgr.employee_id;

다른 테이블이라고 생각해라!