MySQL은 JOIN을 지원한다.
둘 이상의 테이블에서 데이터가 필요한 경우 JOIN을 사용한다.
JOIN의 특징은 아래와 같다.
- 일반적으로 JOIN 조건을 포함하는 where 절을 테이블 수 - 1개 만큼 작성한다.
- 일반적으로 테이블의 기본 키나 외래 키로 구성된다.
JOIN에는 INNER JOIN과 OUTER JOIN이 있다. 하나씩 알아보자.
MySQL은 INNER JOIN을 지원한다.
N개의 테이블 간 교집합을 이용하여 합쳐지는 가장 일반적인 JOIN을 INNER JOIN이라고 한다.
아래는 INNER JOIN의 예시이다.
select e.employee_id, e.first_name, e.salary, e.department_id, d.department_name
from employees e (inner) join departments d
on e.department_id = d.department_id
where e.employee_id = 100;일반적으로 join하는 테이블은 모두 alias로 별칭을 붙여준다.
또한, join 조건은 on 절에 일반 조건은 where 절에 사용한다.
USING을 사용하여 INNER JOIN을 할 수도 있다.
select e.employee_id, e.first_name, e.salary, e.department_id, d.department_name
from employees e (inner) join departments d
using (department_id)
where e.employee_id = 100;두 sql 모두 사번이 100인 사원을 출력한다.
조건을 명시하지 않은 INNER JOIN을 NATURAL JOIN이라고 한다. 아래 예시를 살펴보자.
select d.department_id, d.department_name, l.city
from departments d natural join locations l
where d.department_id = 10;NATURAL JOIN은 자동으로 JOIN 되는 테이블들 중 이름과 타입이 같은 열을 모두 JOIN 조건으로 묶어준다.
따라서 department_id가 10인 부서가 출력된다.
※ NATURAL JOIN은 이름과 타입이 같은 열을 모두 JOIN 조건으로 묶기 때문에 의도하지 않은 결과를 부를 수도 있으므로 사용에 주의가 필요하다.
MySQL은 OUTER JOIN을 지원한다.
INNER JOIN은 한쪽 테이블에는 데이터가 존재하지만 다른 테이블에는 데이터가 존재하지 않을 때 출력되지 않는다는 단점이 있다. 이를 극복하고자 MySQL은 OUTER JOIN을 지원한다.
OUTER JOIN의 종류는 LEFT OUTER JOIN, RIGHT OUTER JOIN이 있다. 하나씩 살펴보자.
왼쪽 테이블 중 JOIN 조건에 일치하지 않는 데이터를 출력하고자 할 때 LEFT OUTER JOIN을 사용한다. 사용 예시는 아래와 같다.
select e.employee_id, e.first_name, d.department_name
from employees e left outer join departments d
using (department_id);출력 결과에는 부서가 없는 사원도 출력된다.
오른쪽 테이블 중 JOIN 조건에 일치하지 않는 데이터를 출력하고자 할 때 RIGHT OUTER JOIN을 사용한다. 사용 예시는 아래와 같다.
select d.department_name, e.employee_id, e.first_name
from employees e right outer join departments d
using (department_id);출력 결과에는 직원이 없는 부서도 출력된다.
MySQL은 SubQuery를 지원한다.
SubQuery란 다른 쿼리 내부에 포함되어 있는 Select 문을 의미한다.
SubQuery 사용 시 주의점은 아래와 같다.
- 반드시 ()로 감싸진다.
- 반드시 단일 행 또는 다중 행 비교 연산자와 함께 사용된다.
SubQuery의 종류는 아래와 같다.
- 중첩 서브 쿼리 : where 문에 작성하는 서브 쿼리
- 인라인 뷰 : from 문에 작성하는 서브 쿼리
- 스칼라 서브 쿼리 : select 문에 작성하는 서브 쿼리
MySQL은 중첩 서브 쿼리를 지원한다.
where절에 사용되는 서브 쿼리를 중첩 서브 쿼리라고 한다.
중첩 서브 쿼리는 단일 행 혹은 다중 행 혹은 다중 열을 반환한다.
단일 행 반환 예시부터 알아보자. 아래 예시를 살펴보자.
select employee_id, first_name, department_id
from employees
where department_id = (
select department_id
from employees
where first_name = 'adam'
);위 경우에서 이름이 adam인 사람은 단 한 명임을 보장한다.
따라서 adam과 같은 department_id를 가진 직원들을 출력한다.
다음엔 다중 행 반환에 대해 알아보자. 다중 행 비교 연산자는 아래와 같다.
- in : 서브 쿼리 결과 중 단 하나라도 일치하면 참
- any : 서브 쿼리 결과 중 단 하나라도 만족하면 참
- all : 서브 쿼리 결과를 모두 만족해야 참
다중 행 사용 예시는 아래와 같다.
select employee_id, first_name
from employees
where salary > any (
select salary
from employees
where department_id = 30
)
order by salary;위 코드를 실행하면 모든 직원들 중 department_id가 30인 사람들의 급여 중 하나라도 급여가 높으면 출력된다.
이 때, 급여로 오름차순되어 출력된다.
마지막으로 다중 열 반환 예시를 살펴보자. 아래 예시를 살펴보자.
select employee_id, first_name
from employees
where (salary, department_id) in (
select salary, department_id
from employees
where commission_pct is not null
and manager_id = 148
);위 코드를 실행하면 manager_id가 148번이고, 커미션은 받는 직원의 급여와 department_id가 같은 직원이 출력된다.
MySQL은 인라인 뷰를 지원한다.
FROM절에 사용되는 서브 쿼리를 인라인 뷰라고 한다.
인라인 뷰는 임시적인 뷰이기 때문에 데이터베이스에는 저장되지 않는다.
인라인 뷰 사용 예시는 아래와 같다.
select e.employee_id, e.first_name, e.salary, e.department_id
from (
select distinct department_id
from employees
where salary < (select avg(salary) from employees)
) d join employees e
on d.department_id = e.department_id;위 코드를 실행하면 모든 사원의 평균 급여보다 급여가 낮은 사원들과 같은 부서에서 일하는 사원들이 출력된다.
MySQL은 스칼라 서브 쿼리를 지원한다.
SELECT절에 사용되는 서브 쿼리를 스칼라 서브 쿼리라고 한다.
스칼라 서브 쿼리는 하나의 행만 반환한다.
스칼라 서브 쿼리 사용 예시는 아래와 같다.
select e.employee_id, e.first_name, salary, department_id,
(select avg(salary) from employees where department_id = 60) as avg60
from employees e
where department_id = 60;위 코드를 실행하면 department_id가 60인 사람들이 출력된다. 이 때 모든 출력 결과에 department_id가 60인 사람들의 평균 급여가 함께 출력된다.
