[DB] Join - 논리적 조인(Inner, Outer, Cross, Self), 물리적 조인(Nested Loop, Sort Merge, Hash)

안녕하세요! 오늘은 데이터베이스 조인에 대해 알아보겠습니다.

조인이란 두 개 이상의 테이블의 레코드를 조합하여 하나의 열로 표현하는 것으로, 테이블로서 저장되거나 또는 그 자체로 이용할 수 있는 결과를 만들어 냅니다.

💡 조인의 필요성
관계형 데이터베이스에서는 중복 데이터를 피하기 위해 데이터를 쪼개어 여러 테이블로 나누어 저장합니다. 이렇게 데이터가 분리되어 저장되어 있을 때, 원하는 결과를 다시 도출하기 위해 여러 데이블을 조합할 필요가 있습니다. 각 테이블에 저장된 데이터를 효과적으로 검색하기 위해 조인이 필요합니다.

 

논리적 조인

논리적 조인이란 사용자가 SQL문으로 지정하는 테이블 결합 방식을 의미합니다.

내부 조인 (Inner Join)	공통 존재 컬럼의 값이 같은 경우를 추출하는 기법입니다.
외부 조인 (Outer Join)	- 왼쪽 외부 조인 (Left Outer Join): 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터를 추출하는 기법입니다. - 오른쪽 외부 조인 (Right Outer Join): 오른쪽 테이블의 모든 데이터와 왼쪽 테이블의 동일 데이터를 추출하는 기법입니다. - 완전 외부 조인 (Full Outer Join): 양쪽의 모든 데이터를 추출하는 기법입니다.
교차 조인 (Cross Join)	조인 조건이 없는 모든 데이터 조합을 추출하는 기법입니다.
셀프 조인 (Self Join)	자기 자신에게 별칭을 지정한 후 다시 조인하는 기법입니다.

 

[도서] 테이블

책번호	책명
111	운영체제
222	자료구조
555	컴퓨터구조

[도서가격] 테이블

책번호	가격
111	20,000
222	25,000
333	10,000
444	15,000

 

Inner Join

내부 조인은 조인하고자 하는 테이블에서 공통된 요소들을 통해 결합하는 방식입니다.

📋 예를 들어 '도서' 테이블과 '도서가격' 테이블에서 책번호가 같은 것끼리 조인하여 책번호, 책명, 가격을 출력하고자 합니다. ('도서' 테이블은 A라는 별칭, '도서가격' 테이블은 B라는 별칭으로 사용)

SELECT A.책번호, A, 책명, B.가격
FROM 도서 A JOIN 도서가격 B
ON A.책번호 = B.책번호;

 

A.책번호	A.책명	B.가격
111	운영체제	20,000
222	자료구조	25,000

 

Outer Join

외부 조인이란 공통 영역을 포함해 한쪽 테이블의 다른 데이터를 포함하는 조인 방식입니다. 외부 조인에는 왼쪽 외부 조인, 오른쪽 외부 조인, 완전 외부 조인이 있습니다.

 

Left Outer Join

왼쪽 외부 조인은 왼쪽 테이블의 모든 데이터와 오른쪽 테이블의 동일 데이터를 추출하는 기법입니다. FROM절에 적어준 테이블이 left, JOIN절에 적어준 테이블이 right가 됩니다.

📋 예를 들어 '도서' 테이블과 '도서가격' 테이블에서 '도서' 테이블 기준으로 왼쪽 외부 조인하여 A.책번호, A.책명, B.책번호, B.가격을 출력하고자 합니다. 

SELECT A.책번호, A, 책명, B.책번호, B.가격
FROM 도서 A LEFT JOIN 도서가격 B
ON A.책번호 = B.책번호;

 

A.책번호	A.책명	B.책번호	B.가격
111	운영체제	111	20,000
222	자료구조	222	25,000
555	컴퓨터구조	NULL	NULL

 

Right Outer Join

오른쪽 외부 조인은 왼쪽 테이블의 동일 데이터와 오른쪽 테이블의 모든 데이터를 추출하는 기법입니다. FROM절에 적어준 테이블이 left, JOIN절에 적어준 테이블이 right가 됩니다.

