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)
);
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);
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));
INSERT INTO Company.Address(employee_id, country, state, city)
VALUES (1001,'대한민국', '서울', '관악구'),
(1002,'대한민국', '서울', '동작구'),
(1003,'대한민국', '서울', '영등포구'),
(1004,'대한민국', '서울', '강남구'),
(1005,'대한민국', '서울', '강동구'),
(1006,'대한민국', '서울', '강북구'),
(1007,'대한민국', '서울', '송파구')
해당 Join은 MySQL에서 지원하는 조인 연산입니다.
가장 기본적인 형태의 Inner Join 입니다. 관계된 두 테이블은 한 테이블은 직원 번호를 주키로 한 테이블은 참조키로 저장되어있습니다.
SELECT *
FROM Employee
INNER JOIN Project
ON Employee.employee_id = Project.employee_id
Inner Jion은 말 그대로 교집합입니다. Project
테이블은 Employee
테이블의 주키를 참조키로 가지고있기 때문에 교집합이 성립되어 두 테이블에 있는 데이터를 모두 가져올 수 있습니다.
시나리오는 다음과 같습니다.
진행하고있는 프로젝트와 프로젝트를 담당하고 있는 사원이름, 부서를 알고싶습니다.
SELECT Project.project_name, Employee.full_name, Employee.department
FROM Project, Employee WHERE Project.employee_id = Employee.employee_id;
Inner Join과 Equi Join 차이점
동등 조건은Where
절과=
연산자를 첨가하여 컬럼의 값이 동일할 경우 조인 합니다
동등 조인의 일종으로, 관계된 두 테이블중 JOIN할 테이븡릐 컬럼을 모두 검사하여 같은 컬럼을 기준으로 JOIN을 수행합니다. 여기서 중복된 컬럼은 하나로만 나타냅니다.
SELECT *
FROM Address NATURAL JOIN Employee;
UNION
을 사용하여 결과를 집합으로 표시합니다.DISTINCT
를 자동으로 포함하고 있기떄문에 중복된 레코드를 제거합니다.SELECT 필드명 FROM 테이블명
UNION
SELECT 필드명 FROM 테이블명;
SELECT 필드명 FROM 테이블명
UNION ALL
SELECT 필드명 FROM 테이블명;
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;
Null
을 표시합니다.SELECT *
FROM Employee E LEFT OUTER JOIN Address A
ON E.employee_id = A.employee_id;
Null
을 표시합니다.SELECT *
FROM Address A RIGHT OUTER JOIN Employee E
ON A.employee_id = E.employee_id;
우선 직원과 직원 매니저를 조회해야한다고 가정했을때 테이블좀 수정해야겠습니다.
ALTER TABLE Employee ADD Column manager INT Null;
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;
NOT EXISTS
,NOT IN
,IS NULL
,LEFT JOIN/IN NULL
등이 쓰입니다.SELECT
FROM
WHERE NOT IN(
SELECT
FROM
);
SELECT
FROM
LEFT JOIN ON
WHERE IS NULL
SELECT
FROM
WHERE NOT EXISTS (
SELECT
FROM
WHERE
)