[데이터베이스] 조인(join)

이번 시간에는 join에 대해 공부해 보겠습니다. 

데이터베이스에는 여러개의 테이블이 존재합니다. 많은 테이블에서 내가 원하는 정보를 검색할 수 있는 방법이 join인데요, 예를 들어서 사원번호가 100인 사원에 대해 [사원] 테이블에서 이름, 나이, 주소가 필요하고 [봉급] 테이블에서 봉급에 대한 정보가 필요한 경우 [사원], [봉급] 테이블에서 사원번호가 100인 튜플에서 정보를 가져와야 할 것입니다. 이렇게 join을 사용하면 여러 테이블에 걸쳐 존재하는 데이터를 가져올 수 있습니다.

 

사실 아래의 사진을 모두 이해할 수 있으면 이 긁을 읽을 필요는 없습니다.

 

설명을 쉽게하기 위해 a속성을 가지는 A테이블, b속성을 가지는 B테이블을 만들었습니다. 

두 테이블의 공통된 데이터는 [3,4,5]이며, A테이블에만 있는 데이터는 [1,2], B테이블에만 있는 데이터는 [6,7]입니다.

 

---

inner join

A테이블 데이터 ∩ B테이블 데이터

 

두 테이블에 존재하는 데이터 중에 공통된 데이터만 추출합니다. inner join에서 inner를 삭제해도 유효하며 join을 사용하지 않고도 사용할 수 있습니다.

mysql> select * from A inner join B on A.a = B.b;      // 명시적 표현
mysql> select * from A join B on A.a = B.b;            // 명시적 표현
mysql> select * from A,B where A.a = B.b;              // 함축적 표현
+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
+------+------+

---

outer join

A테이블의 데이터 ∪ (A테이블 데이터 ∩ B테이블 데이터)

 

inner join의 경우 무조건 두 테이블의 공통된 값만 가져옵니다. inner join는  A[1,2,3,4,5], B[3,4,5,6,7]에서 무조건 공통된 [3,4,5]만 가져옵니다. 하지만 outer join을 사용하면 A,B 공통된 값 뿐만 아니라 A데이터 모두 가져올 수 있으며 left outer join과 right outer join이 있습니다.

 

실제 질의를 작성할 때 outer를 삭제할 수 있습니다. 

left outer join ㅡ> left join

right outer join ㅡ> right join

 

1. left outer join 

left join도 유효하며 왼쪽 테이블을 기준으로 오른쪽 테이블을 join 합니다.

mysql> select * from A left outer join B on A.a = B.b;
mysql> select * from A left join B on A.a = B.b;
+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
|    1 | NULL |
|    2 | NULL |
+------+------+

 

2. right outer join

right join도 유효하며 오른쪽 테이블을 기준으로 왼쪽 테이블을 join 합니다.

mysql> select * from A right outer join B on A.a = B.b;
mysql> select * from A right join B on A.a = B.b;
+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
| NULL |    6 |
| NULL |    7 |
+------+------+

 

3. outer join에서 공통데이터 제거(차집합)

다음과 같이 outer join의 결과값에서 공통데이터를 제거할 수도 있습니다.

 

mysql> select * from A left join B on A.a = B.b where B.b is null;
+------+------+
| a    | b    |
+------+------+
|    1 | NULL |
|    2 | NULL |
+------+------+

mysql> select * from A right join B on A.a = B.b where A.a is null;
+------+------+
| a    | b    |
+------+------+
| NULL |    6 |
| NULL |    7 |
+------+------+

 

4. A,B 모든 데이터 추출(합집합)

다음과 같이 A,B 데이터를 한번에 모두 추출할 수 있습니다. 단 mysl의 경우 full outer join을 사용할 수 있기 때문에 다른 방법을 사용해야 합니다. 

 

2번째 방법이 mysql의 경우이며 A테이블의 데이터 + A,B중복을 제거한 right outer join을 사용해야 합니다.

mysql> select * from A FULL OUTER JOIN B on A.a = B.b;
mysql> select * from A left outer join B on A.a = B.b union select * from A right outer join B on A.a = B.b where A.a is null;
+------+------+
| a    | b    |
+------+------+
|    3 |    3 |
|    4 |    4 |
|    5 |    5 |
|    1 | NULL |
|    2 | NULL |
| NULL |    6 |
| NULL |    7 |
+------+------+

