SELF JOIN (下) : 셀프 조인의 용례

이전 글인 SELF JOIN (上) : 같은 테이블을 조인하기 에서는 셀프 조인의 기본적인 개념에 관해 살펴보았습니다. 셀프 조인은 같은 테이블 내의 데이터를 조인합니다. 즉, 하나의 테이블이 자기 자신을 대상으로 조인하는 것을 말합니다. 왜 본인을 조인하냐고요? 종종 우리는 한 행에 있는 값을 같은 행에 있는 다른 값과 비교해야 할 때가 생기고는 합니다. 이를 해결하려면 자기 자신 테이블을 조인하여 같은 행의 데이터 값을 가지고 비교하는 방법밖에는 없기 때문입니다. 또 기억할 점은, 셀프 조인을 할 경우에는 꼭 테이블에 별칭을 주어야 한다는 것입니다. 그렇지 않으면, 같은 테이블 내의 열을 가지고 데이터를 다루는 것이기 때문에 SQL이 어떤 행에서 데이터를 불러와야 할지 인식하지 못하기 때문입니다. 이제부턴 이번 글의 주제인 셀프 조인의 용례에 대해 살펴보도록 합시다. 

 

셀프 조인은 언제 사용하지?

셀프 조인은 주로 아래와 같은 상황에 사용하게 됩니다.

1. 위계성 데이터를 다룰 때
2. 순차성 데이터 다룰 때
3. 1개의 테이블 안에 관계성이 명시되어야 할 데이터가 여러 개 존재할 때

밑에서 각 상황에 대해 보다 자세히 알아보도록 하겠습니다.

 

1번째 사례 : 위계성 데이터 (Hierarchical Data)

위계성을 지니는 데이터가 셀프 조인을 필요로 하는 대표적인 데이터 종류입니다. 아래에 있는 테이블의 이름은 employer 입니다. 이 테이블은 직원의 아이디, 성과 이름, 월급, 직속상관인 매니저의 아이디를 저장하고 있습니다. 직속상관 값이 없는 직원 아이디 1(즉, 대표)을 제외하고는 모든 직원은 직속상관을 가지고 있습니다. 

id	first_name	last_name	salary	manager_id
1	John	Watson	7550	NULL
2	Anne	Brown	3500	1
3	James	Black	3000	1
4	Scarlett	Miller	2500	3
5	Ethan	Davis	1200	3
6	Jacob	Smith	2000	3

그럼 쿼리문을 작성해 볼까요? 아래 쿼리는 각 직원의 직속상관의 이름을 함께 보여줄 것입니다.

SELECT
 e.id,
 e.first_name,
 e.last_name,
 e.salary,
 m.first_name AS boss_first_name
 m.last_name AS boss_last_name
FROM employee AS e
INNER JOIN employee AS m
      ON e.manager_id = m.id;

동일한 테이블을 2번 사용했고, 각각 별칭을 준 것을 확인하셨나요? 첫 번째 테이블에서는 직원 정보를 가져올 것이기 때문에 e 라고 이름을 지었고, 두 번째 테이블에서는 매니저 정보를 가져올 것이기 때문에 m 이라고 이름을 지었습니다. 비록 우리는 employee 라는 1개의 동일한 테이블을 사용하지만, SQL은 2개의 다른 테이블로 인식하여 조인을 실행할 것입니다.

테이블에만 별칭을 준다고 되는 것은 아닙니다. SELECT문에서 각 테이블마다 가져올 열의 이름 앞에도 별칭을 정확히 명시해야 합니다. 똑같은 테이블을 복사했기 때문에 동일한 열이 각각 2개씩 존재하므로, 어떤 테이블의 열에서 데이터를 가져올지 명시해줘야 한다는 점 잊지 말아 주세요. 아래는 해당 쿼리가 어떤 식으로 실행되는지를 보여줍니다.

결과를 한 번 살펴보도록 할까요? 각 직원의 정보 옆에 해당 직원의 직속상관의 이름까지 잘 붙여진 걸 확인할 수 있습니다. 직원 아이디 1은 직속상관이 없기 때문에 아래 결과값에 포함되지 않았습니다.

id	first_name	last_name	salary	boss_first_name	boss_last_name
2	Anne	Brown	3500	John	Watson
3	James	Black	3000	John	Watson
4	Scarlett	Miller	2500	James	Black
5	Ethan	Davis	1200	James	Black
6	Jacob	Smith	2000	James	Black

