[CS Study] Database - JOIN

Frye 'de Bacon·2023년 12월 19일

Computer Science(CS)

목록 보기

33/40

조인(JOIN)

조인(JOIN)이란 RDBMS에서 하나의 데이터베이스 내 '여러 테이블의 레코드를 조합하여 하나의 열로 표현하는 기법'이다. 즉, 각기 다른 테이블을 한 번에 보여주고자 할 때 쓰는 쿼리라고 할 수 있다.
RDBMS의 가장 큰 장점이자 대표적인 핵심 기능으로서, 실무에서도 매우 많이 사용되는 쿼리이므로 확실하게 이해하고 넘어갈 필요가 있다.

JOIN을 한눈에 이해하기 위한 다이어그램

JOIN을 일단 가장 기본적인 부분으로 구분하면 두 가지로 구분할 수 있다.

등가 조인(EQUI JOIN)

등가 조인(EQUI JOIN)은 Equal(=) 조건을 이용해 JOIN을 하는것을 말한다. 가장 흔히 볼 수 있는 JOIN 방식이기도 하다.

다음 테이블을 통해 예를 들어 보자. 쇼핑몰에서 스피커와 리뷰 데이터가 있다고 하자. 스피커는 '상품' 테이블의 데이터이고, 리뷰는 '상품 리뷰' 테이블에 저장될 것이다.

SELECT * FROM PRODUCT;

PRODUCT_ID	PRODUCT_NAME	PRICE
1001	CreXXXXe 2ch 블루투스 스피커	42,500
1002	YAXXHA 2.1ch 모니터링 스피커(50W)	183,000
1003	PeXXXle 2ch 블루투스 스피커	21,500

SELECT * FROM PRODUCT_REVIEW;

REVIEW_ID	PRODUCT_ID	USER_NAME	CONTENT	DATE
01	1001	mino	크기는 작은데 소리가 진짜 좋네요.	20230419
02	1001	yakasaha	생각보다 가성비 좋은 듯. 잡음도 없어요.	20230912
03	1002	kisoek	확실히 Y브랜드 스피커는 최고는 아니어도 표준은 함	20231120

SELECT A.PRODUCT_ID,
	   A.PRODUCT_NAME,
       B.USER_NAME,
       B.CONTENT,
       B.DATE
FROM PRODUCT A,
     PRODUCT_REVIEW B
WHERE A.PRODUCT_ID = B.PRODUCT_ID;

PRODUCT_ID	PRODUCT_NAME	USER_NAME	CONTENT	DATE
1001	CreXXXXe 2ch 블루투스 스피커	mino	크기는 작은데 소리가 진짜 좋네요.	20230419
1001	CreXXXXe 2ch 블루투스 스피커	yakasaha	생각보다 가성비 좋은 듯. 잡음도 없어요.	20230912
1002	YAXXHA 2.1ch 모니터링 스피커(50W)	kisoek	확실히 Y브랜드 스피커는 최고는 아니어도 표준은 함	20231120

PRODUCT 테이블과 PRODUCT_REVIEW 테이블의 각 컬럼이 하나의 테이블로 반환된다. 이때 두 테이블에서 공통되는 컬럼인 PRODUCT_ID를 비교하여 동일한 데이터만 반환하였으며, 따라서 리뷰 데이터가 존재하지 않는 1003번 상품은 반환되지 않았음을 확인할 수 있다.

비등가 조인(Non EQUI JOIN)

비등가 조인(Non EQUI JOIN)은 Equal(=) 조건이 아닌 다른 조건을 사용하지 않는 조인으로, BETWEEN, >, >=, <, <= 등의 조건이 이에 해당한다. 앞서 등가 조인의 상품 리뷰들 중 일정 기간 내에 작성된 리뷰 작성자에게 상품을 주는 이벤트를 진행한다고 하면, 상품 리뷰 테이블과 이벤트 테이블이 JOIN되어야 할 것이다.

SELECT * FROM PRODUCT_REVIEW;

