[MySQL] JOIN

JOIN

join이란?

• 두 개 이상의 테이블을 연결하여 데이터를 검색하는 것을 말한다.
• FROM절에 둘 이상의 여러 테이블이 나열되어 있어도 내부적으로는 두 테이블씩 조인을 수행한다.
• 이 때 테이블 조인 순서는 Optimizer에 의해 결정되기 때문에 중요 튜닝 포인트이다.

EQUI JOIN

설명

• 두 테이블 간의 칼럼값(도메인값)이 정확하게 일치하는 데이터를 조합하여 검색

INNER JOIN 예시

mysql> SELECT *
    -> FROM emp
    -> INNER JOIN dept
    -> ON emp.deptSeq = dept.deptseq;
+---------+--------+-------------+--------+---------------+---------+----------+
| deptSeq | empSeq | empName     | salary | managerEmpSeq | deptSeq | deptName |
+---------+--------+-------------+--------+---------------+---------+----------+
|       1 |      1 | developer-1 |    100 |             2 |       1 | develop  |
|       1 |      2 | developer-2 |    200 |          NULL |       1 | develop  |
|       2 |      3 | designer-1  |    150 |          NULL |       2 | design   |
+---------+--------+-------------+--------+---------------+---------+----------+

NATURAL JOIN 예시

• 두 테이블 간의 동일한 이름과 타입을 갖는 모든 칼럼에 대해 join을 수행.

mysql> SELECT *
    -> FROM emp
    -> NATURAL JOIN dept;
+---------+--------+-------------+--------+---------------+----------+
| deptSeq | empSeq | empName     | salary | managerEmpSeq | deptName |
+---------+--------+-------------+--------+---------------+----------+
|       1 |      1 | developer-1 |    100 |             2 | develop  |
|       1 |      2 | developer-2 |    200 |          NULL | develop  |
|       2 |      3 | designer-1  |    150 |          NULL | design   |
+---------+--------+-------------+--------+---------------+----------+

NON-EQUI JOIN

설명

• 두 테이블 간의 컬럼값 범위로 일치하는 경우 사용하는 방법.
• BETWEEN, >=, <= 등을 사용하는 JOIN

예시

mysql> SELECT emp.empName, emp.salary, grade.grade
    -> FROM emp
    -> INNER JOIN grade
    -> ON emp.salary BETWEEN grade.minSalary AND grade.maxSalary;
+-------------+--------+-------+
| empName     | salary | grade |
+-------------+--------+-------+
| developer-1 |    100 | B     |
| developer-2 |    200 | A     |
| designer-1  |    150 | A     |
+-------------+--------+-------+

CROSS JOIN

설명

• 생길 수 있는 모든 데이터의 조합으로 join을 수행.
• ON 조건을 명시하지 않는다.

예시

mysql> SELECT *
    -> FROM emp
    -> INNER JOIN dept;
+---------+--------+-------------+--------+---------------+---------+----------+
| deptSeq | empSeq | empName     | salary | managerEmpSeq | deptSeq | deptName |
+---------+--------+-------------+--------+---------------+---------+----------+
|       1 |      1 | developer-1 |    100 |             2 |       1 | develop  |
|       1 |      2 | developer-2 |    200 |          NULL |       1 | develop  |
|       2 |      3 | designer-1  |    150 |          NULL |       1 | develop  |
|       1 |      1 | developer-1 |    100 |             2 |       2 | design   |
|       1 |      2 | developer-2 |    200 |          NULL |       2 | design   |
|       2 |      3 | designer-1  |    150 |          NULL |       2 | design   |
+---------+--------+-------------+--------+---------------+---------+----------+

SELF JOIN

설명

• 하나의 테이블에서 두 개의 컬럼이 연관 관계를 가지고 있을 경우 사용하는 방법.
• 하나의 테이블을 논리적으로 분리시켜 JOIN하는 방법.

예시

mysql> SELECT e1.empName AS 'senior', e2.empName AS 'junior'
    -> FROM emp e1
    -> INNER JOIN emp e2
    -> ON e1.empSeq = e2.managerEmpSeq;
+-------------+-------------+
| senior      | junior      |
+-------------+-------------+
| developer-2 | developer-1 |
+-------------+-------------+

NL(Nested Loops) Join

설명

• 선행 테이블(Outer Table, Driving Table)에서 조건에 만족하는 레코드를 검색한 후, 후행 테이블(Inner Table, Driven Table)에서 결합 조건에 일치하는 레코드를 찾아 조인하는 방식.
• 중첩 반복문과 유사한 방식으로 조인을 수행하는 기법
• Random Access 방식으로 데이터를 읽는다.
• 결과 행의 수가 적은 테이블을 선행 테이블로 사용하는 것이 좋다.
• 조인 조건에 해당하는 컬럼들은 인덱스를 가지고 있어야 한다.

수행 과정

• 선행 테이블에서 조건을 만족하는 첫 번째 행을 찾는다.
• 선행 테이블의 조인 키와 일치하는 후행 테이블의 키가 있는지 확인한다.
• 있으면 후행 테이블에 접근해 일치하는 정보를 조인하여 추출 버퍼에 넣는다.
• 위 과정 반복한다.

Sort Merge Join

설명

• 선행 테이블과 후행 테이블 모두 조인 컬럼을 기준으로 데이터를 정렬한 후 조인을 수행하는 기법이다.
• Full Table Scan 방식으로 데이터를 읽는다.
• Join 칼럼의 인덱스가 존재하지 않을 경우에도 사용 가능하다.
• EQUI JOIN 뿐만 아니라 NON-EQUI JOIN에 대해서도 조인 작업이 가능하다.
• 정렬할 데이터가 많을 경우 메모리에서 모든 작업을 수행하기 어려워 디스크를 사용할 수 있다. (성능 저하 발생할 수 있음)

수행 과정

• 선행 테이블에서 조건을 만족하는 행을 찾는다.
• 해당 행들을 조인 키 기준으로 정렬한다.
• 후행 테이블에서 조건을 만족하는 행을 찾는다.
• 해당 행들을 조인 키 기준으로 정렬한다.
• 두 데이터를 JOIN하여 추출 버퍼에 넣는다.

Hash Join

설명

• 조인 칼럼을 기준으로 Hash 함수를 적용하여 동일한 값이 존재할 때 JOIN을 수행하는 기법이다.
• Hash 함수를 사용하여 일치하는 데이터를 찾는 방식이기 때문에 EQUI JOIN에서만 사용할 수 있다.
• 선행 테이블 기준으로 Hash Table이 만들어지기 때문에 결과 행의 수가 적은 테이블을 선행 테이블로 사용하는 것이 좋다.
• Hash Table의 크기가 메모리보다 클 경우 디스크를 사용할 수 있다. (성능 저하 발생할 수 있음)

수행 과정

• 선행 테이블에서 조건을 만족하는 행을 찾는다.
• 해당 행들을 조인 키 기준으로 Hash 함수를 적용하여 해쉬 테이블을 생성한다.
• 후행 테이블에서 조건을 만족하는 행을 찾는다.
• 해당 행들을 조인 키 기준으로 Hash 함수를 적용하여 해쉬 테이블에 데이터가 있는지 확인한다.
• 데이터가 존재하면 JOIN하고 추출 버퍼에 넣는다.

참고

• https://hoon93.tistory.com/27
• https://blog.naver.com/agopwns/221155943203
• https://hoon93.tistory.com/46
• https://mozi.tistory.com/168
• https://hoing.io/archives/14457