이외에도 가족 족보와 같이 데이터끼리 위계성을 지닐 때 셀프 조인을 사용합니다. 이런 데이터를 다른 말로 나무 구조(tree structure)라고도 부릅니다. 

 

2번째 사례 : 순차성 데이터 (Sequential Data)

순차적인 데이터를 다룰 때도 셀프 조인을 사용하면 상당히 용이합니다. 예를 들어, 특정 음식의 조리법에 관한 정보가 데이터 베이스에 저장되어 있다고 가정해 봅시다. 한 테이블 안에는 한 개의 요리에 관한 조리법의 단계가 저장되어 있습니다. 데이터에 대한 이해를 위해서 아래 instruction 이라는 테이블을 가져왔습니다. 테이블을 살펴보면 각 조리법 단계마다 고유의 아이디가 부여됐고, 해당 조리법의 순서를 previous_id 열과 next_id 을 통해 보여주고 있습니다. 즉, 고유 아이디 4번의 조리법은 이전 조리법의 아이디가 2 이고, 이후 조리법의 아이디가 3 입니다. 지금 테이블 내에 주어진 고유 아이디 순서대로 ( id 1번 ➝ id 6번 ) 조리법을 읽게 된다면 순서가 뒤엉커 버립니다. 이렇게 데이터가 순차성을 지닐 때도 우리는 셀프 조인을 사용합니다. 결국 같은 테이블 내에 있는 조리법에서 순서에 맞게 해당 조리법을 가져와 붙여야 하니까요. 

id	content	previous_id	next_id
1	Preheat an oven to 220 degrees C.	NULL	2
2	Peel four potatoes.	1	4
3	Toss sliced potatoes with oil.	4	6
4	Cut potatoes into slices.	2	3
5	Season the hot slices with salt and pepper.	6	NULL
6	Bake in the preheated oven for 20 minutes.	3	5

셀프 조인을 사용하면 아래와 같은 순서의 조리법을 얻을 수 있습니다.

id	content
1	Preheat an oven to 220 degrees C.
2	Peel four potatoes.
3	Cut potateos into slices.
4	Toss sliced potatoes with oil.
5	Bake in the preheated oven for 20 minutes.
6	Season the hot slices with salt and pepper.

 

3번째 사례 : 1개의 테이블 안에 관계성이 명시되어야 할 데이터가 여러개 존재할 때

제목만 읽으면 무슨 이야기인지 잘 모르실 수도 있습니다. 대표적인 예시는 비행기 항로나 기차의 여정과 같이 1개의 장소가 출발지가 되기도 하면서 도착지도 되는, 즉 하나의 데이터 값이 다른 데이터 사이에서 여러 관계성을 지닐 때도 우리는 셀프 조인을 사용합니다. 그래프로 지금까지 말한 것을 정리해 볼 수 있습니다.

1개의 데이터가 출발지이면서 도착지인 상황. 즉, 1개 데이터가 다수의 관계성을 띨 때 우리는 셀프 조인을 사용합니다.

위 그래프는 5개의 도시를 나타내고 있고, 해당 도시 간의 관계를 말해줍니다. 화살표가 출발지에서 도착지를 표시해 줍니다. 해당 상황을 쿼리로 풀어보기 위해서 우리는 2개의 테이블을 사용해야 합니다. 하나는 도시의 고유 아이디와 도시의 이름을 저장하고 있는 city 테이블이고, 다른 하나는 경로의 고유 아이디, 출발지 도시의 아이디, 도착지 도시의 아이디 정보를 저장하고 있는 route 테이블입니다. 

아래는 city 테이블입니다.

id	name
1	Laredo
2	San Antonio
3	Austin
4	Waco
5	Houston

그리고 이번에는 route 테이블입니다.

id	from_city	to_city_id
1	4	1
2	4	3
3	4	2
4	1	4
5	2	3
6	2	5
7	5	3

우리는 city 테이블에 route 테이블을 조인해 출발지에 대해 정보를 가져올 것이고, 그렇게 해서 연결된 테이블을 가지고 다시 city 테이블을 조인해 도착지에 대한 정보를 추출할 것입니다. 이번 사례가 앞에서 살펴본 첫 번째와 두 번쨰의 사례와 살짝 다른 점은, 이전 2개의 사례에서는 A라는 1개의 테이블에 A 복사본 테이블을 조인하는 셀프 조인이었지만, 세 번째로 살펴보는 지금의 사례는 어떻게 보면 A라는 테이블에 A 테이블을 조인하는 것은 아닙니다. 이 사례는 A와 B 테이블, 즉 2개의 테이블이 존재하고, B를 통해서 결과적으로 A와 A의 테이블을 연결해 주는 것이기 때문입니다. 그렇기 때문에 우리는 이를 셀프 조인의 사례로 간주합니다. 이제 쿼리를 살펴보도록 하겠습니다.

