[데이터 베이스] JOIN

SQL에서 JOIN은 두 개 이상의 테이블에서 관련된 데이터를 결합하여 새로운 결과 세트를 생성하는 데 사용된다. 

INNER JOIN

가장 일반적인 유형의 JOIN으로, 두 테이블 모두에서 일치하는 행만 반환한다.

 

SELECT orders.order_id, customers.name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;

 

SELF JOIN

테이블이 자기 자신과 조인하는 경우를 말하는 것으로 테이블 내의 행들 사이의 관계를 탐색할 수 있도록 하는 기법이다.

SELF JOIN을 사용하려면 같은 테이블을 구분하여 참조하기 위해 AS로 별칭을 사용하여 테이블 이름을 두 번 지정해야 한다.

실제 데이터가 아래와 같이 주어졌을 때 SELF JOIN을 실행하면 다음과 같다.

 

employee_id	name	manager_id
1	John Smith	NULL
2	Jane Doe	1
3	Max Pain	1
4	Lisa Ray	2

SELECT e1.name AS Employee, e2.name AS Manager
FROM employees AS e1
INNER JOIN employees AS e2 ON e1.manager_id = e2.employee_id;

 

이때 결과는 아래와 같이  나오게 된다.

 

Employee	Manager
Jane Doe	John Smith
Max Pain	John Smith
Lisa Ray	Jane Doe

LEFT JOIN (LEFT OUTER JOIN)

왼쪽 테이블의 모든 행과 오른쪽 테이블에서 일치하는 행을 반환한다.

이때 오른쪽 테이블에 일치하는 행이 없는 경우 해당 필드는 NULL로 채워진다.

 

Employees Table

employee_id	name	department_id
1	John Doe	1
2	Jane Doe	2
3	Max Pain	3

 

Departments Table

department_id	department_name
1	HR
2	IT
3	Marketing
4	Sales

 

실제로 위와 같이 데이터가 있다고 했을 때 department가 employees의 모든 department_id를 포함하고 있기 때문에 아래와 같이 코드를 실행하면 잘 나온다.

 

SELECT e.name, d.department_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.department_id;

 

name	department_name
John Doe	HR
Jane Doe	IT
Max Pain	Marketing

 

반면 아래의 경우 employees는 department_id = 4인 경우가 없으므로 해당하는 부분이 NULL로 채워서 출력이 된다.

 

SELECT d.department_name, e.name
FROM departments AS d
LEFT JOIN employees AS e ON d.department_id = e.department_id;

 

department_name	name
HR	John Doe
IT	Jane Doe
Marketing	Max Pain
Sales	NULL

RIGHT JOIN (RIGHT OUTER JOIN)

오른쪽 테이블의 모든 행과 왼쪽 테이블에서 일치하는 행을 반환한다.

이때 왼쪽 테이블에 일치하는 행이 없는 경우 해당 필드는 NULL로 채워진다.

 

SELECT orders.order_id, customers.name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.customer_id;

FULL JOIN (FULL OUTER JOIN)

두 테이블의 모든 행을 반환한다.

이때 한쪽 테이블에만 존재하는 행도 포함되며 만약 일치하는 행이 없는 경우 해당 필드는 NULL로 채워진다.

Colors Table

color_id	color_name
1	Red
2	Blue
3	Green

 

Shapes Table

shape_id	shape_name
1	Circle
2	Square
4	Triangle

SELECT color_name, shape_name
FROM Colors
FULL JOIN Shapes ON Colors.color_id = Shapes.shape_id;

 

위의 데이터로 FULL JOIN을 실행하면 아래와 같은 결과가 나온다.

 

color_name	shape_name
Red	Circle
Blue	Square
Green	NULL
NULL	Triangle

CROSS JOIN

두 테이블의 모든 가능한 조합을 반환한다.

특정 조건에 대한 일치 여부와 관계없이 테이블의 각 행은 다른 테이블의 모든 행과 결합된다.

 

Colors Table

color_id	color_name
1	Red
2	Blue

 

Shapes Table

shape_id	shape_name
1	Circle
2	Square

SELECT color_name, shape_name
FROM Colors
CROSS JOIN Shapes;

 

위의 데이터로 CROSS JOIN을 실행하면 아래와 같은 결과가 나온다.

 

color_name	shape_name
Red	Circle
Red	Square
Blue	Circle
Blue	Square

USING

USING은 JOIN 연산을 수행할 때 두 테이블 간에 같은 이름을 가진 열에 대해 간소화 하기 위해서 사용된다.

위에있는 INNER JOIN의 코드 예시처럼 customer_id가 중복되서 확인되는 경우 아래 코드처럼 USING으로 표현할 수 있다.

 

SELECT orders.order_id, customers.name
FROM orders
INNER JOIN customers USING(customer_id)

 

다만 USING은 조건이 동일한 열 이름을 기반으로 할 때만 사용할 수 있어 열 이름이 다르거나 더 복잡한 조건이 필요한 경우 ON을 사용해야 한다.