[MSSQL] JOIN의 종류 (논리적, 물리적)

 

조인의 정의와 논리적 조인(INNER, OUTER 등), 물리적 조인(Nested Loop Join, Merge Join 등) 에 대해 자세히 알아보자.

 

 

조인이란?

---

관계가 있는 두 개 이상의 테이블을 주어진 조건으로 결합하여 하나의 결과 집합으로 출력하는 기능.

-- 기본 구문
SELECT * FROM 테이블명 A
JOIN 테이블명 B
ON 조인 조건

 

 

 

논리적 조인 (INNER, OUTER, CROSS, APPLY)

---

 

1. INNER JOIN

 

교집합되는 (조인 조건을 모두 만족하는) 데이터만 결과로 출력하는 조인.

 

https://youtu.be/5AJA0gxU4qs?si=YrUpqxXgc0Jzw5zd 참고

SELECT e.Name, d.Dept_no, d.Dept_Name
FROM Employee e INNER JOIN Department d
ON e.Dept_No = d.Dept_No

 

A와 B에서 교집합이 되는 부분이 최종 INNER JOIN의 결과로 조회된다.

 

Employee 테이블에 있는 Dept_No 컬럼 값 [10, 20, 30] 과

동일한 값을 가진 Department 테이블의 값만 조회된다.

 

 

 

 

 

2. OUTER JOIN

 

기준이 되는 테이블의 조인 실패 행까지 모두 조회하는 조인으로, LEFT, RIGHT, FULL 3가지 형태가 있다.

 

1) LEFT OUTER JOIN

LEFT OUTER JOIN

SELECT e.Name, d.Dept_No, d.Dept_Name
FROM Employee e LEFT OUTER JOIN Department d
ON e.Dept_No = d.Dept_No

 

 

LEFT OUTER JOIN은 테이블의 기준이 왼쪽이 된다.

A와 B의 교집합이 되는 2번 구간과, 기준 테이블인 A의 나머지 1번 데이터가 함께 조인된다.

 

조회 결과에서 Employee 테이블 James의 Dept_No = NULL 은 Department 테이블과 일치하는게 없어서

최종적으로 결과 테이블에서 James는 Dept_No와 Dept_Name이 NULL로 조회된다.

 

 

 

2) RIGHT OUTER JOIN

RIGHT JOIN

SELECT e.Name, d.Dept_No, d.Dept_Name
FROM Employee e RIGHT OUTER JOIN Department d
ON e.Dept_No = d.Dept_No

 

RIGHT OUTER JOIN은 LEFT OUTER JOIN과 반대로 기준 테이블이 오른쪽이 된다.

A와 B의 교집합 데이터인 2번 구간과, 기준 테이블인 B의 나머지 1번 데이터가 조인된다.

 

조회 결과에서 Employee 테이블의 James는 Dept_No가 Department 테이블에 겹치는게 없어서 결과에서는 빠지게 된다.

그리고 Employee 테이블에는 Dept_No [40, 50]이 없지만, 기준 테이블에 있기에 조회가 된다.

 

 

 

 

3) FULL OUTER JOIN

FULL OUTER JOIN

 

FULL OUTER JOIN은 양쪽 테이블이 모두 기준 테이블이 된다.

A와 B의 교집합 테이터인 2번 구간과, 기준 테이블인 A,B의 나머지 1번 데이터가 조인된다.

 

조회 결과에서 A 테이블에는 있고 B테이블엔 없는 James 행과, 

A테이블에는 없고 B테이블에 있는 Dept_Name [Design, Marketing]이 모두 조회된다.

 

 

 

 

 

3. CROSS JOIN

CROSS JOIN은 조인 조건 없이 두 테이블에 대한 모든 행을 조인하는 기능.

(A 테이블 행 수) x (B 테이블 행 수) = 조회 행 수

 

CROSS JOIN

 

 

모든 행을 JOIN하면 35(7*5)행이 출력되기 때문에 편의상 WHERE 절에 조건을 명시했다.

결과적으로 9(3*3)행이 조회된다.

 

 

 

 

4. APPLY

 

APPLY는 일반적인 조인과 유사하지만, 조인하는 과정에서 인라인 뷰, 테이블 반환 함수의 매개변수로 값을 전달할 수 있다는 것에 차이가 있다.

 

결과를 출력하는 방식에서  CROSS APPLY와 OUTER APPLY로 나누어진다.

 

1) CROSS APPLY

CROSS APPLY

 

SELECT d.Dept_Name, d.Dept_No, e.Name
FROM Department d CROSS APPLY
	(SELECT TOP 1 Name 
	  FROM Employee e
      WHERE e.Dept_No = d.Dept_No
      ORDER BY Emp_No DESC) e

 

Department 테이블과 Employee 테이블이 재부 조인한 결과, 집합에서 Emp_No 열을 기준으로 가장 높은 1건씩만 출력한다. INNER JOIN처럼 조인에 성공하는 행만 출력하지만, Department 테이블의 Dept_No 값을 인라인 뷰(서브쿼리) 안쪽으로 전달할 수 있다는 것에서 차이가 있다.

 

 

 

2) OUTER APPLY

OUTER APPLY

 

 

SELECT d.Dept_Name, d.Dept_No, e.Name
FROM Department d OUTER APPLY (SELECT TOP 1 Name
				FROM Employee e
                                WHERE e.Dept_No = d.Dept_No
                                ORDER BY Emp_No DESC) e

 

 

CROSS APPLY가  INNER JOIN에 해당한다면,

OUTER APPLY는 LEFT OUTER JOIN에 해당한다.

 

조인에 실패하는 행도 NULL 값이 입력되어 함께 조회된다.

 

 

 

 

 

 