SELECT
 start.name AS start_city,
 destination.name AS destination_city
FROM city AS start
INNER JOIN route AS r
      ON start.id = r.from_city_id
INNER JOIN city AS destination
     ON destination.id = r.to_city_id;

가장 첫 조인에서는 city 테이블과 route 테이블을 city 테이블의 id 와 route 테이블의 from_city_id 의 값을 매칭 시킴으로써 서로 다른 두 테이블을 조인하였습니다. 지금까지의 조인으로는 우리는 도착지에 대한 정보만 알 수 있습니다. 도착지에 관한 정보도 추출하려면 우리는 city 테이블을 셀프 조인해줘야 하는데요. route 테이블의 to_city_id 와 city 테이블의 id 을 매칭함으로써 두 테이블을 조인해 주었습니다.

해당 쿼리의 결과는 다음과 같습니다.

start_city	destination_city
Waco	Laredo
Waco	Austin
Waco	San Antonio
Laredo	Waco
San Antonio	Austin
San Antonio	Houston
Houston	Austin

 

SQL 셀프 조인을 이용해서 중복값 찾기

셀프 조인을 이용해서 테이블 내의 중복값을 찾아낼 수도 있습니다. 예시를 보여드리도록 할게요.

아래는 color 라는 테이블입니다.

id	name
1	blue
2	green
3	yellow
4	blue
5	yellow

id 열 내의 고유한 아이디 때문에 각 행의 데이터는 고유할 수밖에 없습니다. 하지만 그렇다고 해서, 각 행에 저장되는 색깔이 이름이 다른 건 아니네요. 보시면 blue 가 2번 저장이 되었고, yellow 도 마찬가지로 2번 저장되었습니다. 이렇게 name 열에 동일한 이름이 저장된 사례를 찾아내고 싶습니다. 아래 쿼리문을 살펴보도록 하겠습니다.

SELECT
 c1.id AS id1,
 c1.name AS color1,
 c2.id As id2,
 c2.name AS color2
FROM color AS c1
INNER JOIN color AS c2
      ON c1.name = c2.name
   AND c1.id < c2.id;

우리는 원래의 color 테이블에다가 복사본 color 테이블을 조인하고자 색깔의 이름(name 열)을 매칭해 주었습니다. AND 뒤에 넣어준 부등호 수식은 동일한 데이터를 중복으로 인식하지 않도록 하고자 함입니다. 무슨 말이냐면, color 테이블에서 id 1의 blue와 id 4의 blue는 중복값이지만, id 1의 blue와 id 1의 blue는 중복값이라고 하지 않습니다. 다시 말해, 우리는 해당 테이블을 셀프 조인함으로써 똑같은 정보가 담긴 테이블이 2개가 존재하는데요. 첫 번째 color 테이블인 c1의 id 1 blue와 두 번째 color 테이블인 c2의 id 4 blue는 중복값이라고 볼 수 있지만, 첫 번째 color 테이블인 c1의 id 1 blue와 c2의 id 1 blue는 중복값이라고 볼 수 없습니다. 이는 원래 자기 자신이니까요. 이걸 중복이라고 간주해 버리면, 우리는 기존의 데이터를 훼손하게 됩니다. 

위의 쿼리를 실행하면 아래와 같은 결과가 나옵니다. 예상 했던대로 blue와 yellow 가 중복값을 가지고 있었네요. 그리고 첫 번째 color 테이블의 id 1 blue와 두 번째 color 테이블의 id 4 blue를 중복값으로 인식한 점. 확인하셨죠? 만약 결과에 둘 다 id 1 blue가 조회되어 나온다면, 이는 결국 자기 자신을 반환한 것 밖에 되지 않기 때문에 중복값을 찾아낸 것이라고 말할 수 없습니다.

id1	color1	id2	color2
1	blue	4	blue
3	yellow	5	yellow

 

 

#note

셀프 조인과 관련한 글을 총 2개를 다루고 싶어서 각각 상, 하 편으로 나누어서 번역하였음.

원문: How to Join the Same Table Twice + 원문: An Illustrated Guide to the SQL Self Join