REVIEW_ID	PRODUCT_ID	USER_NAME	CONTENT	DATE
01	1001	mino	크기는 작은데 소리가 진짜 좋네요.	20230419
02	1001	yakasaha	생각보다 가성비 좋은 듯. 잡음도 없어요.	20230912
03	1002	kisoek	확실히 Y브랜드 스피커는 최고는 아니어도 표준은 함	20231120

SELECT * FROM EVENT;

EVENT_ID	EVENT_NAME	START_DATE	END_DATE
101	마일리지 500원 증정	20230301	20230331
102	배송비 무료 쿠폰 증정	20230901	20231130
103	크리스마스 특별 이벤트	20231201	20231231

SELECT A.EVENT_NAME,
	   B.USER_NAME,
       B.CONTENT,
       B.DATE
FROM EVENT A,
     PRODUCT_REVIEW B
WHERE B.DATE BETWEEN A.START_DATE AND A.END_DATE;

EVENT_NAME	USER_NAME	CONTENT	DATE
마일리지 500원 증정	mino	크기는 작은데 소리가 진짜 좋네요.	20230419
배송비 무료 쿠폰 증정	yakasaha	생각보다 가성비 좋은 듯. 잡음도 없어요.	20230912
배송비 무료 쿠폰 증정	kisoek	확실히 Y브랜드 스피커는 최고는 아니어도 표준은 함	20231120

DBMS에는 Oracle, MSSQL, MySQL, MaiaDB, PostgreSQL 등 다양한 벤더가 존재한다. 그런데 벤더마다 SQL 문법에 차이가 너무 클 경우 작업자 입장에서 효율성에 문제가 있어 표준이 되는 ANSI SQL을 지정하게 되었다. JOIN 역시 ANSI SQL에 따르는 문법이 있으며, 이를 ANSI JOIN 혹은 표준 조인, Standard JOIN 등으로 부른다.
다음에서 설명할 내부 조인, 외부 조인, 자연 조인, 교차 조인 등은 모두 ANSI JOIN 문법에 해당하는 내용이다.

내부 조인(INNER JOIN)

내부 조인(INNER JOIN)은 가장 많이 사용되는 조인 쿼리로 JOIN 조건을 충족하는 데이터만을 출력하는 방식이다.

앞서 살펴본 등가 조인의 문법과 다른 점은 ON 절을 사용한다는 것이다.

SELECT A.PRODUCT_ID,
	   A.PRODUCT_NAME,
       B.USER_NAME,
       B.CONTENT,
       B.DATE
FROM PRODUCT A INNER JOIN PRODUCT_REVIEW B
ON A.PRODUCT_ID = B.PRODUCT_ID;

PRODUCT_ID	PRODUCT_NAME	USER_NAME	CONTENT	DATE
1001	CreXXXXe 2ch 블루투스 스피커	mino	크기는 작은데 소리가 진짜 좋네요.	20230419
1001	CreXXXXe 2ch 블루투스 스피커	yakasaha	생각보다 가성비 좋은 듯. 잡음도 없어요.	20230912
1002	YAXXHA 2.1ch 모니터링 스피커(50W)	kisoek	확실히 Y브랜드 스피커는 최고는 아니어도 표준은 함	20231120

참고로 위의 등가 조인과 같이 'INNER JOIN ... ON ...' 구문을 사용하지 않는 경우를 '암묵적 표현법(implicit notation)'이라고 하고, 'INNER JOIN ... ON ...' 구문을 사용하는 경우를 '명시적 표현법(explicit notation)'이라고 하기도 한다.

외부 조인(OUTER JOIN)

외부 조인(OUTER JOIN)은 JOIN 조건에 충족하는 데이터가 아니어도 출력하는 방식이다. 크게 세 가지로 구분 가능하며, 각각 'JOIN 조건을 충족하지 않지만 출력되는 데이터'를 판별하는 기준점이 된다.

LEFT OUTER JOIN

