[Database] SQL 테이블 JOIN

Database

SQL 테이블 JOIN

---

SQL의 join에 대해 배우기 앞서 먼저 join이 무엇인지부터 학습해 보자. join은 두 개 이상의 테이블 간에 데이터를 결합하는 데 사용되는 연산이다. 특히, join을 사용하면 데이터베이스에서 여러 테이블 간에 관련된 정보를 가져와서 하나의 결과 집합으로 만들 수 있다. 또한, join은 특정 조건을 만족하는 두 테이블의 행을 결합하여 결과를 생성한다. 

 

join은 여러 종류의 join들이 존재하는데 각 join들에 대해 하나씩 차례대로 살펴보자.

 

implicit(암시적) join

implicit join이란 from절에는 table들만 나열하고, where절에 join condition을 명시하는 방식을 말한다. 이 방식 약간 오래된 방법이고 where절에 selection condition과 join condition이 같이 있기 때문에 가독성이 조금 떨어진다는 특징이 있다. 또한, 복잡한 join 쿼리를 작성하다 보면 실수로 잘못된 쿼리를 작성할 가능성이 크다. 아래의 예제 쿼리를 살펴보며 implicit join의 사용법에 대해 익혀보자.

SELECT D.name
FROM employee AS E, department AS D
WHERE E.id = 1 and E.dept_id = D.id;

우선 위의 쿼리의 내용을 살펴보면 id가 1인 임직원이 속한 부서의 이름을 가져온다. 그래서 위의 쿼리를 분석을 해보면 일단 필요한 department 테이블이 필요하다, 왜냐면 우리가 구해야 하는 것은 최종적으로 부서의 이름인데, 부서의 이름은 department 테이블에 있기 때문이다. 

 

그다음 관련 있는 테이블은 employee 테이블이다. 왜냐면 부서의 이름이긴 한데 "어떤 부서의 이름인가?"의 조건이 "임직원의 아이디가 1인 사람이 속해있는 부서의 이름" 때문이다. 그래서 임직원의 아이디를 가져오기 위해 employee 테이블도 필요하다. 

 

employee

id	name	...	...	dept_id
1	JACK	...	...	1000
2	SNIDER	...	...	1001
3	JACKSON	...	...	1002
4	THOR	...	...	1003
5	TONNY	...	...	1004
6	PETER	...	...	1005

따라서, WHERE절을 살펴보면 우선은 employee 테이블에서 id가 1인 정보를 가져와야 한다. 왜냐면 위의 employee 테이블을 살펴보면 각 임직원이 어떤 부서에 속하는지에 대한 부서 아이디(dept_id)가 저장되어 있다. 그래서 현재 employee에서 id가 1인 튜플을 찾는다면 해당 임직원이 속한 부서 아이디(dept_id)도 찾을 수 있다.

id	name	...	...	...
1	JACK	...	...	1000

그래서 현재 임직원의 아이디가 1인 튜플을 찾았다면 그 임직원이 속한 부서의 아이디도 위와 같이 찾을 수 있다. 하지만 여기까지만 해서는 충분하지 않다, 왜냐면 우리가 알고 싶은 건 해당 임직원이 속한 부서의 이름이지 부서의 아이디가 아니기 때문이다. 부서의 이름은 department 테이블에 있기 때문에 department 테이블 또한 필요한 것이다.

 

department

id	name	leader_id
1000	DEV	4
1001	HQ	null
1002	HR	5
1003	DESIGN	6
1004	PRODUCT	3

그렇다면 최종적으로 임직원의 아이디가 1인 임직원이 속한 부서의 이름을 어떻게 가지고 올 수 있을까? 일단 employee 테이블에서 임직원의 아이디가 1인 튜플을 선택한 뒤, employee 테이블에 있는 dept_id를 바탕으로 department 테이블의 id와 연결을 시키면 된다. 

 

WHERE E.id = 1 and E.dept_id = D.id;

