[CS 전공지식노트] 조인의 종류와 원리

Join

두 개 이상의 테이블을 묶어 하나의 결과물로 만드는 것

• MySQL - JOIN / MongoDB - lookup 이라는 쿼리로 처리할 수 있다.

 

조인의 종류

• 내부 조인(Inner Join): 왼쪽 테이블과 오른쪽 테이블의 두 행이 모두 일치하는 행이 있는 부분만 결과 테이블에 표기
• 왼쪽 조인(Left Outer Join): 왼쪽 테이블의 모든 행이 결과 테이블에 표기
• 오른쪽 조인(Right Outer join): 오른쪽 테이블의 모든 행이 결과 테이블에 표기
• 합집합 조인(Full Outer Join): 두 개의 테이블을 기반으로 조인 조건에 만족하지 않는 행까지 모두 표기

 

SQL JOIN 시각화 사이트
https://sql-joins.leopard.in.ua/

 

내부 조인

두 테이블 간 교집합

SELECT * FROM Table A
INNER JOIN Table B ON
A.key = B.key

 

왼쪽 조인

테이블 B에 일치하는 부분의 레코드와 함께 테이블 A를 기준으로 완전한 레코드 집합을 생성

만약 테이블 B에 일치하는 항목이 없으면 해당 값은 null 값이 된다.

SELECT * FROM Table A
LEFT JOIN Table B ON
A.key = B.key

 

오른쪽 조인

테이블 A에서 일치하는 부분의 레코드와 함께 테이블 B를 기준으로 완전한 레코드 집합을 생성

만약 테이블 A에 일치하는 항목이 없으면 해당 값은 null 값이 된다.

SELECT * FROM Table A
RIGHT JOIN Table B ON
A.key = B.key

 

합집합 조인(완전 외부 조인)

양쪽 테이블에서 일치하는 레코드와 함께 테이블 A와 테이블 B의 모든 레코드 집합을 생성

이때 일치하는 항목이 없으면 누락된 쪽에 null 값이 포함되어 출력

SELECT * FROM Table A
FULL OUTER JOIN Table B ON
A.key = B.key

 

조인의 원리

조인은 조인의 원리를 기반으로 조인 작업이 이루어진다.

• 조인의 원리
  • 중첩 루프 조인
  • 정렬 병합 조인
  • 해시 조인

 

중첩 루프 조인(NLJ, Nested Loop Join)

중첩 for 문과 같은 원리로 조건에 맞는 조인을 하는 방법이다. 

• 랜덤 접근에 대한 비용이 증가 하므로 대용량 테이블에는 사용하지 않는다.

ex) t1, t2 테이블을 조인

첫 번째 테이블에 행을 한 번에 하나씩 읽고 그 다음 테이블도 행을 하나씩 읽어 조건에 맞는 레코드를 찾아 결과 값을 반환한다.

// 의사 코드
for each row in t1 matching reference key {
	for each row in t2 matching reference key {
    	if row saticsfies join conditions, send to client
    }
}

블록 중첩 루프 조인(BNL, Block Nested Loop)

중첩 루프 조인에서 발전한 조인 테이블을 작은 블록으로 나눠 블록 하나씩 조인하는 방식

 

 

정렬 병합 조인

각각의 테이블을 조인할 필드 기준으로 정렬하고 정렬이 끝난 후 조인 작업을 수행하는 조인

조인할 때 사용할 적절한 인덱스가 없고 대용량의 테이블을 조인하고 조인 조건으로 <, > 등 범위 비교 연산자가 있을 때 사용한다.

 

해시 조인

해시 테이블을 기반으로 조인하는 방법이다.

• 두 개의 테이블을 조인한다고 가정했을 때 하나의 테이블이 메모리에 온전히 들어간다면 보통 중첩 루프 조인보다 더 효율적이다.
• 메모리에 올릴 수 없을 정도로 크다면 디스크를 사용하는 비용이 발생하며 동등(=) 조건에서만 사용할 수 있다.

MySQL의 경우 MySQL 8.0.18 릴리스와 함께 해시 조인을 사용할 수 있게 되었고 빌드 단계, 프로브 단계로 나뉜다.

 

빌드 단계

입력 테이블 중 하나를 기반으로 메모리 내 해시 테이블을 빌드하는 단계

두 테이블을 조인한다 했을 때 둘 중 바이트가 더 작은 테이블을 기반으로 테이블을 빌드한다.

 

조인에 사용되는 필드가 해시 테이블의 키로 사용된다.(위의 예시에서 contries.country_id가 키로 사용)

 

프로브 단계

레코드 읽기를 시작하며 각 레코드에서 조인에 사용된 필드인 키에 일치하는 레코드를 찾아서 결과 값으로 반환한다.

 

프로브 단계를 통해 각 테이블은 한 번씩만 읽게 되어 중첩해서 테이블을 읽는 중첩 루프 조인보다 보통은 성능이 더 좋다.

사용 가능한 메모리양은 시스템 변수 join_buffer_size에 의해 제어되며 런타임 시 조정할 수 있다.