데이터베이스 조인

조인 기준에 따른 종류

이너 조인

일치하는 조인 조건이 있는 데이터만 출력

• 이너 조인은 일치하는 조건이 있어야 출력되기 때문에 아우터 조인처럼 레프트, 라이트 개념이 없다.
• 데이터베이스 이론에서 "세타 조인" 및"동등 조인"을 포함하는 실무적 개념이다.
  • 세타 조인: 조건에 맞는 데이터만 출력 (보다 크다, 보다 작다 등)
  • 동등 조인: 값이 일치하는 조건의 데이터만 출력 ( = )
  • 또 하나 이너 조인의 종류 중 하나인 "자연 조인"은 그냥 사용하는 방식의 차이이고 잘 쓰이지 않으니 참고만 하자. 자연 조인이 아닌 이너 조인이 우리가 일반적으로, 가장 흔히 사용하는 조인 방식이다.

(참고) 자연 조인

• 두 테이블에서 공통된 속성(열, attribute)을 기준으로 자동으로 데이터를 결합하는 조인이다. 잘 사용되지 않는다. 하지만 의외로 대부분의 DBMS에서 지원은 된다.

아우터 조인

한쪽에만 조인 조건이 있더라도 데이터 출력, 데이터가 없는 릴레이션 부분은 null 처리됨

Left Join (Left Outer Join)

• 왼쪽 테이블의 모든 행을 반환하고, 오른쪽 테이블과 일치하는 값이 없으면 NULL로 채운다.
• 여기서 왼쪽 테이블이라고 하면 먼저 정의한 테이블(아래의 경우 Employees)을 말한다.

SELECT * FROM Employees e
LEFT JOIN Departments d ON e.department_id = d.id;

Right Join (Right Outer Join)

• 오른쪽 테이블의 모든 행을 반환하고, 왼쪽 테이블과 일치하는 값이 없으면 NULL로 채운다.
• 테이블 순서만 바꿔 적으로 Left와 완전 동일한 결과를 출력한다. 따라서 Left로 통일해서 쓰고 Right는 잘 쓰지 않는 경우가 많다.

SELECT * FROM Employees e
RIGHT JOIN Departments d ON e.department_id = d.id;

Full Join (Full Outer Join)

• 두 테이블 모두에서 일치하는 행은 결합하고, 일치하지 않는 행은 NULL로 채워진다.
• 양쪽 테이블의 모든 데이터가 반환되므로 실무적으로 사용할 일은 많지 않다.

SELECT * FROM Employees e
FULL JOIN Departments d ON e.department_id = d.id;

조인 방식에 따른 종류

NESTED LOOPS 조인

조인 대상 테이블의 인덱스가 있는 경우 사용

• Loop의 기준이 되는 드라이빙/드리븐 테이블 선정
• 드라이빙 테이블 기준으로 반복해서 읽으며 인덱스를 이용한 조인 수행

최적화 방안

• 드라이빙 테이블 선정
• 조인 순서 조정
• 적절한 인덱스 설계

SORT-MERGE 조인

조인 대상 테이블에 인덱스가 없는 경우 사용

• 각 테이블의 데이터를 동시에 읽어 들임
• 각 테이블 데이터를 연결 칼럼을 기준으로 정렬
• 정렬이 모두 끝난 후 조인 수행

장점

• 인덱스 불필요
• 조인 연산자가 '='이 아닌 경우 nested loop 조인보다 유리한 경우가 많음

단점

• 정렬 작업으로 인한 메모리 오버헤드가 큼
• 조인 대상 데이터 간 크기의 차이가 큰 경우, 한쪽 정렬이 먼저 완료되어 대기하므로 비효율 발생

최적화 방안

• 데이터를 빨리 읽어들여서 정렬할 수 있도록 메모리(SORT_AREA_SIZE) 최적화

HASH 조인

NESTED LOOPS나 SORT-MERGE 사용이 불리한 경우 사용

• 드라이빙 테이블 선정
• 드라이빙 테이블을 우선적으로 해시 생성
• 만들어지는 해시를 기준으로 조인 수행

장점

• 메모리 성능이 좋은 경우 최적의 성능 발휘

단점

• 메모리의 과다한 사용, 메모리 부족시 속도가 현저히 느려짐

최적화 방안

• 데이터를 빨리 읽어들여서 해실할 수 있도록 메모리(HASH_AREA_SIZE) 최적화

조인 방식 비교