물리적 조인 (Nested Loop Join, Merge Join, Hash Join)

---

1. Nested Loop Join

Nested Loop Join (NL Join)은 중첩된 반복 조인으로, 선행 테이블의 결과 집합을 한 건 씩 후행 테이블에 조인하고 이를 반복하여 최종 결과 집합을 만들어낸다.

이 방식은 주로 작은 데이터셋에서 효율적이고, 한쪽 입력 테이블이 적고 다른 한쪽이 적절히 인덱싱되어 있을 떄 주로 사용한다.

 

https://youtu.be/5AJA0gxU4qs?si=LveGSmF-CUv4gMnc 참고

 

 

CREATE NONCLUSTERED INDEX IX_DEPTNO ON Dept(Deptno)
CREATE NONCLUSTERED INDEX IX_EMP ON Emp(Deptno)

SELECT e.EName, e.Sal, d.DName
FROM Dept d INNER LOOP JOIN Emp e
ON d.Deptno = e.Deptno
WHERE d.Deptno = 20 AND e.Sal > 2000

 

 

(1) [Dept] 테이블의 [Deptno] = 20 조건에 만족하는 데이터를 [IX_DEPTNO] 인덱스로 범위 탐색한다.

(2) [IX_DEPTNO] 인덱스로부터 탐색한 행의 RID 값으로 [Dept] 테이블에 RID Lookup을 수행한다.

(3) 조인 조건인 [Deptno] 열을 기준으로 [Emp] 테이블의 [IX_EMP] 인덱스와 조인한다.

(4) 조인된 결과에서 [IX_EMP]의 RID 값으로 [Emp] 테이블에 RID Lookup을 수행한다.

(5) 조인된 결과 집합에서 [Emp] 테이블의 조건인 Sal>2000을 만족하는 데이터만 운반 단위에 전달한다.

(6) (1)에서 탐색한 행 수만큼 (2) ~ (5) 과정을 반복하여 최종 결과 집합을 출력한다.

 

 

* RID Lookup

 일반적으로 비클러스터드 인덱스를 통해 검색된 데이터에서 실제 행 데이터를 찾기 위해 사용되며, Row Identifier (RID)를 기반으로 데이터를 검색한다.

비클러스터드 인덱스 검색 -> 결과로 RID 반환 -> Heap 테이블에서 RID 찾기(데이터 랜덤 접근인 Random I/O로 조회) 

 

 

 

 

2. Merge Join

두 테이블의 처리 범위 내에서 조인 키를 기준으로 정렬한 다음 병합하여 조인 작업을 수항해는 방식이다.

NL Join은 후행 테이블을 반복 스캔하는 반면, Merge Join은 한 번만 스캔하고 조인을 수행하기 때문에, 인덱스로 정렬을 생략할 수 있는 상황에서 데이터가 크다면 성능적인 이점을 가질 수 있다.

 

 

SELECT e.EName, e.Sal, d.DName
FROM Dept d INNER LOOP JOIN Emp e
ON d.Deptno = e.Deptno
WHERE d.Deptno = 20 AND e.Sal > 2000

 

(1) [Dept] 테이블의 [Deptno] = 20 조건에 만족하는 데이터를 찾고 조인 조건인 [Deptno] 열로 정렬을 수행한다.

(2) [Emp] 테이블에서도 Sal>2000 조건에 만족하는 데이터를 찾고 [Deptno] 열을 기준으로 정렬을 수행한다.

(3) [Deptno]로 정렬된 데이터를 기준으로 마지막 값을 만날 때까지 스캔하면서 최종 결과 집합을 출력한다.

 

* 1:N Merge Join
한쪽 테이블의 하나의 행이 다른 테이블의 여러 행과 연결되는 조인으로, 양쪽 데이터 집합이 정렬된 상태로 병렬 읽기를 시작하다가 매칭이 완료되면 왼쪽(기준) 테이블의 포인터를 다음 행으로 이동하고 다시 비교를 진행함.

* N:M Merge Join
두 테이블의 하나의 행이 다른 테이블의 여러 행과 연결될 수 있는 조인이다.
두 테이블이 정렬된 상태에서 병렬로 읽다가 조인 조건을 만족하는 행을 모두 반환한다.
값을 비교할 때, 후행 테이블의 값을 WorkTable에 저장하고 재사용하는 방식으로 수행된다.
두 테이블에서 N개의 행이 매칭될 경우 조합이 증가하여 처리해야 할 데이터 양이 많아진다.

 

 

 

 

3. Hash Join

해시 함수를 적용하여 조인하는 방식으로, 선행으로 읽어내는 테이블을 빌드 입력(Build Input), 후행으로 읽는 테이블을 프로브 입력(Probe Input)이라거 한다.

빌드 입력에서는 해시 테이블을 생성하고, 프로브 입력에서는 값을 해시 함수로 적용하여 해시 테이블에 조인을 수행한다.

 

CREATE NONCLUSTERED INDEX IX_DEPTNO ON DEPT(DEPTNO)

SELECT e.EName, e.Sal, d.DName
FROM Dept d INNER LOOP JOIN Emp e
ON d.Deptno = e.Deptno
WHERE d.Deptno = 20 AND e.Sal > 2000

 

(1) [Dept] 테이블에서 [Deptno]>20 조건에 해당하는 행을 {IX_DEPTNO] 인덱스로 탐색한 후 해시 함수를 적용하여 해시 테이블을 생성한다.

(2) [Emp] 테이블에서 [Sal]>2000 조건에 해당하는 행을 해시 함수를 적용하면서 해시 테이블에 조인한다.

(3) 조인에 성공한 행은 최종 결과 집합에 포함하며, [Emp] 테이블의 행이 모두 비교될 때까지 (2)를 반복한다.