7조 SQL JOIN

🔍 JOIN이란 무엇인가?

A join clause in the Structured Query Language (SQL) combines columns from one or more tables into a new table. The operation corresponds to a join operation in relational algebra. Informally, a join stitches two tables and puts on the same row records with matching fields : INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS. - Wikipedia

• 하나 이상의 테이블에 존재하는 컬럼을 새 테이블로 결합하는 방식
• 각 테이블에 저장된 데이터를 효과적으로 검색하기 위해 활용합니다!

🗂️ JOIN의 유형들

INNER JOIN

조인 구문에 기반한 2개의 테이블의 컬럼 값을 결합해 새로운 결과 테이블을 보여줍니다.

• 예시 코드

  SELECT A.column1, B.column2
  FROM TableA A
  INNER JOIN TableB B
  ON A.common_column = B.common_column;

LEFT JOIN (LEFT OUTER JOIN)

기준 테이블의 값 + 테이블과 기준 테이블의 중복된 값을 보여줍니다.

우측 테이블에 조인할 칼럼의 값이 없는 경우 사용하며 좌측 테이블의 모든 데이터를 포함하는 결과 집합을 생성합니다.

• 예시 코드

  SELECT A.column1, B.column2
  FROM TableA A
  LEFT OUTER JOIN TableB B
  ON A.common_column = B.common_column;

RIGHT JOIN (RIGHT OUTER JOIN)

LEFT OUTER JOIN의 반대입니다.

좌측 테이블에 조인할 칼럼의 값이 없는 경우 사용하며 우측 테이블의 모든 데이터를 포함하는 결과 집합을 생성합니다.

• 예시 코드

  SELECT A.column1, B.column2
  FROM TableA A
  RIGHT OUTER JOIN TableB B
  ON A.common_column = B.common_column;

FULL JOIN (FULL OUTER JOIN)

양쪽 테이블 모두 OUTER JOIN이 필요할 때 사용합니다.

• 예시 코드

  SELECT A.column1, B.column2
  FROM TableA A
  FULL OUTER JOIN TableB B
  ON A.common_column = B.common_column;

CROSS JOIN

조인되는 두 테이블에서 곱집합을 반환합니다. → 모든 경우의 수를 전부 표현해줍니다.

• 예시 코드

  SELECT A.column1, B.column2
  FROM TableA A
  CROSS JOIN TableB B;

SELF JOIN

한 테이블에서 자기 자신에게 조인을 진행합니다. → 하나의 테이블을 여러번 복사해서 조인하는 방식입니다.

• 예시 코드

  SELECT A.column1, B.column2
  FROM TableA A
  INNER JOIN TableA B
  ON A.common_column = B.common_column;

⚙️ 어떻게 동작하는걸까?

JOIN이 데이터베이스 엔진 내부에서 어떻게 작동하나요?

NL(Nested-Loop) Join

하나의 조인 테이블을 외부 입력 테이블로 사용하고 다른 테이블을 내부 입력 테이블로 사용합니다. Nested Loop Join은 외부 테이블에서 행을 가져와서 내부 테이블에서 해당 행을 검색하며, 이 프로세스는 외부 테이블의 모든 출력 행이 내부 테이블에서 검색될 때까지 계속됩니다.

의사코드

For each tuple r in R do
	For each tuple s in S do
		If r and s satisfy the join condition
			Then output the tuple <r, s>

1. 선행 테이블에서 만족하는 첫 번째 행을 스캔
2. 선행 테이블의 조인 키를 가지고 후행 테이블에 키가 존재하는지 스캔
3. 후행 테이블의 인덱스에 선행 테이블의 조인 키가 존재하는지 확인
   1. 2번에서 데이터가 나오지 않는다면 그냥 선행 테이블만 필터링 진행
4. 인덱스에서 추출한 레코드 식별자를 이용하여 후행 테이블에 엑세스
   1. 조건을 따져서 필터링

코드예시

SELECT   /*+ USE_NL (B) */
        A.*
      , B.*
FROM    ITEM A, UITEM B
WHERE A.ITEM_ID=B.ITEM_ID --- 1
AND A.ITEM_TYPE_CD = '100100' --- 2
AND A.SALE_YN = 'Y'  --- 3
AND B.SALE_YN = 'Y'  --- 4

동작순서 : 2 -> 3 -> 1 -> 4

• 절차적이며, 프로그래밍에서 FOR, WHILE문 과 같은 구조로 수행된다.

• 선행테이블은 풀스캔하므로, 선행테이블의 크기가 작을수록 유리하다

  (So. 두 테이블의 크기 차이가 있는 경우, 유리하게 사용될 수 있는 방법임)

• 후행테이블에 대해서는 반드시 인덱스가 존재해야 NL 조인이 가능하다.

• 인덱스 구성 전략이 특히 중요하다. 조인 컬럼에 대한 인덱스가 있느냐 없느냐, 있다면 컬럼이 어떻게 구성됐느냐에 따라 조인 효율이 크게 달라진다.

• 랜덤 액세스 방식으로 데이터를 읽는다.-> 처리 범위가 좁은 것이 유리하다.

이런 여러가지 특징을 종합할 때, NL 조인은 소량의 데이터를 주로 처리하거나 부분범위처리가 가능한 온라인 트랜잭션 환경에 적합한 조인 방식이라고 할 수 있다.

