[Mysql] 조인 (JOIN) 이란?

1. JOIN 이란?

한개 또는 여러개의 테이블을 결합하는 연산을 의미합니다. JOIN의 종류는 다음과 같습니다.

cross join
inner join
left outer join
right outer join
full outer join
self join

 

join을 설명하기 이전에 테스트로 사용될 테이블 부터 생성해 보겠습니다.

CREATE TABLE `department` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(50) NULL DEFAULT NULL,
	PRIMARY KEY (`id`)
)

CREATE TABLE `employee` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(100) NULL DEFAULT NULL,
	`salary` INT(11) NULL DEFAULT NULL,
	`dept_id` INT(11) NULL DEFAULT NULL,
	`manager_id` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	CONSTRAINT `fk_department_id` FOREIGN KEY (`dept_id`) REFERENCES `department` (`id`),
	CONSTRAINT `fk_manager_id` FOREIGN KEY (`manager_id`) REFERENCES `employee` (`id`)
)

INSERT INTO `department` (`id`, `name`) VALUES (1, '사업부');
INSERT INTO `department` (`id`, `name`) VALUES (2, '개발부');
INSERT INTO `department` (`id`, `name`) VALUES (3, '영업부');

INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`, `manager_id`) VALUES (1, 'kim', 3000, 2, NULL);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`, `manager_id`) VALUES (2, 'park', 2600, 2, NULL);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`, `manager_id`) VALUES (3, 'lee', 7000, 1, NULL);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`, `manager_id`) VALUES (4, 'go', 3400, 2, NULL);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`, `manager_id`) VALUES (5, 'jang', 4000, 1, NULL);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`, `manager_id`) VALUES (6, 'hong', 5500, 1, NULL);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`, `manager_id`) VALUES (7, 'sin', 1800, 2, 2);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`, `manager_id`) VALUES (8, 'lim', 3400, 2, NULL);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`, `manager_id`) VALUES (9, 'chang', 4400, 3, NULL);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`, `manager_id`) VALUES (10, 'ho', 1800, NULL, 1);
INSERT INTO `employee` (`id`, `name`, `salary`, `dept_id`, `manager_id`) VALUES (11, 'coco', 8900, NULL, NULL);

employee 테이블

department 테이블

 

1) cross join 

조인한 테이블을 교차 결합할 때 사용합니다. 결과 수는 조인한 테이블의 행을 곱한 수입니다.

select * from employee cross join department;
# 33개가 조회된다.

 

2) inner join

inner join은 특정 조건에 맞는 행만 결합할 때 사용합니다.

select a.*, b.name
from employee a
inner join department b on (b.id = a.dept_id)

on 절 다음에 조인 조건을 명시하며 조건에 맞는 행을 결합하여 출력합니다.
위 조건은 employee 테이블의 dept_id와 department 테이블의 id가 같은 경우 결합하도록 명시했습니다.

여기서 employee 테이블의 ho와 coco는 dept_id가 null이기 때문에 조인되지 않습니다.

 

3) left outer join

left outer join은 왼쪽 테이블을 대상으로 오른쪽 테이블과 결합하는 연산이며, 여기서 포인트는 조인 조건이 맞지 않더라도 왼쪽에 있는 테이블의 모든 행이 출력된다는 점입니다. 조건이 맞을 경우 그 만큼 행이 추가되서 조회됩니다.

select a.*, b.name
from employee a
left outer join department b on (b.id = a.dept_id)

employee 테이블을 대상으로 left outer join을 했기 때문에, employee 테이블의 모든 행이 출력되었고, 그 중 조건이 맞는 경우 department의 name이 출력되게 했습니다. 조건이 맞지 않을 경우 null이 출력됩니다.

 

좀 더 다양한 테스트를 하기 위해 access_log 테이블을 추가해 보겠습니다.

# employee의 접근 기록을 저장하는 테이블
CREATE TABLE `access_log` (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`access_dt` DATETIME NULL DEFAULT '',
	`emp_id` INT(11) NULL DEFAULT NULL,
	PRIMARY KEY (`id`),
	CONSTRAINT `fk_access_log_emp_id` FOREIGN KEY (`emp_id`) REFERENCES `employee` (`id`)
)

INSERT INTO `access_log` (`id`, `access_dt`, `emp_id`) VALUES (1, '2021-03-28 16:30:55', 1);
INSERT INTO `access_log` (`id`, `access_dt`, `emp_id`) VALUES (2, '2021-03-28 16:30:58', 1);
INSERT INTO `access_log` (`id`, `access_dt`, `emp_id`) VALUES (3, '2021-03-28 16:31:01', 1);
INSERT INTO `access_log` (`id`, `access_dt`, `emp_id`) VALUES (4, '2021-03-28 16:31:04', 2);
INSERT INTO `access_log` (`id`, `access_dt`, `emp_id`) VALUES (5, '2021-03-28 16:31:05', 3);
INSERT INTO `access_log` (`id`, `access_dt`, `emp_id`) VALUES (6, '2021-03-28 16:31:06', 3);
INSERT INTO `access_log` (`id`, `access_dt`, `emp_id`) VALUES (7, '2021-03-28 16:31:08', 3);
INSERT INTO `access_log` (`id`, `access_dt`, `emp_id`) VALUES (8, '2021-03-28 17:31:11', 4);
INSERT INTO `access_log` (`id`, `access_dt`, `emp_id`) VALUES (9, '2021-03-28 18:31:13', 1);

access_log 테이블

 

access_log 테이블은 employee의 접근 기록을 저장하는 테이블입니다. 위 정보를 봐서 유추해볼 수 있는 점은 emp_id가 1인 사원은 4번의 출입 기록이 있고, 2는 1번, 3은 3번, 4는 1번의 출입 기록이 있는 것을 알 수 있습니다.
더 정확한 정보를 알기 위해서 employee 테이블과 access_log를 left outer join 해보겠습니다.

select a.*, b.access_dt
from employee a
left outer join access_log b on (b.emp_id = a.id)

employee 테이블을 대상으로 access_log 테이블과 left outer join을 했기 때문에 employee 테이블의 모든 행이 출력되었고, 접근 기록이 있는 만큼 추가로 조회됩니다.

right outer join은 left outer join 시 테이블의 위치만 바뀐 것이며, full outer join 설명은 생략하겠습니다.

 

4) self join

self join은 계층 구조를 표현 할 때 사용하며 한 개의 테이블로 표현이 가능합니다.  

select emp.*, manager.name AS 'manager_name'
from employee emp
join employee manager on (manager.id = emp.manager_id)

employee 테이블을 조인 하였으며, employee의 id와 manager_id 가 같은 경우 조회됩니다.