WHERE절에서 논리 연산자가 AND로 묶여있기 때문에 employee 테이블에서 임직원의 아이디가 1인 튜플을 가져온 후 그 튜플에서 dept_id와 department 테이블의 id가 같은 튜플을 조회 후, department 테이블의 name 속성을 조회하면 된다.

 

explicit(명시적) join

implicit join의 단점을 보완하기 위해 조인을 명시할 수 있는 문법이 추가가 된 것이 explicit join이다. explicit join은 FROM절에 JOIN 키워드와 함께 joined table들을 명시하는 방식이다. FROM 절에서 ON 키워드 뒤에 join condition이 명시되어 가독성이 좋고 복잡한 join 쿼리 작성 중에도 실수할 가능이 적다. 그럼 implicit join의 예제서 사용했던 쿼리를 그대로 가져와서 explicit join으로 작성해 보자. 

SELECT D.name
FROM employee AS E JOIN department AS D on E.dept_id = D.id
WHERE E.id = 1;

위의 쿼리를 살펴보면 JOIN이라는 키워드를 사용해서 명시를 했다. 그리고 join 컨디션도 implicit join에서는 WHERE절에서 사용했지만 지금은 FROM절에 ON이라는 키워드 뒤에 사용하고 있다. 

 

INNER JOIN

이미지 출처 :  한빛미디어

 

INNER JOIN은 두 table에서 join condition을 만족하는 tuple들로 result table(두 테이블을 조합)을 만드는 join이다.

SELECT *
FROM employee E JOIN department D ON E.dept_id = D.id;

먼저 앞에서 작성했던 쿼리를 조금 더 간단하게 설명하기 위해 위와 같은 형태로 바꿔보겠다. INNOER JOIN은 사실 위의 예제 쿼리에서 INNER라는 키워드가 생략된 것이다. 즉, 지금까지 예제에서 우리는 이미 INNER JOIN이라는 것을 사용해 왔던 것이다. 

SELECT *
FROM employee E INNER JOIN department D ON E.dept_id = D.id;

따라서, 위와 같이 INNER 키워드를 추가해서 사용해도 문제가 없다, 그렇다면 INNER JOIN은 어떻게 동작하는 걸까? 

 

employee

id	name	...	...	dept_id
1	JACK	...	...	1000
2	SNIDER	...	...	1002
3	JACKSON	...	...	1002
4	THOR	...	...	1003
5	TONNY	...	...	1004
6	PETER	...	...	1002
16	JOKER	...	...	null

 

 

department

id	name	leader_id
1000	DEV	4
1001	HQ	null
1002	HR	5
1003	DESIGN	6
1004	PRODUCT	3

먼저 위의 두 테이블이 있다고 했을 때, 여기서 주목해야 할 점은 employee 테이블의 임직원의 이름이 JOKER인 튜플이다. JOKER는 현재 dept_id가 없으므로 아직 부서를 배정받지 못한 걸 수도 있고 아니면 누락이 된 걸 수도 있다. 어쨌든 지금 JOKER라는 임직원은 부서 정보가 없는 상태이다. 

 

그리고 department 테이블에서 주의해서 봐야 할 점은 1001이라는 아이디를 가진 부서에 속한 임직원이 employee 테이블에서 그 어떤 임직원도 부서 아이디가 1001인 임직원이 없다. 따라서 department 테이블의 HQ 부서는 현재 어떠한 임직원도 가지고 있지 않은 부서이다. 

 

📢 그렇다면 왜 저 두 점에 대해 주의를 해야 할까?

바로 INNER JOIN의 특징과 관련이 있기 때문이다.  

SELECT *
FROM employee E INNER JOIN department D ON E.dept_id = D.id;

다시 쿼리를 한 번 살펴보자, employee 테이블과 department 테이블을 INNER JOIN을 할 건데, 그 조건은 employee 테이블의 부서 아이디와 department 테이블의 아이디가 일치하는 조건을 만족하는 튜플들에 대해서만 그 튜플들을 조합해서 조회한다. 

 

따라서, 위의 쿼리를 실행하게 되면 결괏값은 아래와 같이 나온다.

(속성의 글씨가 검은색인 부분은 employee테이블이고 흰색인 부분은 department 테이블이다.)

