[SQL] JOIN(결합)에 대해서(with.옵티마이저 및 결합 알고리즘)

fk값이 무조건 있는 경우에는 left join 보다는
inner join을 사용하는 
습관을 길러야 한다고 한다고 말씀하셨다.
그 이유를 찾고 공부한 내용을 정리하기 위해 작성한다.

---

Join(결합)이란?

조인이란 두 개 이상의 테이블을 서로 묶어 하나의 결과 집합을 만들어 내는 것을 말한다. 즉, 데이터베이스에 있는 두 개 이상의 테이블의 데이터를 결합할 수 있는 SQL의 필수 기능이고 조인 작업을 사용하면 테이블이 서로 어떻게 관련되어 있는지 지정하여 여러 테이블에서 데이터를 검색할 수 있다.

이 글에서 다룰 결합이다.

• 내부 조인 (INNER JOIN)|

• 외부 조인 (OUTER JOIN)
  • 왼쪽 (LEFT OUTER)
  • 오른쪽 (RIGHT OUTER)
    

 

 

내부 조인 (INNER JOIN)

두 테이블에 일치하는 값이 있는 행만 반환

SELECT column1, column2, ...
FROM table1
JOIN table2 ON table1.column = table2.column;

 

 외부 조인 (OUTER JOIN)

기존 테이블을 기준으로 조인 테이블에 데이터가 존재하지 않더라도 기준 테이블의 모든 데이터가 조회되고, 데이터가 존재할 경우 해당 데이터를 참조할 수 있다.

 

 

왼쪽 외부 조인 (LEFT OUTER JOIN)

왼쪽 테이블의 모든 행과 오른쪽 테이블의 일치하는 행을 반환하고 오른쪽 테이블에 일치하는 행이 없으면 결과에 오른쪽 테이블의 열에 대한 NULL 값이 포함된다.

SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

 

오른쪽 외부 조인 (RIGHT OUTER JOIN)

오른쪽 테이블에서 모든 행을 반환하고 왼쪽 테이블에서 일치하는 행을 반환하고 왼쪽 테이블에 일치하는 행이 없으면 결과에 왼쪽 테이블의 열에 대한 NULL 값이 포함된다.

SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

 

 

 

---

 

 

옵티마이저(Optimizer)란?

옵티마이저는 모든 관계형 데이터베이스 관리 시스템(RDBMS)의 핵심 구성 요소입니다.
주요 기능은 데이터베이스에서 사용 가능한 데이터를 기반으로 지정된 SQL 쿼리에 대한 최적의 실행 계획을 생성하는 것입니다. 옵티마이저는 SQL에서 조인 연산을 수행할 때 내부적으로 선택되는 알고리즘을 선택하는데 데이터의 크기, 결합키(Key), 인덱스(Index)와 같은 요인에 따라장 효율적인 방법을 결정한다. 그러나 작동 방식을 이해하고 최적화 도구와 함께 작동하도록 데이터베이스 스키마와 SQL 쿼리를 최적화하는 것이 중요하다. 여기에는 인덱스 작성, 통계 수집, 쿼리 재작성, 쿼리 힌트 사용 등이 포함될 수 있으며 최적화 도구가 특정 실행 계획을 선택하도록 강제할 수 있다.

 

 

---

 결합(Join) 알고리즘

SQL에서 조인 연산을 수행할 때 내부적으로 선택되는 알고리즘의 종류는 아래와 같다

1. Nested Loops Join
2. Hash Join 
3. Sort Merge Join

---

 

Nested Loops Join 

흔히 사용하는 중첩 for 문의 원리와 같이 중첩 반복을 사용하는 알고리즘이다.

1. Table A와 Table B 가 어떤 Key 를 기준으로 결합을 진행

2. Table A (구동 테이블, Driving Table) 의 첫 번째 행에서 출발해 Table B (내부 테이블, Inner Table) 의 모든 행을 스캔하고 이때 결합 조건이 맞으면 값을 리턴

