SELF JOIN (上) : 같은 테이블을 조인하기

조인(JOIN)은 SQL 안에서 가장 많이 쓰이는 문 중 하나입니다. 이미 해당 개념을 공부하셨다면, 여러분은 이미 조인이 2개 혹은 그 이상의 테이블을 1개의 공통된 데이터 세트로 합칠 때 사용된다는 것을 아실 겁니다. 이 글에서는 조인 중에서도 독특한 종류의 조인을 살펴보고자 합니다. 바로 동일한 테이블 1개를 그 자신에게 합치는, 즉 똑같은 테이블을 합치는 셀프 조인을 살펴볼 것입니다. 셀프 조인은 왜 필요한 것일까요? 그리고 셀프 조인은 어떠한 경우에 사용해야 하는 것일까요? 그리고 SQL 문에서는 어떤 식으로 쿼리문을 작성해야 할까요? 지금부터 살펴보도록 합시다.

 

 

JOINs : 개념 다시 짚고 가기

2개의 테이블이 있다고 가정했을 때, 공통된 열을 활용해 2개의 테이블을 합치는 것을 우리는 조인이라고 말합니다. 이를 이해하기 위해 아래 그림을 살펴보도록 합시다.

현재 왼쪽에는 customer 테이블이 있고, 오른쪽에는 city 테이블이 있습니다. 그리고 이 두 테이블은 city_id 라는 공통된 열을 가지고 있습니다. 자, 이제 우리는 한 테이블 내에서 각 고객의 이름, 생년월일, 배우자 정보 이외에도 그들이 사는 도시의 이름까지 함께 살펴보고 싶습니다. 아래와 같이 조인을 사용해 쿼리문을 작성해 볼 수 있겠네요.

SELECT
 c1.customer_id,
 c1.firstname,
 c1.lastname,
 c1.birthdate,
 c1.spouse_id,
 c1.city_id,
 c2.name AS city_name
FROM customer AS c1
INNER JOIN city AS c2
      ON c1.city_id = c2.city_id;

위에서 사용된 조인을 살펴보면 공통된 열인 city_id를 중심으로 customer 테이블과 city 테이블의 데이터를 서로 짝지었습니다. customer 테이블에서는 총 6개의 열을, city 테이블에서는 단 1개의 열(name)을 선택했습니다. 조인의 종류는 여러 가지가 있는데, 이번 예시는 그중에서도 이너 조인(INNER JOIN)을 사용했네요.

조인의 기본적인 문법에 관해선 이 글에서 다루지 않겠습니다. 위 쿼리문을 실행하면 아래와 같이 customer 테이블의 6개 열과 city 테이블의 1개 열로 구성된 1개의 테이블을 반환해줍니다.

customer_id	firstname	lastname	birthdate	spouse_id	city_id	city_name
1	John	Mayer	1983‑05‑12	2	1	London
2	Mary	Mayer	1990-07-30	1	1	London
3	Lisa	Ross	1989-04-15	5	6	Oxford
4	Anna	Timothy	1988-12-26	6	4	Leeds
5	Tim	Ross	1957-08-15	3	6	Oxford
6	Steve	Donell	1967-07-09	4	4	Leeds
7	Donna	Trapp	1978-06-23	0	2	Manchester

 

똑같은 테이블을 여러 번 조인하기

지금까지 조인의 기본적인 개념을 떠올려 보았으니, 이제는 본격적으로 좀 더 복잡한 조인을 살펴보도록 합시다. 데이터를 다루다 보면 같은 테이블을 여러 번 조인해야 할 경우가 발생합니다. Sometimes you need to join the same table multiple times. Generally, this involves adding one or more columns to a result set from the same table but to different records or by different columns. 우리는 2가지 시나리오를 살펴볼 것입니다. 하나는 자기 자신을 합치는 경우이고 다른 하나는 다단계 관계를 나타내기 위해 테이블을 조인하는 경우입니다.

 

셀프 조인: 자기 자신에게 테이블을 조인하는 법

셀프 조인은 여러 조인 중에서도 꽤나 독특한 아이입니다. 2개의 서로 다른 테이블 내에 있는 데이터를 하나의 테이블로 붙이는 대신 자기 자신 안에 있는 똑같은 데이터를 가져다 붙이니까요. 어째서 이미 존재하는 데이터를 조인을 통해 또 가져다 붙여 쓰고 싶은 것일까요? 위에서 살펴본 조인의 예시는 city 테이블 내의 열인 name 을 customer 테이블에 가져다 붙이고 싶었습니다. 그래서 2개의 테이블을 조인해 주었죠. 하지만 셀프 조인을 한다는 것은, 예를 들자면, customer 테이블의 데이터를 다시 customer 테이블에다가 가져다 붙이는 것입니다.

customer 테이블을 가져와서 부연 설명을 해보도록 하겠습니다.

customer_id	firstname	lastname	birthdate	spouse_id
1	John	Mayer	1983-05-12	2
2	Mary	Mayer	1990-07-30	1
3	Lisa	Ross	1989-04-15	5
4	Anna	Timothy	1988-12-26	6
5	Tim	Ross	1957-08-15	3
6	Steve	Donell	1967-07-09	4
7	Donna	Trapp	1978-06-23	.

spouse_id 열은 고객의 배우자의 customer_id 정보를 담고 있습니다. 예를 들어, customer_id 1과 2는 (John과 Mary) 서로의 배우자입니다. customer_id 3과 5 (Lisa와 Tom) 또한 서로의 배우자네요. 이런 식으로 데이터가 구성되어 있습니다. 하지만 해당 테이블을 보면 고객의 이름과 그 고객의 배우자의 아이디는 알 수 있지만, 배우자의 성과 이름은 바로 알지 못하는 상황입니다. 그래서 customer 테이블에 배우자의 성과 이름을 붙여주고 싶습니다. 이럴 때 바로 셀프 조인을 사용합니다. customer 테이블을 customer 테이블에 조인할 것입니다. 즉, customer 테이블 안에 있는 데이터를 customer 테이블 안에 있는 데이터와 매칭 하여 옆에다가 고스란히 붙여줄 것입니다.