📋 예를 들어 '도서' 테이블과 '도서가격' 테이블에서 '도서가격' 테이블 기준으로 오른쪽 외부 조인하여 A.책번호, A.책명, B.책번호, B.가격을 출력하고자 합니다. 

SELECT A.책번호, A, 책명, B.책번호, B.가격
FROM 도서 A RIGHT JOIN 도서가격 B
ON A.책번호 = B.책번호;

 

A.책번호	A.책명	B.책번호	B.가격
111	운영체제	111	20,000
222	자료구조	222	25,000
NULL	NULL	333	10,000
NULL	NULL	444	15,000

 

Full Outer Join

양쪽의 모든 데이터를 추출하는 기법으로, Left Outer Join와 Right Outer Join의 결과를 합친 것과 같습니다.

📋 예를 들어 '도서' 테이블과 '도서가격' 테이블에서 완전 외부 조인하여 A.책번호, A.책명, B.책번호, B.가격을 출력하고자 합니다. 

SELECT A.책번호, A, 책명, B.책번호, B.가격
FROM 도서 A FULL JOIN 도서가격 B
ON A.책번호 = B.책번호;

 

A.책번호	A.책명	B.책번호	B.가격
111	운영체제	111	20,000
222	자료구조	222	25,000
NULL	NULL	333	10,000
NULL	NULL	444	15,000
555	컴퓨터구조	NULL	NULL

 

Cross Join

교차 조인이란 조인 조건이 없는 모든 데이터 조합을 추출하는 기법입니다. 모든 경우의 수에 대한 결합을 보여주어 특정한 기준이 필요없으므로 ON절이 없어지게 됩니다.

✅ 교차 조인의 특징
내부/외부 조인은 두 테이블 간의 특정 기준에 의한 데이터 결합의 결과를 보여주는 방식이라면,
교차 조인은 특정 기준 없이 두 테이블 간 가능한 모든 경우의 수에 대한 결합을 결과를 보여주는 방식입니다.

📋 예를 들어 '도서' 테이블과 '도서가격' 테이블에서 교차 조인하여 A.책번호, A.책명, B.책번호, B.가격을 출력하고자 합니다. 

SELECT A.책번호, A, 책명, B.책번호, B.가격
FROM 도서 A CROSS JOIN 도서가격 B

 

A.책번호	A.책명	B.책번호	B.가격
111	운영체제	111	20,000
111	운영체제	222	25,000
111	운영체제	333	10,000
111	운영체제	444	15,000
222	자료구조	111	20,000
222	자료구조	222	25,000
222	자료구조	333	10,000
222	자료구조	444	15,000
555	컴퓨터구조	111	20,000
555	컴퓨터구조	222	25,000
555	컴퓨터구조	333	10,000
555	컴퓨터구조	444	15,000

 

Self Join

셀프 조인은 말 그대로 자기 스스로를 결합시키는 조인입니다. 즉 참조 테이블이 다른 테이블이 아닌 자기 자신입니다.

[도서] 테이블

책번호	책명	선수과목_책번호
111	운영체제	222
222	자료구조	555
555	컴퓨터구조	NULL

📋 예를 들어 '도서' 테이블을 A, B라는 별칭으로 따로 설정하여 책별 선수과목 책번호와 책명을 출력하고자 합니다.

SELECT A.책번호, A.책명, B.책번호, B.책명
FROM 도서 A JOIN 도서 B
ON A.선수과목_책번호 = B.책번호;

 

A.책번호	A.책명	B.책번호	B.책명
111	운영체제	222	자료구조
222	자료구조	555	컴퓨터구조

 

출처: https://bit.ly/3KwGYwX

 

---

물리적 조인

물리적 조인이란 DBMS 엔진에 의해 내부적으로 발생하는 테이블 결합 방식을 의미합니다.

중첩 반복 조인 (Nested Loop Join)	바깥 테이블의 처리 범위를 하나씩 액세스하면서 그 추출된 값으로 안쪽 테이블을 조인하는 방식입니다.
정렬 병합 조인 (Sort Merge Join)	양쪽 테이블의 처리 범위를 각자 액세스하여 정렬한 결과를 차례로 스캔하며, 연결고리 조건으로 Merge하는 방식입니다.
해시 매치 조인 (Hash Join)	Hash Table을 생성하여 Hash Function에 의한 탐색을 하여 조인하는 방식입니다.

 

