[DB] 집합 연산자(Set operator) / 조인(JOIN)

1. 쿼리의 결과 간의 연산: 집합 연산자(Set Operator)

 집합 연산자(Set Operator)는 SELECT 쿼리의 결과를 대상으로 연산을 수행하는 연산자이다. 집합 연산자에는 UNION, UNION ALL, INTERSECT, MINUS(EXCEPT)가 있다. 주요 집합 연산자들의 특징에 대해 알아보자.

 

주요 집합 연산자

UNION

• 두 개의 SELECT 쿼리의 결과를 중복을 제거하고, 결합하여 반환 

SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;

<UNION 활용 예시>

 

**문자셋을 utf-9이나 euckr로 지정했음에도 한글 표기가 되지 않는 이유는 콘솔로 접속했기 때문이다. 원격 터미널(putty)로 접속하면 정상적으로 출력된다. 

 

UNION ALL 

• 두 개의 SELECT 쿼리의 결과를 중복을 제거하지 않고, 결합하여 반환

SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2;

 

INTERSECT

• 두 개의 SELECT 쿼리 결과에서 공통으로 존재하는 컬럼만 반환

SELECT column_name FROM table1
INTERSECT
SELECT column_name FROM table2;

 

MINUS

• 첫 번째 SELECT 쿼리 결과에서 두 번째 쿼리 결과에 포함되지 않은 컬럼을 반환

SELECT column_name FROM table1
MINUS
SELECT column_name FROM table2;

*Oracle에서는 MINUS를 사용하고, 일부 다른 DB에서는 EXCEPT를 사용하는 경우도 있음

 

 집합 연산자를 사용할 때, 중요한 점은 병합하고자 하는 A쿼리문의 컬럼 수와 자료형이 동일해야 한다는 것이다. MySQL(mariaDB)의 경우, 상관없이 결과가 병합되지만, Oracle을 비롯한 다른 DB의 경우 엄격하게 처리한다. 

 

 

1. 관계를 기반으로 테이블을 결합하자: JOIN

 조인(JOIN)은 관계형 데이터베이스에서 여러 테이블을 연결하여 데이터를 조회할 때 사용된다. 관계형 데이터베이스로 두 테이블이 연결되면 최소 한 개의 컬럼이 공유되어야 하고, 주로 기본 키(primary key)와 외래 키(foreign key)를 이용하여 연결한다. 이러한 상황에서 내가 찾고자 하는 컬럼이 다른 테이블에 있고, 두 테이블을 마치 하나의 테이블처럼 만들어서 검색하고자 할 때, 조인이 사용된다. 

 

다음의 테이블들을 이용해서 조인에 대해 알아보자.

 

[Department table]

id	department
1	HR
2	Engineering
3	Marketing
4	Sales

 

[Employee Table]

name	department_id	salary
Alice	1	5000
Bob	2	6000
Charlie	3	4500
David	2	7000
Eve	4	5500

 

 

1) INNER JOIN

 공통된 값이 있는 행(row)을 반환한다. INNER JOIN은 가장 일반적인 조인으로 JOIN만 입력해도 된다. 위의 두 테이블을 INNER JOIN하는 방법과 INNER JOIN을 수행했을 때, 나오는 결과값은 다음과 같다. 

SELECT column, column
FROM table1 
INNER JOIN table2 ON table1.column_name = table2.column_name; 

--ex--
SELECT employees.name, employees.salary, departments.department_name
FROM employees 
JOIN departments ON employees.department_id = departments.id;

name	salary	department_name
Alice	5000	HR
Bob	6000	Engineering
Charlie	4500	Marketing
David	7000	Enginerring
Eve	5500	Sales

 

위의 결과값을 봤을 때, INNER JOIN은 ON의 조건에 맞는 행들만 출력하는 것을 볼 수 있다. 만약 공통된 값이 없거나 NULL일 경우, 해당 행은 결과값으로 출력되지 않는다. 

 

2) RIGHT/LEFT OUTER JOIN

오른쪽/왼쪽 테이블에서 모든 행을 반환하고, 왼쪽/오른쪽 테이블에서 일치하는 행이 있으면 결합하며, 없으면 NULL을 반환한다. 

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

--ex--
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

--ex--
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;

name	department_name
Alice	HR
Bob	Engineering
Charlie	Marketing
David	Enginerring
Eve	Sales

 

결과값을 보면, RIGHT/LEFT OUTER JOIN은 공통된 값과 오른쪽 /왼쪽 테이블의 모든 값을 함께 보고 싶을 때 사용하는 것을 알 수 있다.

 

3) FULL OUTER JOIN

두 테이블에서 모든 행을 반환하며, 일치하는 데이터는 결합하고, 일치하지 않는 경우에는 NULL을 반환한다. MySQL에서는 FULL OUTER JOIN을 지원하지 않지만, UNION을 이용해 구현할 수 있다.

--ex--
SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id
UNION
SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;

 

4) CROSS JOIN

두 테이블에서 모든 행의 조합을 반환한다. 즉, A테이블의 카디널리티(행)가 N개, B테이블의 카디널리티가 M개일 경우, 두 테이블을 CROSS JOIN한 결과의 카디널리티는 N*M이다. 앞선 테이블들로 CROSS JOIN을 했을 경우, 나오는 테이블의 카디널리티는 20개이다.

SELECT columns
FROM table1
CROSS JOIN table2;

--ex--
SELECT employees.name, departments.department_name
FROM employees
CROSS JOIN departments;

name	department_name
Bob	HR
Bob	Engineering
...
Eve	Markting
Eve	Sales

 

5) SELF JOIN

하나의 테이블을 자기 자신과 조인하는 방식이다. SELF JOIN은 주로 같은 테이블 내의 계층적인 정보를 조회할 때 사용한다. 쉽게 생각하면, 같은 두 개의 테이블을 조인한다고 생각하면 된다. 

SELECT a.column, b.column
FROM table_name a, table_name b
WHERE a.common_column = b.common_column;

--ex--
SELECT e1.name AS Employee, e2.name AS Manager
FROM employees e1
LEFT JOIN employees e2
ON e1.manager_id = e2.id;

 

 

 

 힘들지만 오늘도 해낸 나를 위한 한 마디,

"당신이 원하는 위치나 모습에 대한 비전은 당신이 가진 큰 자산이다. 목표가 없다면 성공을 거두기 어렵다", 폴 아든

 

"이루고자 하는 목표와 비전, 그리고 그것들에 대한 확신을 가지고 끊임없이 나아가자"