구분	NESTED LOOPS 조인	SORT-MERGE 조인	HASH 조인
동작	선행(드라이빙) 테이블에서 조건을 만족하는 행마다 후행 테이블을 반복 탐색한다	두 테이블을 조인 컬럼 순으로 정렬한 뒤 차례로 병합한다	작은 쪽으로 해시 테이블을 만들고(빌드) 큰 쪽 행으로 탐색한다(프로브)
인덱스	후행 테이블 조인 컬럼에 인덱스가 있어야 좋다. 없으면 선행 행마다 후행 테이블 전체 스캔이 반복된다	필요 없다. 이미 정렬되어 있거나 인덱스로 정렬 순서를 얻을 수 있으면 정렬 단계를 생략한다	필요 없다
조인 조건	모든 조건(동등, 부등호)	동등과 부등호(<, <=, >, >=). 교과서의 병합 조인은 동등 조인만 다룬다	동등 조인만
잘 맞는 경우	결과가 적은 온라인 처리, 부분 범위 처리	조인 컬럼 인덱스가 없고 대량이며 정렬된 결과가 필요할 때	대량 데이터 조인. 일반적으로 다른 둘보다 효율적이다
메모리	적게 쓴다	정렬 영역을 쓴다	해시 테이블이 메모리에 다 들어가지 않으면 임시 영역(디스크)에 일부를 내려 파티션 단위로 처리한다

Oracle 문서는 조인 조건이 부등호이면 해시 조인을 쓸 수 없어 정렬 병합 조인을 고르며, 인덱스가 있으면 첫 집합의 정렬을 피할 수 있고, 해시 테이블이 PGA 에 다 들어가지 않으면 임시 공간에 파티션을 내려 처리한다고 설명한다. Silberschatz 도 해시 조인과 병합 조인을 동등 조인·자연 조인용 알고리즘으로 둔다.

• "후행 테이블 인덱스가 없으면 전체 스캔이 반복된다"는 NESTED LOOPS 의 성질이다. SORT-MERGE 의 설명이 아니다.
• "인덱스가 없거나 부등호 조건이어도 쓰고, 이미 정렬되어 있으면 정렬을 생략한다"는 SORT-MERGE 의 성질이다. NESTED LOOPS 의 설명이 아니다.

이원 조인 구현 방법

Elmasri·Navathe 는 R ⋈_A=B S 를 구현하는 방법을 넷으로 나눈다.

• 중첩 루프 조인 - R 의 각 레코드마다 S 의 모든 레코드를 검사한다. 아무 전제 조건이 없다
• 인덱스 검사(단일 루프) 조인 - 한쪽 조인 속성에 인덱스나 해시 키가 있으면, 다른 쪽 레코드마다 그 인덱스로 짝을 바로 찾는다
• 정렬 합병 조인 - R 과 S 가 조인 속성 순으로 정렬되어 있을 때 두 파일을 한 번씩만 차례로 훑으며 같은 값을 맞춘다. 정렬되어 있지 않으면 먼저 외부 정렬을 해야 이 방법을 쓸 수 있다
• 해시 조인 - 두 파일을 같은 해시 함수로 분할한 뒤 같은 분할끼리 짝을 맞춘다. 정렬이 필요 없다

두 릴레이션이 조인 속성으로 정렬되어 있어야만 적용할 수 있는 방법은 정렬 합병 조인이다.

분할 해시 조인(partition-hash join)은 해시 조인의 다른 이름이다. 두 릴레이션을 같은 해시 함수로 여러 분할(partition)로 나눈 뒤 같은 번호의 분할끼리 조인하므로 이렇게 부른다. 따라서 관계 DBMS 가 조인 연산을 구현하는 방법은 중첩 루프 조인, 인덱스 조인, 정렬-합병 조인, (분할) 해시 조인이다.

k-최근접 이웃 조인(k-nearest neighbor join, kNN join)은 이와 성격이 다르다. 한 점 집합 R 의 각 점마다 다른 집합 S 에서 가장 가까운 k 개 점을 짝지어 돌려주는 유사도 조인 질의 연산으로, 벡터·공간 데이터와 데이터 마이닝에서 쓰인다. 결과가 R 쪽 점마다 k 개로 정해지고 R ⋈ S 와 S ⋈ R 이 다른 비대칭 연산이다. 동등 조인 같은 관계 조인을 처리하는 구현 방법이 아니다 → K-최근접 이웃

같이 보기

• 데이터베이스 인덱스
• 외부 조인
• 세미 조인