SQL에서 왼쪽에 표기된 테이블(A)의 모든 데이터와 JOIN 조건을 충족하는 오른쪽 테이블(B)의 데이터가 출력되는 JOIN이다. 이때 오른쪽 테이블에서 JOIN되는 데이터가 없는 행의 경우 값이 NULL로 출력된다.

SELECT * FROM PRODUCT;

PRODUCT_ID	PRODUCT_NAME	PRICE
1001	CreXXXXe 2ch 블루투스 스피커	42,500
1002	YAXXHA 2.1ch 모니터링 스피커(50W)	183,000
1003	PeXXXle 2ch 블루투스 스피커	21,500

SELECT * FROM PRODUCT_REVIEW;

REVIEW_ID	PRODUCT_ID	USER_NAME	CONTENT	DATE
01	1001	mino	크기는 작은데 소리가 진짜 좋네요.	20230419
02	1001	yakasaha	생각보다 가성비 좋은 듯. 잡음도 없어요.	20230912
03	1002	kisoek	확실히 Y브랜드 스피커는 최고는 아니어도 표준은 함	20231120

SELECT A.PRODUCT_ID,
	   A.PRODUCT_NAME,
       B.USER_NAME,
       B.CONTENT,
       B.DATE
FROM PRODUCT A LEFT OUTER JOIN PRODUCT_REVIEW B
ON A.PRODUCT_ID = B.PRODUCT_ID;

PRODUCT_ID	PRODUCT_NAME	USER_NAME	CONTENT	DATE
1001	CreXXXXe 2ch 블루투스 스피커	mino	크기는 작은데 소리가 진짜 좋네요.	20230419
1001	CreXXXXe 2ch 블루투스 스피커	yakasaha	생각보다 가성비 좋은 듯. 잡음도 없어요.	20230912
1002	YAXXHA 2.1ch 모니터링 스피커(50W)	kisoek	확실히 Y브랜드 스피커는 최고는 아니어도 표준은 함	20231120
1003	PeXXXle 2ch 블루투스 스피커	[NULL]	[NULL]	[NULL]

RIGHT OUTER JOIN

LEFT OUTER JOIN과 반대로 SQL에서 오른쪽에 표기된 테이블(B)의 데이터가 모두 출력되는 방식이다. 마찬가지로 왼쪽 테이블(A)의 데이터 중 JOIN 조건에 매치되지 않는 행은 값이 NULL로 출력된다.

SELECT * FROM PRODUCT_REVIEW;

REVIEW_ID	PRODUCT_ID	USER_NAME	CONTENT	DATE
01	1001	mino	크기는 작은데 소리가 진짜 좋네요.	20230419
02	1001	yakasaha	생각보다 가성비 좋은 듯. 잡음도 없어요.	20230912
03	1002	kisoek	확실히 Y브랜드 스피커는 최고는 아니어도 표준은 함	20231120

SELECT * FROM PRODUCT;

PRODUCT_ID	PRODUCT_NAME	PRICE
1001	CreXXXXe 2ch 블루투스 스피커	42,500
1002	YAXXHA 2.1ch 모니터링 스피커(50W)	183,000
1003	PeXXXle 2ch 블루투스 스피커	21,500

SELECT A.USER_NAME,
	   A.CONTENT,
       A.DATE,
       B.PRODUCT_ID,
       B.PRODUCT_NAME
FROM PRODUCT_REVIEW A RIGHT OUTER JOIN PRODUCT B
ON A.PRODUCT_ID = B.PRODUCT_ID;

USER_NAME	CONTENT	DATE	PRODUCT_ID	PRODUCT_NAME
mino	크기는 작은데 소리가 진짜 좋네요.	20230419	1001	CreXXXXe 2ch 블루투스 스피커
yakasaha	생각보다 가성비 좋은 듯. 잡음도 없어요.	20230912	1001	CreXXXXe 2ch 블루투스 스피커
kisoek	확실히 Y브랜드 스피커는 최고는 아니어도 표준은 함	20231120	1002	YAXXHA 2.1ch 모니터링 스피커(50W)
[NULL]	[NULL]	[NULL]	1003	PeXXXle 2ch 블루투스 스피커