SELECT
 cust.customer_id,
 cust.firstname,
 cust.lastname,
 cust.birthdate,
 cust.spouse_id,
 spouse.firstname AS spouse_firstname,
 spouse_lastname AS spouse_lastname
FROM customer AS cust
INNER JOIN customer AS spouse
   ON cust.spouse_id = spouse.customer_id

이 쿼리문을 실행하면 아래와 같은 결과가 나옵니다.

customer_id	firstname	lastname	birthdate	spouse_id	spouse_firstname	spouse_lastname
1	John	Mayer	1983‑05‑12	2	Mary	Mayer
2	Mary	Mayer	1990-07-30	1	John	Mayer
3	Lisa	Ross	1989-04-15	5	Tim	Ross
4	Anna	Timothy	1988-12-26	6	Steve	Donell
5	Tim	Ross	1957-08-15	3	Lisa	Ross
6	Steve	Donell	1967-07-09	4	Anna	Timothy

셀프 조인을 실 예제를 살펴보았으니, 방금 우리가 위에서 작성했던 쿼리문을 제대로 뜯어보면서 이해해 봅시다.

 

셀프 조인 문법

셀프 조인의 문법은 다른 조인과 크게 다를 바가 없습니다. 편의를 위해 위에서 작성했던 쿼리문을 가져오도록 하겠습니다.

SELECT
 cust.customer_id,
 cust.firstname,
 cust.lastname,
 cust.birthdate,
 cust.spouse_id,
 spouse.firstname AS spouse_firstname,
 spouse.lastname AS spouse_lastname
FROM customer AS cust
INNER JOIN customer AS spouse
   ON cust.spouse_id = spouse_id;

우리가 추가적으로 필요한 정보는 배우자의 성과 이름입니다. 근데 해당 정보는 바로 같은 테이블 안에 customer_id 로 저장이 되어 있습니다. spouse_id 가 customer 테이블 내의 다른 고객의 아이디, 즉 customer_id를 의미하기 때문에 우리는 이를 중심으로 테이블을 합쳐줄 것입니다. 

좀 더 쉽게 설명해보자면, 원본 테이블이 있고 그 원본 테이블의 복사본을 생성하는 것입니다. 그렇게 똑같은 2개의 테이블을 합친다고 생각하시면 됩니다. 우리 예시를 가지고 이야기하자면, customer 이라는 원본 테이블이 있고 이것의 복사본을 만들어서 spouse 테이블이라고 이름을 지어주었습니다. customer 테이블 안의 spouse_id 는 spouse 테이블 안의 spouse_id 와 정확히 일치할 것이고, 이를 매칭함으로써 spouse 테이블에서 spouse_firstname 과 spouse_lastname 을 가져와 customer 테이블에 붙일 것입니다. 

셀프 조인에서 유념할 점은 셀프 조인은 테이블의 이름을 꼭 지정해줘야 합니다. 그 이유는 똑같은 데이터가 총 2개의 테이블에 저장되어 있는 상황에서 SQL은 특정 데이터를 어떤 테이블에서 가져와야 할지 모르기 때문입니다. SQL은 셀프 조인을 동일한 테이블을 합친다고 인식하지 않고, 그렇게 인식하지 못합니다. 똑같은 테이블을 합치기 때문에 우리가 셀프 조인이라는 이름을 붙인 것이지 SQL 자체에는 셀프 조인이라는 개념이 존재하지 않습니다. 그래서 우리는 셀프 조인을 하고 싶을 때 SQL 문에다가 서로 다른 테이블 별칭을 주어 서로 다른 테이블 2개를 조인하는 것이라고 인식케 합니다. 위 예시에서는 원본 customer 테이블은 cust 라는 별칭을, 복사본 테이블에는 spouse 라는 별칭을 주었습니다.

SELECT 문에는 우리가 보고 싶은 열의 이름을 쭉 나열합니다. 여기서도 또한 열 이름 앞에 어떤 테이블 내의 열을 가져와야 할지 말해줘야 합니다. 똑같은 열이 2개의 테이블에 존재하니까요. 쿼리문을 보면 customer 테이블에서 총 5개의 열을 (cust.customer_id, cust.firstname, cust.lastname, cust.birthdate, cust.spouse_id) 가져왔고, spouse 테이블에서 총 2개의 열을 (spouse.firstname, spouse.lastname) 가져온 것을 확인할 수 있습니다.위 예시의 경우 우리는 이너 조인을 사용했지만, 여러분이 어떤 결과를 얻길 원하는지에 따라 다른 종류의 쿼리를 사용하셔도 됩니다. LEFT 조인, RIGHT 조인, CROSS 조인 등 말입니다. 우리는 양 쪽 테이블에 공통으로 존재하는 결과값만 살펴보고 싶었기 때문에 이너 조인을 사용한 것뿐입니다. 그래서 결과 테이블을 살펴보면 오직 배우자만 있는 고객들만 반환된 것을 볼 수 있습니다. 원래 테이블에서 customer_id 7인 Donna는 배우자가 없기 때문에, 조인을 통해 만들어낸 결과 테이블에는 Donna가 없는 것을  확인할 수 있습니다. 셀프 조인을 실행하기 위해 꼭 이너 조인을 사용해야 하는 것은 아니라는 점 기억해주세요.

 

원문: How to Join the Same Table Twice