Join은 대체 왜 사용할까?

Mysql과 같은 RDBMS에서는 단순히 Select를 써서 데이터만 가져오면 될거 같은데 왜 머리아프게 JOIN까지 써야할까요?

RDBMS에서는 데이터의 중복을 피하기위해, 테이블을 나누어 저장합니다. 하지만, 이렇게 쪼개진 데이터들을 하나로 가져오기 위해서 필요한 데이터가 들어있는 테이블을 조합하여 원하는 데이터를 불러 올 수 있습니다.

하지만 종류가 너무 많습니다. 그래서 한번에 정리 해볼려고합니다.

실습 테이블

우선 Join에 대한 설명을 이어나가기 전에 쉽게 예제를 하나 준비해보겠습니다.

CREATE DATABASE Company DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

/**
Docker같은 가상화로 Mysql을 사용할 경우 DB생성시 다음과 같은 속성을 추가해줘야 한글이 안깨집니다.

DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci
**/

USE Company;

CREATE TABLE IF NOT EXISTS Employee  (
 employee_id INT PRIMARY KEY,
 full_name VARCHAR(20) NOT NULL,
 department VARCHAR(45) NOT NULL,
 salary BIGINT NOT NULL,
 gender VARCHAR(45) NOT NULL,
 age INT NOT NULL
);

CREATE TABLE IF NOT EXISTS Project  (
 project_id INT PRIMARY KEY AUTO_INCREMENT, 
 project_name VARCHAR(20) NOT NULL,
 employee_id INT,
 start_date DATETIME,
 end_date DATETIME,
 FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
);

CREATE TABLE IF NOT EXISTS Address  (
 address_id INT PRIMARY KEY AUTO_INCREMENT,
 employee_id INT,
 country VARCHAR(50),
 state VARCHAR(50),
 city VARCHAR(50),
 FOREIGN KEY (employee_id) REFERENCES Employee(employee_id)
);

• Employee

INSERT INTO Company.Employee (employee_id,full_name,department,salary,gender,age) VALUES
	 (1001,'김김김','Front-End',2200000,'Male',27),
	 (1002,'박박박','HR',2200000,'Male',27),
	 (1003,'최최최','Back-End',2600000,'Male',27),
	 (1004,'이이이','Back-End',2900000,'Female',27),
	 (1005,'손손손','Back-End',2700000,'Female',27),
	 (1006,'오오오','Front-End',2800000,'Female',27),
	 (1007,'신신신','HR',2800000,'Male',27);

• Porject

INSERT INTO Company.Project (project_name,employee_id,start_date,end_date) VALUES
	 ('to do list 레이아웃 개발',1001,NOW(), DATE_ADD(NOW(), INTERVAL 45 DAY)),
	 ('팀원들 채찍질',1002,NOW(), DATE_ADD(NOW(), INTERVAL 36 DAY)),
	 ('스키마 작성',1003,NOW(),DATE_ADD(NOW(), INTERVAL 5 DAY)),
	 ('API 설계',1004,NOW(),DATE_ADD(NOW(), INTERVAL 72 DAY)),
	 ('직원들 채찍질',1007, NOW(),DATE_ADD(NOW(), INTERVAL 36 DAY)),
	 ('Drag and Drop 기능 개발',1006, NOW(),DATE_ADD(NOW(), INTERVAL 42 DAY)),
   ('NodeJS 백엔드 서버 개발',1005,NOW() ,DATE_ADD(NOW(), INTERVAL 36 DAY));

• Address

INSERT INTO Company.Address(employee_id, country, state, city) 
VALUES (1001,'대한민국', '서울', '관악구'),
(1002,'대한민국', '서울', '동작구'),
(1003,'대한민국', '서울', '영등포구'),
(1004,'대한민국', '서울', '강남구'),
(1005,'대한민국', '서울', '강동구'),
(1006,'대한민국', '서울', '강북구'),
(1007,'대한민국', '서울', '송파구')

Join의 종류

해당 Join은 MySQL에서 지원하는 조인 연산입니다.

Inner Join, 내부조인

가장 기본적인 형태의 Inner Join 입니다. 관계된 두 테이블은 한 테이블은 직원 번호를 주키로 한 테이블은 참조키로 저장되어있습니다.

SELECT *
FROM Employee 
INNER JOIN Project 
ON Employee.employee_id  = Project.employee_id 

Inner Jion은 말 그대로 교집합입니다. Project 테이블은 Employee 테이블의 주키를 참조키로 가지고있기 때문에 교집합이 성립되어 두 테이블에 있는 데이터를 모두 가져올 수 있습니다.

Equi Join, 동등, 동일, 등가 조인

• 두 개의 테이블 간 컬럼 값들이 서로 일치하는 경우 사용되는 방법입니다.
• 대부분 PK, FK관계를 기반으로 만들어집니다.
• 지정한 컬럼에 대해 동일한 값이 있는 행만 반환합니다.

시나리오는 다음과 같습니다.

진행하고있는 프로젝트와 프로젝트를 담당하고 있는 사원이름, 부서를 알고싶습니다.

SELECT Project.project_name, Employee.full_name, Employee.department 
FROM Project, Employee WHERE Project.employee_id = Employee.employee_id;