---

cross join

경우의 수를 모두 표현한 방법입니다. 결과값은 N x M 개가 나옵니다.

mysql> select * from A cross join B;
+------+------+
| a    | b    |
+------+------+
|    1 |    3 |
|    2 |    3 |
|    3 |    3 |
|    4 |    3 |
|    5 |    3 |
|    1 |    4 |
|    2 |    4 |
|    3 |    4 |
|    4 |    4 |
|    5 |    4 |
|    1 |    5 |
|    2 |    5 |
|    3 |    5 |
|    4 |    5 |
|    5 |    5 |
|    1 |    6 |
|    2 |    6 |
|    3 |    6 |
|    4 |    6 |
|    5 |    6 |
|    1 |    7 |
|    2 |    7 |
|    3 |    7 |
|    4 |    7 |
|    5 |    7 |
+------+------+

---

self Join

cross join는 서로 다른 테이블을 join한 것이지만 self join은 같은 테이블을 join한 것입니다.

mysql> select A1.*,A2.* from A A1, A A2;
+------+------+
| a    | a    |
+------+------+
|    1 |    1 |
|    2 |    1 |
|    3 |    1 |
|    4 |    1 |
|    5 |    1 |
|    1 |    2 |
|    2 |    2 |
|    3 |    2 |
|    4 |    2 |
|    5 |    2 |
|    1 |    3 |
|    2 |    3 |
|    3 |    3 |
|    4 |    3 |
|    5 |    3 |
|    1 |    4 |
|    2 |    4 |
|    3 |    4 |
|    4 |    4 |
|    5 |    4 |
|    1 |    5 |
|    2 |    5 |
|    3 |    5 |
|    4 |    5 |
|    5 |    5 |
+------+------+

---

nested loop join

loop join을 두 테이블은 중첩 for문과 같은 원리로 join을 합니다. 예를 들어서 IDOL_GROUP와 IDOL_MEMBER라는 두개의 테이블이 있고 GROUP_NAME라는 속성으로 loop join을 한다면 IDOL_GROUP 테이블의 첫번째 row를 기준으로 GROUP_NAME이 같은 IDOL_MEMBER를 찾고, 그 다음 row를 기준으로 GROU_NAME이 같은 IDOL_MEMBER을 찾고...그 다음 row를 기준으로....이렇게 중첩 for문 처럼 하나의 테이블을 기준으로 다른 테이블을 join하게 됩니다.

 

여기서 IDOL_GROUP이 outer table, IDOL_MEMBER이 inner table이 됩니다. 여기서 생각해주셔야 하는 부분은 inner table에 index가 걸려있지 않으면 full scan을 하기 때문에 index 전략이 중요한 고려사항이 됩니다. 또한 하나씩 join을 하기 때문에 대량의 테이블을 join 하는 방식으로는 적절하지 않으며 outer table이 소량의 데이터를 가진 경우가 성능에 유리합니다.

---

sort merge join

sort merge join은 loop join처럼 중첩 for문과 같은 원리로 join을 하지만 먼저 join 속성을 기준으로 정렬을 한 후 join을 하는 방식입니다. inner table쪽에 적절한 index가 없어서 nested loop join을 사용하기에 비효율적이거나 equal join이 아닌 범위로 join하는 경우 사용할 수 있습니다. 

 

table random access가 일어나지 않고 정렬하는 작업이 PGA(메모리 영역)에서 수행되기 때문에 경합이 발생하지 않아 성능에 유리한 점이 있습니다. 

---

hash join

hash join은 IDOL_GROUP 테이블과 IDOL_MEMBER 테이블에서 IDOL_MEMBER 테이블의 데이터가 너무 많은 경우 IDOL_GROUP 테이블을 hash 영역에 저장합니다. 이후 IDOL_MEMBER 테이블을 스캔 하면서 해싱 테이블을 적용하여 메모리에 로딩된 테이블과 join하는 방식입니다. 

 

hash join은 outer table, inner table에 index없거나 대용량 테이블을 join할 때 사용하면 좋습니다. 또한 equal join만 가능하고 sort merge join처럼 random access 부하가 없으며 hash 영역에 들어가는 테이블의 크기가 충분히 작아야 성능에 유리함