WITH RECURSIVE로 계층구조 탐색하기

최창효·2023년 9월 2일

CS mysql

WITH RECURSIVE

오라클에는 계층형 쿼리를 위한 START WITH절과 CONNECT BY절이 존재합니다.
MySQL에서는 WITH RECURSIVE와 UNION을 이용해 계층형 쿼리를 구현하는 게 일반적입니다.

WITH

WITH는 임시테이블을 생성해 줍니다. WITH로 테이블을 만든 구문에 ;을 찍지 않고, 뒤이어 작성하는 쿼리에 임시테이블을 활용할 수 있습니다.
```
WITH tmp_table AS (
SELECT COUNT(*) as cnt
FROM category 
WHERE master_category_id = 1
) -- ;을 찍지 않았음 
SELECT tmp_table.cnt, category.category_id FROM tmp_table, category;
```
- WITH로 작성한 tmp_table은 이어진 쿼리(;로 쿼리를 끝내기 전까지 유효)에서 활용할 수 있습니다.

UNION

UNION은 2 쿼리의 결과를 행으로 합쳐줍니다.

SELECT * FROM category WHERE master_category_id = 1의 결과는 다음과 같습니다.

category_id	category_name	master_category_id
4	생수/음료/주류	1
5	정육	1
6	수산	1

SELECT * FROM category WHERE master_category_id = 3의 결과는 다음과 같습니다.

category_id	category_name	master_category_id
10	노트북/데스크탑/태블릿	3
11	휴대폰	3
12	카메라	3

이때 UNION을 활용하면 다음과 같은 결과를 얻을 수 있습니다.

SELECT * FROM category WHERE master_category_id = 1
UNION
SELECT * FROM category WHERE master_category_id = 3;

category_id	category_name	master_category_id
4	생수/음료/주류	1
5	정육	1
6	수산	1
10	노트북/데스크탑/태블릿	3
11	휴대폰	3
12	카메라	3

UNION은 행으로 합치기 때문에 두 쿼리의 column이 일치해야 합니다.

간단히 다음과 같은 쿼리는 에러가 발생한다는 얘기입니다.

-- 3개의 column 조회
SELECT category_id, category_name, master_category_id FROM category WHERE master_category_id = 1
UNION
-- 2개의 column 조회
SELECT category_id, category_name FROM category WHERE master_category_id = 3;

UNION vs UNION ALL

UNION은 결과를 합칠 때 중복된 ROW를 제거하지만 UNION ALL은 중복을 제거하지 않습니다.

WITH RECURSIVE

with recursive를 활용하는 계층형 쿼리는 일반적으로 다음과 같은 구조를 가집니다.

WITH RECURSIVE 임시_테이블_명 AS(
	기저_쿼리
    
    UNION_ALL
    
    임시_테이블을_활용하는_쿼리
)
최종 쿼리

예제로 살펴보겠습니다.

WITH RECURSIVE CTE AS(
	SELECT 10 AS num
    
    UNION ALL
    
    SELECT num+1
    FROM CTE
    WHERE num = 10
)
SELECT * FROM CTE;

기저 쿼리는 최초에 임시테이블의 값이 됩니다. 그러므로 두번째 쿼리를 실행하기 전 CTE라는 임시테이블 SELECT 10 AS num을 실행한 것과 동일한 상태이며 다음과 같은 모양입니다.

num
10

여기서 SELECT num+1 FROM CTE WHERE num = 10을 진행하면 그 결과는 다음과 같습니다.

num
11

두 쿼리 결과를 UNION ALL로 합친 값이 CTE가 됩니다.

num
10
11

RECURSIVE하기 때문에 CTE가 다음과 같은 상태로 다시 한번 두 번째 쿼리인 SELECT num+1 FROM CTE WHERE num = 10가 진행됩니다.
이 때 기존에 있던 ROW(10)에 대해서는 해당 쿼리문을 실행하지 않고 직전에 새롭게 추가된 ROW(11)에 대해서만 쿼리가 진행됩니다. 11은 10이 아니기 때문에 더 이상 새로운 결과를 생성하지 못하고 재귀가 종료됩니다.

실습 - 카테고리 테이블을 계층구조로 탐색하기

테이블 소개

아래와 같은 모양의 Category라는 자기참조 테이블이 존재합니다.

category_id	category_name	master_category_id
1	식품	null
2	의류	null
3	전자제품	null
4	생수/음료/주류	1
5	정육	1
6	수산	1
7	상의	2
8	하의	2
9	신발	2
10	노트북/데스크탑/태블릿	3
11	휴대폰	3
12	카메라	3
13	생수	4
14	탄산음료	4
15	주류	4
16	소고기	5
17	닭고기	5
18	돼지고기	5
19	생선	6
20	오징어/쭈꾸미/낙지	6
21	전복/굴/조개	6
22	아우터	7
23	반팔	7
24	긴팔	7
25	면바지	8
26	청바지	8
27	정장 바지	8
28	운동화/스니커즈	9
29	샌들/슬리퍼	9
30	구두	9
31	노트북	10
32	데스크탑	10
33	태블릿	10
34	아이폰	11
35	갤럭시	11
36	기타	11
37	디지털 카메라	12
38	DSLR 카메라	12
39	즉석 카메라	12

