5. DB/4-1. MySQL DBMS

MySQL - [JOIN (Inner Join / Outer Join / Cross Join / Self Join) ]

yunyj99 2022. 4. 8. 00:10

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;

 

두 테이블에 같은 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;

department_id가 null이더라도 employees 테이블의 모든 행 출력

 

 

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;

employees 테이블에 값이 없더라도 departments 테이블의 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 값을 가진 레코드가 모두 출력됨

left outer와 right outer join의 합집합과 같음

 

 

 


3. 크로스조인

- 이너 조인과 아우터 조인은 두 테이블간의 특정 기준에 의해 데이터 결합의 결과를 보여주는 방식이었다면, 크로스 조인은 특정 기준 없이 두 테이블간 가능한 모든 경우의 수에 대한 결합을 결과로 보여주는 방식

- 쿼리를 작성할 때에도, 특정한 기준이 필요없으므로 on절이 없어지게 됨

- 출력 행 수는 (기준 테이블의 모든 행의 개수) * (조인 테이블의 모든 행의 개수) 이다.

SELECT l.country_id, c.country_id
FROM locations l CROSS JOIN countries c;

 

- 총 행 수 => 23(location 테이블 행 수) * 25(countries 테이블 행 수) = 575rows

 

 

 


4. 셀프 조인

- 하나의 테이블을 마치 다른 테이블인듯 테이블 alias를 달리 부여하여 자기 자신 테이블과 조인하는 유형

- 이너조인 및 아우터 조인, 크로스 조인과 동일한 방식으로 사용될 수 있지만 조인을 할 때에 참조하는 테이블이 자기 자신이어야 함
- 셀프 조인시에는 별칭을 필수로 입력

 

employees 테이블 데이터

-- 직원의 사번, 직원의 이름, 직원의 급여, 매니저 이름을 출력하는 구문을 작성
-- 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;

사원 id, 이름, 연봉, 사원을 담당하는 매니저 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 : 조인 조건문에 동등연산자가 아닌 그 외 다른 비교연산자를 사용한 조인 유형

 

 

Job_grades 테이블

employees 테이블

 

-- 
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;

참조