FULL OUTER JOIN

조인되는 테이블의 모든 데이터가 출력되는 방식으로, LEFT OUTER JOIN과 RIGHT OUTER JOIN의 합집합 개념으로 이해할 수 있을 것이다. 단, 이때 중복값은 제거된다.

SELECT * FROM PLAYSTATION_5;

NAME	GENRE
Spider-man 2	Action
Baldur's Gate 3	RPG
Ghost of tsushima	Action
Sniper Elite 5	TPS

SELECT * FROM X_BOX;

NAME	GENRE
STARFIELD	RPG
Baldur's Gate 3	RPG
Halo infinite	FPS
Sniper Elite 5	TPS

SELECT A.NAME as P_GAME,
       B.NAME as X_GAME,
       FROM PLAYSTAION_5 A FULL OUTER JOIN X_BOX B
       ON A.NAME = B.NAME;

P_GAME	X_GAME
[NULL]	STARFIELD
[NULL]	Halo infinite
Spider-man 2	[NULL]
Baldur's Gate 3	Baldur's Gate 3
Ghost of tsushima	[NULL]
Sniper Elite 5	Sniper Elite 5

자연 조인(NATURAL JOIN)

A 테이블과 B 테이블에서 동일한 이름을 가진 컬럼들이 모두 동일한 데이터를 가지고 있을 경우 JOIN이 되는 방식이다.
※ MSSQL에서는 지원하지 않는다.

SELECT * 
FROM PLAYSTAION_5 A NATURAL JOIN X_BOX B;

NAME	GENRE
Baldur's Gate 3	RPG
Sniper Elite 5	TPS

만약 PLAYSTATION_5 테이블에서만 'Sniper Elite 5'의 장르를 'Shooting'으로 바꾼다면 어떻게 될까?

SELECT * FROM PLAYSTATION_5;

NAME	GENRE
Spider-man 2	Action
Baldur's Gate 3	RPG
Ghost of tsushima	Action
Sniper Elite 5	Shooting

SELECT * 
FROM PLAYSTAION_5 A NATURAL JOIN X_BOX B;

NAME	GENRE
Baldur's Gate 3	RPG

두 테이블에 존재하는 'Sniper Elite 5' 행에서 GENRE 컬럼의 데이터가 다르므로 NATURAL JOIN 시에는 출력되지 않는다.

교차 조인(CROSS JOIN)

교차 조인(CROSS JOIN)은 별도의 JOIN 조건이 없는 경우 가능한 모든 경우를 출력하는 방식이다. 곱집합(Cartesian product)이라고도 한다.

SELECT emp_name, emp_id FROM EMPLOYEE;

emp_name	emp_id
Elly	001
Sian	002
Nalo	003

SELECT drink_name, drink_id FROM DRINK;

drink_name	drink_id
Coke	A01
Cider	A02
Green tea	B01

SELECT A.emp_name,
       A.emp_id
       B.drink_name
       B.drink_id
FROM EMPLOYEE A CROSS JOIN DRINK B;

emp_name	emp_id	drink_name	drink_id
Elly	001	Coke	A01
Elly	001	Cider	A02
Elly	001	Green tea	B01
Sian	002	Coke	A01
Sian	002	Cider	A02
Sian	002	Green tea	B01
Nalo	003	Coke	A01
Nalo	003	Cider	A02
Nalo	003	Green tea	B01

셀프 조인(SELF JOIN)

셀프 조인(SELF JOIN)은 말 그대로 자기 자신과 조인하는 방법이다. 하나의 테이블을 2개처럼 취급하여 JOIN하는 것으로, FROM 절에 같은 테이블이 2번 이상 등장하므로 ALIAS를 반드시 표기해야 한다.

다음과 같은 테이블 employees가 있다고 하자.