위 테이블은 다음과 같은 계층구조를 지니고 있습니다.

대분류	중분류	소분류
식품	생수/음료/주류	생수
		탄산음료
		주류
	정육	소고기
		닭고기
		돼지고기
	수산	생선
		오징어/쭈꾸미/낙지
		전복/굴/조개

여기서 저는 특정 카테고리를 기준으로 검색했을 때 해당 카테고리의 모든 하위 카테고리가 모두 알 수 있는 쿼리문을 작성하고 싶습니다.

만약 식품(category_id = 1)을 기준으로 검색을 하면 다음과 같은 결과가 나오길 기대합니다.

category_id	category_name	master_category_id
4	생수/음료/주류	1
5	정육	1
6	수산	1
13	생수	4
14	탄산음료	4
15	주류	4
16	소고기	5
17	닭고기	5
18	돼지고기	5
19	생선	6
20	오징어/쭈꾸미/낙지	6
21	전복/굴/조개	6

1번과 직접적인 연결관계인 4~6번, 그리고 4~6과 연관된 13~21 카테고리를 쿼리결과로 모두 조회하고 싶습니다.

다음과 같은 재귀적이지 않은 쿼리를 작성할 수도 있을 겁니다.

SELECT *
FROM item
WHERE category_id IN
	(SELECT category_id
	FROM category 
	WHERE master_category_id IN 
		(SELECT category_id 
		FROM 
			(SELECT * 
			FROM category 
			WHERE master_category_id = 1
			) as m_category
		)
	);

하지만 이러한 쿼리는 중분류를 기준으로 검색해서 소분류들을 가져오거나, 3계층이 아니라 4,5,...계층으로 변했을 때는 사용할 수 없는 쿼리입니다.

WITH RECURSIVE 쿼리

WITH RECURSIVE temp_table AS(
	SELECT * FROM category WHERE master_category_id = 1 // 1: 원하는 Root값

    UNION ALL
    
    SELECT c.category_id,c.category_name,c.master_category_id FROM category AS c
			INNER jOIN temp_table ON c.master_category_id = temp_table.category_id
)
select * from temp_table;

위 쿼리는 다음과 같은 결과를 반환합니다.

category_id	category_name	master_category_id
4	생수/음료/주류	1
5	정육	1
6	수산	1
13	생수	4
14	탄산음료	4
15	주류	4
16	소고기	5
17	닭고기	5
18	돼지고기	5
19	생선	6
20	오징어/쭈꾸미/낙지	6
21	전복/굴/조개	6

다음과 같은 방식으로 작업이 진행됩니다.

기저조건으로 SELECT * FROM category WHERE master_category_id = 1을 실행하면 temp_table이 다음과 같은 모습으로 생성됩니다.

category_id category_name master_category_id
4 생수/음료/주류 1
5 정육 1
6 수산 1
category_id가 4인 값에 대해 INNER JOIN이 실행되고 해당 테이블과 기존 테이블의 UNION ALL 이 실행됩니다.

category_id category_name master_category_id
4 생수/음료/주류 1
5 정육 1
6 수산 1
13 생수 4
14 탄산음료 4
15 주류 4
category_id가 5인 값에 대해 INNER JOIN이 실행되고 해당 테이블과 기존 테이블의 UNION ALL 이 실행됩니다.

category_id category_name master_category_id
4 생수/음료/주류 1
5 정육 1
6 수산 1
13 생수 4
14 탄산음료 4
15 주류 4
16 소고기 5
17 닭고기 5
18 돼지고기 5

category_id가 6인 값에 대해 INNER JOIN이 실행되고 해당 테이블과 기존 테이블의 UNION ALL 이 실행됩니다.

category_id	category_name	master_category_id
4	생수/음료/주류	1
5	정육	1
6	수산	1
13	생수	4
14	탄산음료	4
15	주류	4
16	소고기	5
17	닭고기	5
18	돼지고기	5
19	생선	6
20	오징어/쭈꾸미/낙지	6
21	전복/굴/조개	6

새롭게 추가된 category_id가 13, 14, .. 21에 대해서도 INNER JOIN이 실행됩니다. 하지만 해당 쿼리의 결과로 아무 값이 추가되지 않습니다. 결과적으로 4번 작업에서 살펴본 테이블 모양이 최종적인 temp_table의 모양이 됩니다.

최창효

기록하고 정리하는 걸 좋아하는 개발자.

이전 포스트

for-for-if 구조 개선해보기

다음 포스트

WITH RECURSIVE로 계층구조 탐색하기

WITH RECURSIVE

WITH

UNION

WITH RECURSIVE

실습 - 카테고리 테이블을 계층구조로 탐색하기

테이블 소개

WITH RECURSIVE 쿼리

for-for-if 구조 개선해보기

@Repository의 스프링 예외 추상화

0개의 댓글