조인(Join)
두 개 이상의 테이블을 묶어 하나의 결과물을 만드는 것
MySQL에서는 JOIN, MongoDB에서는 lookup이라는 쿼리로 처리할 수 있다.
하지만 MongoDB의 lookup은 성능이 떨어지므로, 여러 테이블을 조인하는 작업이 많을 경우, 관계형 데이터베이스를 써야 한다.
두 테이블의 조인을 위해서는 기본키와 외래키 관계로 맺어져야 하고, 이를 일대다 관계라고 한다.
조인의 종류
INNER JOIN, (LEFT, RIGHT, FULL) OUTER JOIN, CROSS JOIN, SELF JOIN 이 있다.
- INNER JOIN(내부 조인)은 두 테이블을 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 있어야 한다.
- OUTER JOIN(외부 조인)은 두 테이블을 조인할 때, 1개의 테이블에만 데이터가 있어도 결과가 나온다.
- CROSS JOIN(상호 조인)은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인하는 기능이다.
- SELF JOIN(자체 조인)은 자신이 자신과 조인한다는 의미로, 1개의 테이블을 사용한다.
INNER JOIN(내부 조인)
왼쪽 테이블과 오른쪽 테이블의 두 행이 모두 일치하는 행이 있는 부분만 표기한다.
두 테이블 간 교집합을 나타낸다.
두 테이블을 연결할 때 가장 많이 사용하는 것이 내부 조인이다.
그냥 조인이라고 부르면 내부 조인을 의미한다.
LEFT OUTER JOIN(왼쪽 조인, 줄여서 LEFT JOIN으로도 쓴다.)
왼쪽 테이블의 모든 행이 결과 테이블에 표기된다.
만약 테이블 B에 일치하는 항목이 없으면 해당 값은 null 값이 된다.
한쪽 테이블에만 데이터가 있어도 결과가 나온다.
RIGHT OUTER JOIN(왼쪽 조인, 줄여서 LEFT JOIN으로도 쓴다.)
오른쪽 테이블의 모든 행이 결과 테이블에 표기된다.
만약 테이블 A에 일치하는 항목이 없으면 해당 값은 null이 된다.
FULL OUTER JOIN(합집합 조인 or 완전 외부 조인)
두 개의 테이블을 기반으로 조인 조건에 만족하지 않는 행까지 모두 표기한다.
양쪽 테이블에서 일치하는 레코드와 함께 테이블 A와 테이블 B의 모든 레코드 집합을 생성한다.
이때 일치하는 항목이 없으면 누락된 쪽에 null 값이 포함되어 출력된다.
CROSS JOIN(상호 조인)
한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인시키는 기능이다.
상호 조인 결과의 전체 행 개수는 두 테이블의 각 행의 개수를 곱한 수만큼 됩니다.
카티션 곱(CARTESIAN PRODUCT)라고도 한다.
SELECT * FROM TableA A
CROSS JOIN TableB B
SELF JOIN(자체 조인)
자체 조인은 자기 자신과 조인하므로 1개의 테이블을 사용한다.
테이블과 컬럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭(Alias)을 사용해야 한다.
SELECT * FROM Table A
JOIN Table B
WHERE 조건
SQL JOIN 시각화 사이트 : https://sql-joins.leopard.in.ua/
조인의 원리
중첩 루프 조인 (NLJ, Nested Loop Join)
중첩 for 문과 같은 원리로 조건에 맞는 조인을 하는 방법
랜덤 접근에 대한 비용이 많이 증가하므로 대용량의 테이블에서는 사용하지 않는다.
예를 들어 "t1, t2 테이블을 조인하다."라고 했을 때,
첫 번째 테이블에서 행을 한 번에 하나씩 읽고, 그다음 테이블에서도 행을 하나씩 읽어 조건에 맞는 레코드를 찾아 결괏값을 반환한다.
중첩 루프 조인에서 발전했고, 조인할 테이블을 작은 블록으로 나눠서 블록 하나씩 조인하는 블록 중첩 루프 조인(BNL, Block Nested Loop)이라는 방식도 있다.
정렬 병합 조인(Sort Merge Join)
각각의 테이블을 조인할 필드 기준으로 정렬하고 정렬이 끝난 이후에 조인 작업을 수행하는 조인
조인할 때 쓸 적절한 인덱스가 없고, 대용량의 테이블들을 조인하고 조인 조건으로 <, > 등 범위 비교 연산자가 있을 때 사용한다. 또한, 동등(=) 조인에서만 사용할 수 있다.
해시 조인(Hash Join)
해시 테이블을 기반으로 조인하는 방법
두 개의 테이블을 조인한다고 했을 때 하나의 테이블이 메모리에 온전히 들어간다면 보통 중첩 루프 조인보다 더 효율적이다.
MySQL의 해시 조인 단계는 빌드 단계, 프로브 단계로 나뉜다.
빌드 단계
입력 테이블 중 하나를 기반으로 메모리 내 해시 테이블을 빌드하는 단계
예를 들어 persons와 countries라는 테이블을 조인한다고 했을 때 둘 중에 바이트가 더 작은 테이블을 기반으로 해서 테이블을 빌드한다.
또한, 조인에 사용되는 필드가 해시 테이블의 키로 사용된다. 아래 그림에서 'countries.country_id'가 키로 사용된다.
프로브 단계
레코드 읽기를 시작하는 단계
각 레코드에서 'persons.country_id'에 일치하는 레코드를 찾아서 결괏값으로 반환한다.
이를 통해 각 테이블은 한 번씩만 읽게 되어, 중첩해서 두 개의 테이블을 읽는 중첩 루프 조인보다 보통은 성능이 더 좋다. 사용 가능한 메모리양은 시스템 변수 join_buffer_size에 의해 제어되며, 런타임 시에 조정이 가능하다.
참고
https://blog.skby.net/%EC%A4%91%EC%B2%A9-%EB%B0%98%EB%B3%B5-%EC%A1%B0%EC%9D%B8-nested-loop-join/
https://dev.mysql.com/blog-archive/hash-join-in-mysql-8/
면접을 위한 CS 전공지식 노트 - 주홍철 저
'CS' 카테고리의 다른 글
| [CS] 프로세스와 스레드 (0) | 2023.11.20 |
|---|---|
| [CS] 네트워크 기기 (0) | 2023.11.05 |
| [CS] 네트워크 분류 / 네트워크 성능 분석 명령어 / 네트워크 프로토콜 표준화 (0) | 2023.11.03 |
| [CS] OSI 7계층 - 세션, 표현, 응용 계층 (0) | 2023.08.14 |
| [CS] 가상메모리 (0) | 2023.08.10 |