Sort Merge Join

쉽게 이야기해서 선행 테이블에서 자료를 찾고 선행 테이블 자료를 기준으로 후행 테이블에서 sort를 하는 것

일반적으로 대량의 조인 작업에서 정렬 작업을 필요로 하는 Sort Merge Join 보다는 CPU 작업 위주로 처리하는 Hash Join이 성능상 유리합니다. 그러나 Sort Merge Join은 Hash Join 과는 달리 동등 조인뿐만 아니라 비동등 조인에 대해서도 조인 작업이 가능하다는 장점이 있습니다.

→ sort를 하기 때문에 부등호 연산도 가능함

1. 선행 테이블에서 주어진 조건을 만족하는 행을 찾음
2. 선행 테이블의 조인 키를 기준으로 정렬작업을 수행

• 1 ~ 2번 작업을 선행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행

1. 후행 테이블에서 주어진 조건을 만족하는 행을 찾음
2. 후행 테이블의 조인 키를 기준으로 정렬 작업을 수행

• 3 ~ 4번 작업을 후행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행

1. 정렬된 결과를 이용하여 조인을 수행하며 조인에 성공하면 추출버퍼에 넣음

코드예시

SELECT  /*+ ORDERED USE_MERGE(B) */
        A.*
      , B.*
FROM  ITEM A, UITEM B
WHERE A.ITEM_ID=B.ITEM_ID   -- 1
AND A.ITEM_TYPE_CD = '100101'   -- 2
AND A.SALE_YN = 'Y'  -- 3
AND B.SALE_YN = 'Y'  -- 4

동작 순서 2 -> 3 -> 4 -> 1

• 인덱스가 없어도 가능한 조인법
• 대부분 해시조인인 보다 느린 성능을 보이나, 아래와 같은 상황에서는 소트머지 조인이 유용하다.
  • 선행 테이블에 소트연산을 대체할 인덱스가 있을 때
  • 조인할 선행 집합이 이미 정렬되어 있을 때
  • 조인 조건식이 등차(=)조건이 아닐 때
  • 두 테이블의 사이즈가 비슷한경우에 유리하며, 사이즈 차이가 큰 경우에는 불리하고, 비효율적인 방법이다.

Hash Join

조인될 두 테이블 중 하나를 해시 테이블로 선정하여 조인될 테이블의 조인 키 값을 Hash 알고리즘으로 비교하여 매치되는 결과값을 얻는 방식.

1. 선행 테이블에서 주어진 조건을 만족하는 행을 찾음
2. 선행 테이블의 조인 키를 기준으로 해시함수를 적용하여 해시 테이블을 생성 -> 조인 컬럼과 SELECT 절에서 필요로 하는 컬럼도 함께 저장
   • 1 ~ 2번 작업을 선행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행
3. 후행 테이블에서 주어진 조건을 만족하는 행을 찾음
4. 후행 테이블의 조인 키를 기준으로 해시 함수를 적용하여 해당 버킷을 찾음 -> 조인 키를 이용해서 실제 조인될 데이터를 찾음
5. 조인에 성공하면 추출버퍼에 넣음
   • 3 ~ 5번 작업을 후행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행

특징

1. 조인 칼럼의 인덱스를 사용하지 않기 때문에 조인 칼럼의 인덱스가 존재하지 않을 경우데도 사용할 수 있는 기법이다.
2. 메모리 사용이 큰 대용량 테이블 조인시 메모리 외에 임시영역(PGA 메모리)까지 사용하여 저장할 수 있어 유리함
3. 해쉬 함수를 이용하여 조인을 수행하기 때문에 '='로 수행하는 조인에서만 사용가능 합니다.

📝 정리하면..

	Nested-Loop Join	Hash Join	Sorted-Merge Join
강점	직관적이고 구현이 간단	대규모 데이터셋을 조인할 때 효율적	매우 빠르고 효율적 등치 조인과 비등치 조인 모두에 사용할 수 있다.
단점	시간 복잡도: O(N * M) ( N과 M: 각각 테이블 A와 B의 행 수). 따라서, 두 테이블의 행 수가 많아질수록 성능이 급격히 저하될 수 있습니다.	비등치 조인(≥, >, <, ≤, ≠)에 부적합 메모리 사용	두 테이블 모두 정렬되어 있어야 한다 정렬 단계의 비용이 크다. 메모리 사용량이 많을 수 있다.
최적화	1. 인덱스 생성 2. 작은 테이블을 외부 루프, 큰 테이블을 내부 루프로 3. 조인 힌트 사용: MySQL에서는 조인 힌트를 사용하여 옵티마이저에게 특정 조인 전략을 사용하도록 지시할 수 있습니다.	1. 작은 테이블을 해시 테이블로 선택 2. 파티셔닝: 큰 테이블을 파티셔닝하여, 각 파티션을 독립적으로 해시 조인하면 메모리 사용을 줄일 수 있습니다. 3. 충돌이 적은 해시 함수를 선택	1. (조인 키에 대해) 인덱스 사용 2. 파티셔닝: 데이터를 파티셔닝하여 정렬 및 병합 단계를 병렬로 처리할 수 있습니다.

🚗 JOIN 성능 최적화하기