id	name	...	...	dept_id	id	name	leader_id
1	JACK	...	...	1000	1000	DEV	4
2	SNIDER	...	...	1002	1002	HR	5
3	JACKSON	...	...	1002	1002	HR	5
4	THOR	...	...	1003	1003	DESIGN	6
5	TONNY	...	...	1004	1004	PRODUCT	3
6	PETER	...	...	1002	1002	HR	5

employee 테이블에서 JOKER라는 이름을 가진 임직원은 dept_id 값이 null이기 때문에 쿼리의 조건을 만족하지 않아 조회되지 않는다. 마찬가지로 부서 아이디가 1001은 HQ부서는 임직원 테이블에서 부서 아이디가 1002인 임직원이 없기 때문에 조회가 되지 않는다. 

E.dept_id = D.id

조건을 다시 한번 살펴보면 먼저 employee 테이블의 dept_id를 가져온다, 그러면 위의 employee 테이블에서 JOKER 임직원의 dept_id 값을 살펴보자, 바로 null이다. 그렇다면 위의 조건은 아래와 같은 형태로 볼 수 있다.

 

null = D.id

SQL의 three-valued logic을 아시는 분이라면(모르시는 분들은 링크를 통해 참고) 해당 조건의 결과는 unknown이 반환된다는 걸 알 수 있다. WHERE 조건에서는 반드시 결과가 TRUE여야만 결과를 조회하므로 JOKER는 결과를 왜 만족할 수 없는지 이해가 갈 것이다. 

 

 

OUTER JOIN

이미지 출처 :  한빛미디어

 

방금 전 INNER JOIN에 대해 배웠기 때문에 OUTER JOIN을 이해하는 데 더욱 수월할 것이다. OUTER JOIN은 두 table에서 join condition을 만족하지 않는 tuple들도 result table에 포함하는 join이다. 따라서 OUTER JOIN에는 크게 LEFT, RIGHT, FULL로 3가지가 있다. 참고로 아래의 쿼리에서 OUTER라는 키워드는 선택사항이니 생략이 가능하다.

 

employee

id	name	...	...	dept_id
1	JACK	...	...	1000
2	SNIDER	...	...	1002
3	JACKSON	...	...	1002
4	THOR	...	...	1003
5	TONNY	...	...	1004
6	PETER	...	...	1002
16	JOKER	...	...	null

 

 

department

id	name	leader_id
1000	DEV	4
1001	HQ	null
1002	HR	5
1003	DESIGN	6
1004	PRODUCT	3

 

SELECT * FROM employee E LEFT OUTER JOIN department D ON E.dept_id = D.id;

LEFT 키워드가 있기 때문에 employee 테이블에 대해 조인 조건에 매칭이 되지 않는 튜플까지도 전부 가져온다.

id	name	...	...	dept_id	id	name	leader_id
1	JACK	...	...	1000	1000	DEV	4
2	SNIDER	...	...	1002	1002	HR	5
3	JACKSON	...	...	1002	1002	HR	5
4	THOR	...	...	1003	1003	DESIGN	6
5	TONNY	...	...	1004	1004	PRODUCT	3
6	PETER	...	...	1002	1002	HR	5
16	JOKER	...	...	null	null	null	null

따라서 결과를 확인해 보면 JOKER는 employee 테이블에 있던 정보를 그대로 가져오고 department 테이블에 있는 정보는 매칭이 되는 게 없었기 때문에 null로 들어가 있는 걸 확인할 수 있다. 

 

SELECT * FROM employee E RIGHT OUTER JOIN department D ON E.dept_id = D.id;

RIGHT 조인은 LEFT의 반대로, 오른쪽 테이블인 deparment 테이블의 모든 튜플을 조건에 상관없이 가져오고 왼쪽의 employee 테이블에서 조건이 충족되지 않는 튜플들은 null로 세팅된다.

