JOIN - INNER JOIN, NATURAL JOIN, OUTER JOIN

JOIN

• 둘 이상의 테이블에서 데이터가 필요한 경우 테이블 조인
• 일반적으로 조인 조건을 포함하는 WHERE 절을 작성해야 한다.
• 조인 조건은 일반적으로 각 테이블의 PK 및 FK 로 구성된다.

종류

• INNER JOIN : 직접 조건 주기 (on , using)
• OUTER JOIN:조건에안맞는것까지다출력
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN

 

JOIN 조건의 따른 구분

• NATURAL JOIN : 두 테이블 간 동일 컬럼을 모두 조인 조건으로 사용
• CROSS JOIN(FULL JOIN, CARTESIAN JOIN) : 일부러 조건을 안 줘서 mxn개 출력

 

employees 테이블과 departments 테이블을 'where 절'로 join

select e.employee_id, e.first_name, e.department_id, d.department_id, d.department_name
from employees e, departments d
where e.department_id = d.department_id;

---

INNER JOIN

• 가장 일반적인 JOIN
• Equi-Join 이라고도 하고 N개 테이블 join 시 N-1 개의 조인 조건이 필요하다.

- on

-- on 키워드로 join 조건 준다. 보통 on - join 조건, where - 일반 조건
select e.employee_id, e.first_name, d.department_id, d.department_name
from employees e inner join departments d
on e.department_id = d.department_id;

- using

-- using 으로 join 조건 준다. using 내에는 alias 쓰지 말기~!
select e.employee_id, e.first_name, d.department_id, d.department_name
from employees e inner join departments d
using (department_id)
where employee_id = 100;

using 절 내에는 table 이름이나 alias 명시하면 에러남!!!

 

---

NATURAL JOIN

• 두 테이블을 자연조인하면 따로 조건을 주지 않아도 두 테이블의 동일컬럼을 기준으로 JOIN 한다.

사원의 사원번호, 이름, 부서아이디, 부서이름을 출력하라.

-- natural join : 같은 컬럼들 다 확인하는데 같은 컬럼이 여기서 2개라서 department_id 로 join이 안 됨!
-- 에시로 100번인 사람이 manager_id null 이라서 조인결과에서 사라짐,,,
select e.employee_id, e.first_name, d.department_id, d.department_name
from employees e natural join departments d;

employees 와 departments 테이블의 동일컬럼은 department_id, manager_id 로 두 개이기 때문에

기준점이 두 테이블중 department_id, manager_id 이 같은 데이터만 출력한다.

 

즉 위의 구문은

SELECT
FROM employees e INNER JOIN departments d 
ON e.department_i = d.department_id 
AND e.manager_id = d.manager_id;

-- 혹은


SELECT 
FROM employees e INNER JOIN departments d 
USING (department_id, manager_id);

이 상태다.

 

 

여기서 의도한 기준점은 department_id 이니까 

select e.employee_id, e.first_name, e.department_id, d.department_name
from employees e inner join departments d
using (department_id);

이렇게 바꾸면 원하던 데이터가 나온다.

 

그런데, 문제는 department_id 가 없는 사원은 출력이 안된다.

그래서 OUTER JOIN 을 사용하면, 기준점이 되는 컬럼이 NULL 이어도 출력되게 할 수 있다.

 

---

OUTER JOIN

• 종류 : left outer join, right outer join, full outer join
• 어느 한쪽 테이블에는 해당하는 데이터가 존재하는데 다른 쪽 테이블에는 데이터가 존재하지 않을 경우 그 데이터가 검색되지 않는 문제점을 해결하기 위해 사용

---

• LEFT OUTER JOIN

왼쪽 테이블을 기준으로 JOIN 기준에 일치하지 않는 데이터까지 출력

ex)

회사에 근무하는 모든 사원의 사번, 이름, 부서이름을 두 개의 테이블 join 으로 출력해보자

select e.employee_id, e.first_name, e.department_id, d.department_name
from employees e join departments d
using (department_id);

위처럼 하면 employees 테이블에서 department_name이 null 인 컬럼이 안나옴!

select e.employee_id, e.first_name, e.department_id, d.department_name
from employees e left outer join departments d
using (department_id);

그래서 left outer join 을 사용하여 employees 테이블에서 department_name이 null 인 컬럼도 출력

 

 

• RIGHT OUTER JOIN

오른쪽 테이블을 기준으로 JOIN 조건에 일치하지 않는 데이터까지 출력

 

ex) 회사에 존재하는 모든 부서의 부서이름과 부서에서 근무하는 사원의 사번, 이름 출력

select e.employee_id, e.first_name, e.department_id, d.department_name
from employees e join departments d
using (department_id);

위처럼 하면 사원이 없는 부서는 안 나옴

 

select e.employee_id, e.first_name, d.department_id, d.department_name
from employees e right outer join departments d
using (department_id)
order by d.department_id;

right outer join 사용하면 사원이 없는 부서까지도 다 출력

 

 

 

• full outer join 

양쪽 테이블을 기준으로 JOIN 조건에 일치하지 않는 데이터까지 출력한다.

MySQL 은 지원하지 않음.

---

SELF JOIN

- 자신 테이블을 join

자신의 매니저보다 연봉을 많이 받는 직원들의 성과 연봉을 출력하시오.

SELECT e.last_name , e.salary 
FROM employees e INNER JOIN employees m
ON e.manager_id = m.employee_id 
WHERE e.salary > m.salary ;

 

 

---