Nested Loop Join

중첩 반복 조인이란 선행(바깥쪽) 테이블의 처리 범위를 하나씩 액세스하면서 그 추출된 값으로 후행(안쪽) 테이블을 조인하는 방식입니다. 두 개의 테이블의 행을 각각 모두 확인하여 조인하는 방법으로, 중첩된 for문의 형태처럼 동작합니다. 실행 속도는 선행 테이블 사이즈에 후행 테이블 접근 횟수를 곱한 값이 됩니다. 선행 테이블을 driving 테이블, 후행 테이블을 driven 테이블이라고 합니다.

SELECT /*+ USE_NL(테이블1, 테이블2) */

출처: https://bit.ly/3KwGYwX

💡 순차적 처리, 랜덤 액세스

선행 테이블의 처리 범위를 하나씩 액세스하면서 안쪽 테이블을 조인하는 작업이 반복적이고 순차적으로 진행됩니다. 선행 테이블의 추출된 값을 통해 후행 테이블을 액세스할 때 랜덤 I/O가 발생합니다. 선행 테이블은 최초 ROW만 액세스가 발생하고 이후에는 스캔 방식으로 진행됩니다.

💡 선행 테이블 (Driving Table)

중첩 반복 조인은 선행 테이블의 조건을 만족하는 행을 추출하여 후행 테이블을 읽으면서 조인을 수행하게 됩니다. 따라서 선행 테이블의 조건을 만족하는 모든 행의 수 만큼 반복 수행합니다. 따라서 선행 테이블의 조건을 만족하는 행의 수가 많으면 그만큼 후행 테이블 조인 작업은 반복되기 때문에, 선행 테이블의 행의 수가 적은 테이블을 선행 테이블로 선택하는 것이 전체 일의 양을 줄일 수 있습니다. 후행 테이블의 필터링 조건은 선행 테이블에서 나온 결과를 한 번 더 걸러주는 체크 조건 역할을 할 뿐 전체 처리량을 좌우하지 않기 때문에 선행 테이블의 처리 범위가 전체 일의 양을 결정합니다. 

💡 후행 테이블 (Driven Table)

선행 테이블과 일치하는 값을 후행 테이블에서 찾아 조인하므로 후행 테이블의 연결고리가 중요합니다. 즉, 후행 테이블에 인덱스가 있어야 테이블 전체를 탐색하지 않고 필요한 행에 대해서만 탐색하여 효율적입니다. 비용기반 옵티마이저(CBO: Cost-Based Optimizer)는 쿼리 수행 시간을 바탕으로 실행계획을 생성하기 때문에, 인덱스가 있거나 레코드 수가 적은 테이블을 후행(Driven) 테이블로 결정합니다.

✅ 중첩 반복 조인의 장단점
[장점]
- 메모리 사용량이 적음
- 좁은 범위에서 성능이 좋음
[단점]
- 데이터를 랜덤으로 액세스하여 결과 집합이 많으면 속도가 느려짐
- join index가 없거나 조인 집합을 구성하는 검색 조건이 조인 범위를 줄이지 못할 경우 비효율적임

 

Sort Merge Join

정렬 병합 조인은 양쪽 테이블을 정렬 후 스캔하여 조인을 수행합니다. 양쪽 테이블의 처리 범위를 각자 액세스하여 정렬한 결과를 차례로 스캔하면서 연결고리의 조건으로 머지하는 방식입니다. 조인의 대상 범위가 넓어 랜덤 액세스를 줄이기 위한 경우 또는 연결고리에 마땅한 인덱스가 존재하지 않을 경우 사용합니다. 중첩 반복 조인보다 훨씬 빨라지는 경우도 많으나 일반적으로는 사용 빈도가 적습니다.

SELECT /*+ USE_MERGE(테이블1, 테이블2) */

출처: https://bit.ly/3KwGYwX

💡 동시적 처리, 스캔 방식

각 키에 의해 정렬된 테이블을 선행/후행 개념 없이 병합하여 조인을 수행합니다. 선행 테이블, 후행 테이블의 크기는 성능과 관련이 없기 때문에 조인의 방향과는 무관합니다. 그러나 선행 테이블에는 메모리 사용량이 적은 중복 행이 존재하지 않는 테이블 사용을 권장합니다. 자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우를 제외하고, 스캔 방식으로 데이터를 읽기 때문에 랜덤 액세스로 부담이 되는 넓은 범위의 데이터를 처리할 때 이용합니다.