id	name	...	...	dept_id	id	name	leader_id
1	JACK	...	...	1000	1000	DEV	4
null	null	null	null	null	1001	HR	null
2	SNIDER	...	...	1002	1002	HR	5
3	JACKSON	...	...	1002	1002	HR	5
4	THOR	...	...	1003	1003	DESIGN	6
5	TONNY	...	...	1004	1004	PRODUCT	3
6	PETER	...	...	1002	1002	HR	5

결과적으로 왼쪽의 employee 테이블에선 조건을 만족하지 않았기 때문에 null로 대체되고 department 테이블의 튜플은 모두 그대로 들어가 있는 걸 확인할 수 있다. 

 

📢 MySQL은 FULL OUTER JOIN을 지원하지 않기 때문에 예외적으로 PostgreSQL에서 실행.

SELECT * FROM employee E FULL OUTER JOIN department D ON E.dept_id = D.id

지금까지 LEFT와 RIGHT 조인을 살펴보았기 때문에 FULL OUTER 조인은 어떤 건지 짐작할 수 있을 거라 생각한다. FULL OUTER JOIN은 왼쪽 테이블과 오른쪽 테이블에서 서로 매칭되지 않는 튜플들까지 모두 가져오는 것이다. 이때 조건을 만족하지 않는 튜플은 마찬가지로 null로 대체된다. 

id	name	...	...	dept_id	id	name	leader_id
1	JACK	...	...	1000	1000	DEV	4
2	SNIDER	...	...	1002	1002	HR	5
3	JACKSON	...	...	1002	1002	HR	5
4	THOR	...	...	1003	1003	DESIGN	6
5	TONNY	...	...	1004	1004	PRODUCT	3
6	PETER	...	...	1002	1002	HR	5
16	JOKER	...	...	null	null	null	null
null	null	null	null	null	1001	HQ	null

위의 결과 테이블을 보면 매칭이 되지 않았던 employee 테이블의 JOKER와 department 테이블에서 아이디가 1001인 튜플까지 모두 가져온 걸 확인할 수 있다. 정리하면 FULL OUTER JOIN은 2개의 테이블에서 매칭되지 않는 튜플들까지도 가져온다. 

 

EQUI JOIN

EQUI JOIN은 간단하게 설명하면 join condition에서 = 연산자를 사용하는 join이다. 

 

📢 그럼 지금까지 살펴보았던 JOIN들을 보면 전부 = 연산자를 사용했는데 그것들도 다 EQUI JOIN인가?

그렇다.

 

INNER JOIN이면서 EQUI JOIN

SELECT * FROM employee E INNER JOIN department D ON E.dept_id = D.id;

 

OUTER JOIN이면서 EQUI JOIN

SELECT * FROM employee E LEFT OUTER JOIN department D ON E.dept_id = D.id;

SELECT * FROM employee E RIGHT OUTER JOIN department D ON E.dept_id = D.id;

SELECT * FROM employee E FULL OUTER JOIN department D ON E.dept_id = D.id;

 

하지만 EQUI JOIN에 대한 두 가지 시각이 있다. 필자의 경우 1번 항목의 시각으로 바라보기 때문에 혹여라도 독자분들 중에 2번 항목의 시각으로 보는 분들이어도 해당 포스팅에선 1번 항목을 기준으로 작성하고 있다는 점을 알아주길 바란다.

1. INNER JOIN, OUTER JOIN 상관없이 = 연산자를 사용한 join이라면 EQUI JOIN으로 보는 경우
2. INNER JOIN으로 한정해서 = 연산자를 사용한 경우에 EQUI JOIN으로 보는 경우

 

NATURAL JOIN

NATURAL JOIN은 두 테이블에서 같은 이름을 가지는 모든 attribute pair에 대해서 equi join을 수행한다. join condition을 따로 명시하지는 않는다. 

SELECT * FROM table1 NATURAL INNER JOIN table2;

SELECT * FROM table1 NATURAL LEFT OUTER JOIN table2;
SELECT * FROM table1 NATURAL RIGHT OUTER JOIN table2;
SELECT * FROM table1 NATURAL FULL OUTER JOIN table2;

 

employee

