MySQL - [JOIN (Inner Join / Outer Join / Cross Join / Self Join) ]
JOIN
SELECT table1별칭.컬럼1, ..., table2별칭.컬럼1, ...
FROM table1 [table1의 별칭] JOIN table2 [table2의 별칭]
ON table1별칭.컬럼1 = table2별칭.컬럼2
[WHERE 조건문];
- 두 개 이상의 테이블을 서로 연결하여 데이터를 검색할 때 사용하는 방법
- 테이블에서 공통된 데이터가 저장된 컬럼을 찾아서 조인 구문을 작성해야함. 컬럼명이 같을 필요는 없음
- 성능 개선을 위해서 from절에 테이블 alias를 선언하여, 컬럼명 앞에 테이블 alias를 붙여줌
- 한쪽 테이블에만 있는 유일한 컬럼명 앞에는 테이블명이나 테이블 alias명이 필수는 아니나 붙여주면 성능 개선에 도움이 되므로 권장하고 있음
- (Inner) Join, Outer Join, Cross Join, Self Join 가능
1. 이너조인
- 기준 테이블(table1), 조인 테이블(table2)에 조인 컬럼에 해당하는 값이 모두 존재하는 경우에만 데이터가 조회됨
- table1과 table2의 어떤 컬럼을 기준으로 할지는 ON 뒤에 작성
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
FROM employees e [INNER] JOIN departments d
ON e.department_id = d.department_id;
2. 아우터조인
- 두 테이블의 공통영역을 포함해 한쪽 테이블의 다른 데이터를 포함하는 조인방식
- 조인조건을 만족하는 행은 물론이고 조인조건을 만족하지 않는 행까지 반환
- left와 outer를 정하는 기준은 from절에 적어준 테이블이 left가 되고, join절에 적어준 테이블이 right가 된다.
1) LEFT OUTER JOIN
- 조인 조건을 만족하지 않는 왼쪽 테이블의 행도 함께 반환해 주는 조인 유형
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;
2) RIGHT OUTER JOIN
- 조인조건을 만족하지 않는 오른쪽 테이블의 행도 함께 반환해 주는 조인 유형
SELECT e.employee_id, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;
3) FULL OUTER JOIN
- 조인조건을 만족하지 않는 양쪽 테이블의 행도 모두 함께 반환해 주는 조인 유형
- MySQL에서는 FULL OUTER JOIN을 지원하지 않으므로 LEFT OUTER JOIN 결과와 RIGHT OUTER JOIN결과를 UNION 하여 사용
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;
-- MySQL 에서는
SELECT e.employee_id, e.last_name, e.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.last_name, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;
- employees 테이블에서 department_id가 null인 레코드와, employees 테이블에서 사용하지 않은 department 테이블의 department_id 값을 가진 레코드가 모두 출력됨
3. 크로스조인
- 이너 조인과 아우터 조인은 두 테이블간의 특정 기준에 의해 데이터 결합의 결과를 보여주는 방식이었다면, 크로스 조인은 특정 기준 없이 두 테이블간 가능한 모든 경우의 수에 대한 결합을 결과로 보여주는 방식
- 쿼리를 작성할 때에도, 특정한 기준이 필요없으므로 on절이 없어지게 됨
- 출력 행 수는 (기준 테이블의 모든 행의 개수) * (조인 테이블의 모든 행의 개수) 이다.
SELECT l.country_id, c.country_id
FROM locations l CROSS JOIN countries c;
- 총 행 수 => 23(location 테이블 행 수) * 25(countries 테이블 행 수) = 575rows
4. 셀프 조인
- 하나의 테이블을 마치 다른 테이블인듯 테이블 alias를 달리 부여하여 자기 자신 테이블과 조인하는 유형
- 이너조인 및 아우터 조인, 크로스 조인과 동일한 방식으로 사용될 수 있지만 조인을 할 때에 참조하는 테이블이 자기 자신이어야 함
- 셀프 조인시에는 별칭을 필수로 입력
-- 직원의 사번, 직원의 이름, 직원의 급여, 매니저 이름을 출력하는 구문을 작성
-- MANAGER_ID 와 EMPLOYEE_ID 를 셀프 조인
SELECT e.employee_id, e.last_name, e.salary, e.manager_id, e2.last_name
FROM employees e JOIN employees e2
ON e.manager_id = e2.employee_id;
N 개의 테이블 조인
| 테이블 개수 | 조인 조건수 |
| 2 | 1 |
| 3 | 2 |
| N | N-1 |
- Employees, Departments, Locations 테이블을 조인해서 직원 정보, 부서 정보, 위치 정보를 함께 출력하려 함
- Employees 테이블과 Departments 테이블은 Department_id로 / Departments 테이블과 Locations 테이블은 location_id로 묶어줌
SELECT e.employee_id, e.last_name, e.salary, d.department_id, l.city
FROM employees e JOIN departments d
ON e.department_id = d.department_id
JOIN locations l
ON d.location_id = l.location_id;
Equi JOIN / Non-equi JOIN
- equi join : 조인 조건문에 동등연산자(=)를 사용한 조인 유형
- non-equi join : 조인 조건문에 동등연산자가 아닌 그 외 다른 비교연산자를 사용한 조인 유형
--
SELECT e.employee_id, 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;참조