💡 정렬

양 테이블은 모두 조인키에 의해 정렬되어 있어야 합니다. 정렬할 데이터가 많아 모든 정렬 작업을 수행하기 어려운 경우에는 임시 영역을 사용하므로 성능이 떨어질 수 있습니다. 

✅ 정렬 병합 조인의 장단점
[장점]
- 랜덤 액세스가 줄어 시스템 부하 감소
- 주로 스캔 방식으로 데이터를 읽기 때문에 랜덤 액세스로 부담이 되는 넓은 범위의 데이터를 처리할 때 효과적
[단점]
- 정렬 시 메모리 사용량이 증가함
- 두 테이블의 크기 차이가 많이 나는 경우 비효율적임

 

Hash Join

해시 매치 조인은 두 테이블 중 작은 테이블로 해시 테이블 버킷(Hash Table Buckets)을 생성하고 큰 테이블로 Hash Function에 의한 탐색을 하면서 조인하는 방식입니다. 테이블의 인덱스는 사용되지 않기 때문에, 인덱스가 없는 테이블을 조인하려고 할 때 탁월한 성능을 보입니다. 

SELECT /*+ USE_HASH(테이블1, 테이블2) */

출처: https://bit.ly/3KwGYwX

💡 Nested Loop Join의 랜덤액세스, Sort Merge Join의 정렬의 단점 극복

랜덤 엑세스가 있으나 중첩 반복 조인과는 달리 빠르고, 정렬의 부하가 많이 발생하는 정렬 병합 조인의 단점을 보완하기 위해 Hash Function을 이용합니다. 해싱 함수는 직접적인 연결을 담당하는 것이 아니라 연결될 대상을 모아두는 역할을 합니다.

💡 해시

두 테이블 중 크기가 작은 테이블을 기준으로 해시 테이블을 생성합니다. 해싱 함수의 시간 복잡도는 O(1)로 속도가 빨라 주로 대용량의 데이터를 처리할 때 사용합니다. 하지만 해시 충돌 및 해시 체인의 크기가 커지는 것을 방지하기 위해 중복되는 데이터가 적은 경우, 해시 테이블 버킷에 충분히 담길 수 있도록 선행 테이블의 데이터의 양이 적은 경우 사용하는 것이 좋습니다.

💡 조인 결과

조인의 결과는 정렬하지 않은 상태로 출력됩니다. 특정 컬럼을 기준으로 정렬하고 싶다면 ORDER BY절을 이용해야 합니다.

✅ 해시 매치 조인의 장단점
[장점]
- 대량의 데이터를 조인할 때 사용
- 양쪽 테이블 모두 JOIN 컬럼에 인덱스가 없을 경우 사용 - 속도가 빠름
[단점]
- 해시 테이블 생성 시 많은 메모리 사용하여 오버헤드가 발생할 수 있음
- 소량의 데이터를 조인할 때 오히려 불필요한 I/O가 증가할 수 있음
- CBO에서만 가능하며, CPU 성능에 의존적

 

---

[참조]

https://doorbw.tistory.com/223

[MS SQL Server] #12_조인(JOIN)이란 무엇일까?, 기초적인 조인들!

https://velog.io/@yoonee1126/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%B2%A0%EC%9D%B4%EC%8A%A4-%EB%85%BC%EB%A6%AC%EC%A0%81%EB%AC%BC%EB%A6%AC%EC%A0%81-%EC%A1%B0%EC%9D%B8

[데이터베이스] 논리적/물리적 조인

https://snepbnt.tistory.com/436

데이터베이스 주요 개념 2. Nested loop, Sort Merge, Hash join

https://needjarvis.tistory.com/162

Nested Loop, Sort-Merge, Hash Join 조인연산

https://devlog.changhee.me/posts/Join%EA%B8%B0%EB%B2%95_%EC%A0%95%EB%A6%AC/

Join 기법 정리 (Nested Loop, Sort Merge, Hash)

https://blog.sonim1.com/108

[MSSQL] JOIN의 방식 - Nested loop Join / Merge Join / Hash Join