id	name	...	salary	dept_id
1	JACK	...	...	1000
2	SNIDER	...	...	1002
3	JACKSON	...	...	1002
4	THOR	...	...	1003
5	TONNY	...	...	1004
6	PETER	...	...	1002
16	JOKER	...	...	null

 

 

department

dept_id	dept_name	leader_id
1000	DEV	4
1001	HQ	null
1002	HR	5
1003	DESIGN	6
1004	PRODUCT	3

지금까지 살펴보았던 employee와 department 테이블의 속성 이름을 조금씩 변경해 주었다. department 테이블의 기본키 속성을 id → dept_id로 변경하였고, name도 dept_name으로 변경하였다. 

 

그럼 이 두 테이블은 예시로 NATURAL 키워드를 사용하여 INNER JOIN을 하게 되면 아래와 같은 결과를 출력한다.

dept_id	id	name	...	salary	dept_name	leader_id
1000	1	JACK	...	...	DEV	4
1002	2	SNIDER	...	...	HR	5
1002	3	JACKSON	...	...	HR	5
1003	4	THOR	...	...	DESIGN	6
1004	5	TONNY	...	...	PRODUCT	3
1002	6	PETER	...	...	HR	5

속성 이름이 같았던 dept_id는 같은 이름을 사용하고 있기 때문에 한 개만 표시를 하고, employee 테이블에 대한 값들과 department 테이블에 대한 값들을 표시해 준다. 그리고 NATURAL INNER JOIN이기 때문에 매칭이 되지 않는 employee 테이블의 JOKER나. department 테이블의 1001 부서는 표시가 되지 않는다. 

 

CROSS JOIN

두 테이블의 tuple pair로 만들 수 있는 모든 조합이다( = Cartesian product)을 result table로 반환한다. join condition은 없다. 사용방법은 암시적, 혹은 명시적으로 아래와 같이 사용할 수 있다. 

 

implicit(암시적) cross join

SELECT * FROM table1, table2;

 

explicit(명시적) cross join

SELECT * FROM table1 CROSS JOIN table2;

 

CROSS JOIN

 

CROSS JOIN은 위의 이미지와 같이 왼쪽 테이블의 튜플 1개가 오른쪽 테이블의 모든 튜플과 조합되게 된다. 마찬가지로 왼쪽 테이블의 2번째, 3번째,... 마지막 튜플까지 같은 작업을 반복하게 된다. 

 

employee

id	name	...	...	dept_id
1	JACK	...	...	1000
2	SNIDER	...	...	1002
3	JACKSON	...	...	1002
4	THOR	...	...	1003
5	TONNY	...	...	1004
6	PETER	...	...	1002
16	JOKER	...	...	null

 

 

department

id	name	leader_id
1000	DEV	4
1001	HQ	null
1002	HR	5
1003	DESIGN	6
1004	PRODUCT	3

위와 같이 두 테이블이 있을 때 CROSS JOIN을 하면 어떻게 되는지 한 번 살펴보자.

SELECT * FROM employee CROSS JOIN department;

 

id	name	...	...	dept_id	id	name	leader_id
1	JACK	...	...	1000	1000	DEV	4
1	JACK	...	...	1000	1001	HQ	null
1	JACK	...	...	1000	1002	HR	5
1	JACK	...	...	1000	1003	DESIGN	6
1	JACK	...	...	1000	1004	PRODUCT	3
2	SNIDER	...	...	1002	1000	DEV	4
2	SNIDER	...	...	1002	1001	HQ	null
...	...	...	...	...	...	...	...

employee 테이블의 각 튜플이 department 테이블의 모든 튜플과 조합이 돼서 결과가 나오는 걸 확인할 수 있다. 

 

📢 MySQL에서 CROSS JOIN은 SQL 표준과는 약간 다른 점이 있다.

• MySQL에서는 CROSS JOIN = INNER JOIN = JOIN이다.
• CROSS JOIN에 ON(or USING)을 같이 쓰면 INNER JOIN으로 동작한다.
• INNER JOIN(or JOIN)이 ON(or USING) 없이 사용되면 CROSS JOIN으로 동작한다.