emp_no	first_name	last_name	dept_no
1001	Bear	Nalo	d001
1002	Evy	Frye	d002
1003	Yuna	Kim	d002
1004	Alis	Key	d003

여기서 같은 부서에서 일하는 직원만을 찾고자 할 때 셀프 조인을 사용할 수 있다.

SELECT A.first_name, AS EmployeeName1,
       B.first_name AS EmployeeName2,
       A.dept_no
FROM employees as A, employees as B
WHERE A.emp_no <> B.emp_no
AND A.demp_no = B.dept_no;

EmployeeName1	EmployeeName2	dept_no
Evy	Yuna	d002
Yuna	Evy	d002

서브쿼리(Subquery)

서브쿼리(Subquery)는 하나의 SQL문안에 존재하는 또 하나의 SQL문을 말한다. 사용되는 위치에 따라 세 가지로 구분할 수 있다.

구분	내용
SELECT절	스칼라 서브쿼리(Scalar subquery)
FROM절	인라인 뷰(Inline view)
WHERE, HAVING절	중첩 서브쿼리(Nested subquery)

Scalar subquery

스칼라 서브쿼리(Scalar subquery)는 주로 SELECT절에 위치하나, ‘컬럼’이 올 수 있는 대부분의 위치에 사용 가능한 서브쿼리이다.

스칼라 서브쿼리는 반드시 하나의 값만을 반환해야 한다.

SELECT 테이블명1.컬럼명,
       (SELECT 테이블명2.컬럼명 FROM 테이블명2 WHERE 조건) AS 컬럼명,
       테이블명1.컬럼명, …
FROM 테이블명1;

Inline view

인라인 뷰(Inline view)는 FROM절 등 ‘테이블’이 올 수 있는 위치에 사용 가능한 서브쿼리이다. 가상의 테이블로서 실제 데이터가 저장되지는 않으며, 임시적으로 생성되는 동적 뷰이다.

SELECT 테이블명1.컬럼명1, 테이블명2.컬럼명1, 테이블명2.컬럼명2
FROM 테이블명1,
     (SELECT 컬럼명1, 컬럼명2 FROM 테이블) AS 테이블명2
WHERE 조건;

Nested subquery

중첩 서브쿼리(Nested subquery)는 WHERE절과 HAVING절에 사용 가능하며, 몇 기준에 따라 다시 구분이 가능하다.

메인 쿼리와의 관계에 따른 구분

구분	내용
비연관(Uncorrelated) 서브쿼리	서브쿼리가 메인 쿼리의 컬럼을 가지고 있지 않은 서브쿼리로, 메인쿼리에 어떤 값을 제공하기 위한 목적으로 주로 사용됨
연관(Correlated) 서브쿼리	서브쿼리가 메인 쿼리의 컬럼을 가지고 있는 형태로, 메인 쿼리가 먼저 수행되어 읽어낸 데이터에 대하여 서브쿼리에서 조건을 설정할 때 주로 사용됨

반환되는 데이터 형태에 따른 구분

구분	내용
단일 행 서브쿼리	항상 1건 이하의 실행 결과만을 반환하며, 단일 행 비교 연산자(=, >, <, >= 등)와 함께 사용됨
다중 행 서브쿼리	서브쿼리의 실행 결과가 여러 건이며, 다중 행 비교 연산자(IN, ALL, ANY, SOME, EXISTS 등)와 함께 사용됨
다중 컬럼 서브쿼리	서브쿼리의 실행 결과가 여러 개의 컬럼으로, 서브쿼리와 메인 쿼리에서 비교하고자 하는 컬럼의 개수와 위치는 동일해야 함

참고 자료

유선배 SQL개발자(SQLD) 과외노트, 정미나, (주)시대교육
관계형 데이터베이스에서 조인(join)이란?

Frye 'de Bacon

AI, NLP, Data analysis로 나아가고자 하는 개발자 지망생

이전 포스트

[CS Study] Database - Concurrency Control

다음 포스트