3. Table A 의 첫 번째 행의 스캔이 끝나면 두 번째 행이 Table B 의 모든 행을 스캔

4. 2~3번 과정을 반복하여 Table A 의 마지막 행이 Table B 의 모든 행을 스캔하면 결합이 완료

---

인덱스(Index)

TABLE의 컬럼을 색인화(따로 파일로 저장)하여 테이블의 검색속도를 향상시키기 위한 자료구조이다.
검색시 해당 TABLE의 레코드를 Full Scan 하는게 아니라 색인화 되어있는 INDEX 파일을 검색하여 검색속도를 증가

 

 

---

Hash Join

해시 테이블을 사용하여 두 개의 큰 테이블을 조인하는 조인 작업의 한 유형이며, 특히 큰 테이블의 경우 쿼리 성능을 향상시킬 수 있는 효율적인 조인 알고리즘이다.

작동방식

1. 먼저, 두 개의 테이블 중 작은 테이블이 선택되고 이 테이블은 작은 크기 때문에 메모리에 로드
2. 선택된 테이블의 join 컬럼 값을 hash 함수를 사용하여 해시 테이블로 매핑하고 이 해시 테이블은 메모리에 저장되며, 각 해시 버킷에는 해당하는 키 값이 있는 모든 행에 대한 포인터가 포함된다.
3. 나머지 큰 테이블은 작은 테이블과 동일한 해시 함수를 사용하여 join 컬럼 값을 해싱, 그런 다음 해싱 된 값을 사용하여 해시 테이블에서 해당하는 버킷을 찾는다.
4. 일치하는 버킷에서 행을 찾는다. 일치하는 버킷에 대해 반복하여, 일치하는 키 값을 가진 모든 행을 찾는다.
5. 일치하는 행을 조인하고, 결과 집합을 생성한다.

SQL Hash Join은 각 테이블을 한 번씩만 읽어들이면 되기 때문에 매우 효율적이지만 해시 테이블을 저장하기 위한 상당한 메모리 리소스가 필요하며, 메모리에 맞지 않는 매우 큰 테이블에는 적합하지 않을 수 있다. 이러한 경우, 정렬 병합 조인과 같은 다른 조인 알고리즘이 더 적합할 수 있다.

---

 HASH JOIN의 성능 개선 포인트 

[DB] 데이터베이스 HASH JOIN (해시 조인)에 대하여

---

 

Sort Merge Join

Sort Merge Join은 먼저 두 테이블을 정렬한 다음에 join을 수행한다. 이 알고리즘은 대부분의 경우에서 좋은 성능을 보여주며, 테이블의 크기가 매우 큰 경우에도 효율적으로 작동한다.

Sort Merge Join이 작동하는 방식

1. 먼저, 두 개의 테이블이 메모리에 로드된다.
2. Join에 사용될 열을 기준으로 두 개의 테이블이 정렬된다.
3. 두 개의 정렬된 테이블이 병합된다. 이 단계에서는 두 개의 정렬된 테이블이 하나의 큰 정렬된 테이블로 병합하고 이를 위해 일치하는 값이 발견되면 두 테이블에서 해당하는 행이 병합되고 결과 행이 생성한다.
4. Join 작업이 끝나면 결과 집합이 반환

Sort Merge Join은 대부분의 경우에서 효율적이지만, 정렬 작업에 대한 비용이 있으며 메모리 사용이 높을 수 있다. 또한 두 개의 테이블을 메모리에 저장할 수 없는 경우에는 작동하지 않을 수 있다고 한다.

---

참고자료

[SQL] "성능 관점"에서 보는 결합(Join)

 

[DB] Table간 Join시 Inner Join과 Left Join 후 where의 차이

 

[SQL] JOIN(INNER, LEFT OUTER)

 

[DB] 데이터베이스 HASH JOIN (해시 조인)에 대하여

 

[오라클] Join 원리, 개념 파악하기(loop join, sort_merge join, hash join)