Inner Join과 Equi Join 차이점
동등 조건은 Where 절과 =연산자를 첨가하여 컬럼의 값이 동일할 경우 조인 합니다

NON-EQUI Join, 비등가 조인

• 동일 컬럼 없이 다른 조건으로 조인할 때 사용합니다.

Natural Join, 자연조인

동등 조인의 일종으로, 관계된 두 테이블중 JOIN할 테이븡릐 컬럼을 모두 검사하여 같은 컬럼을 기준으로 JOIN을 수행합니다. 여기서 중복된 컬럼은 하나로만 나타냅니다.

SELECT *
FROM Address NATURAL JOIN Employee;

Outer Join, 외부조인

• 외부 조인은 3가지 있습니다.
• LEFT, RIGHT, FULL OUTER 입니다.
• MySQL은 Full JOIN 구문이 없기때문에 UNION을 사용하여 결과를 집합으로 표시합니다.

UNION

• UNION은 DISTINCT를 자동으로 포함하고 있기떄문에 중복된 레코드를 제거합니다.

SELECT 필드명 FROM 테이블명
UNION 
SELECT 필드명 FROM 테이블명;

UNION ALL

• UNION ALL은 중복된 레코드까지 모두 출력할때 쓰입니다.

SELECT 필드명 FROM 테이블명
UNION ALL
SELECT 필드명 FROM 테이블명;

FULL OUTER JOIN, 완전 외부 조인

mysql 에는 full outer join 구문이 없습니다. 그래서 Left, Right JOIN을 활용해서 UNION으로 합쳐버리는 방법이 있습니다.

SELECT * FROM Employee E LEFT JOIN Address A ON E.employee_id = A.employee_id
UNION
SELECT * FROM Address A RIGHT JOIN Employee E ON A.employee_id=E.employee_id;

LEFT OUTER JOIN, 왼쪽 외부 조인

• 조인문 왼쪽에 있는 테이블의 모든 결과를 가져옵니다.
• 조인문 오른쪽 테이블의 데이터를 매칭하여, 매칭되는 데이터가 없는 경우 Null을 표시합니다.
• LEFT JOIN이랑 같습니다.

SELECT *
FROM Employee E LEFT OUTER JOIN Address A
ON E.employee_id = A.employee_id;

RIGHT OUTER JOIN, 오른쪽 외부 조인

• 조인문 오른쪽에 있는 테이블의 모든 결과를 가져옵니다.
• 조인문 왼쪽 테이블의 데이터를 매칭하여, 매칭되는 데이터가 없는 경우 Null을 표시합니다.
• RIGHT JOIN이랑 같습니다.

SELECT *
FROM Address A RIGHT OUTER JOIN Employee E
ON A.employee_id = E.employee_id;

3. Self Join, 셀프조인

• 관계된 테이블이아닌 하나의 자체 테이블에서 참조하는 조인 형태입니다.
• 한 테이블에 별칭을 두개를 사용하여 조인을 수행합니다.
• Outer Join을 이용하는게 일반적입니다.

우선 직원과 직원 매니저를 조회해야한다고 가정했을때 테이블좀 수정해야겠습니다.

• Employee 테이블 수정

ALTER TABLE Employee ADD Column manager INT Null;

• manager에 ROW 갯수만큼 채우기

UPDATE Employee E JOIN (
SELECT 1001 as employee_id, 1002 as new_manager
UNION ALL
SELECT 1002, 1006
UNION ALL
SELECT 1003, 1002
UNION ALL
SELECT 1004, 1002
UNION ALL
SELECT 1005, 1006
UNION ALL
SELECT 1006, 1006
UNION ALL
SELECT 1007, 1006
UNION ALL
SELECT 1008, 1006
) vals ON E.employee_id = vals.employee_id
SET manager = new_manager

이후 질의문을 봅시다.

사원 번호와 이름, 관리하는 매니저의 이름을 조회한다고 가정해봅시다.

SELECT E.employee_id, E.full_name, M.full_name 
FROM Employee E LEFT JOIN Employee M
ON E.manager = M.employee_id;  

Anti Join, 안티조인

• 차집합이라고도 불리는 조인형태는, 한 테이블에서 다른 테이블에 일치하는 모든 행을 조회합니다.
• 관계된 두 테이블에 다른 한쪽에 존재하지 않는 컬럼 값을 가져올때 씁니다.
• 구현방법은 다양합니다. 서브쿼리 혹은 NOT EXISTS,NOT IN,IS NULL,LEFT JOIN/IN NULL 등이 쓰입니다.

NOT IN

SELECT
FROM 
WHERE NOT IN(
  SELECT 
  FROM
); 

LEFT JOIN/IS NULL

SELECT 
FROM
LEFT JOIN ON
WHERE IS NULL

NOT EXSISTS

SELECT 
FROM
WHERE NOT EXISTS (
	SELECT
    FROM
    WHERE
)

- LEFT ANTI JOIN, IS NULL

• 관계된 두 테이블에서 왼쪽 테이블ㅇ

- RIGHT ANTI JOIN, IS NULL

Semi Join, 세미조인

ref: multiple Update, Union 사용법,안티조인에 대해서,안티조인 에 대해서