[MySQL] WITH RECURSIVE( 재귀 )

WITH RECURSIVE는 재귀 CTE (Common Table Expression)를 정의할 때 사용하는 MySQL의 기능입니다.
이 기능은 계층적 데이터(예: 조직도, 디렉토리 구조, 그래프 등)를 처리하거나 반복적으로 데이터를 생성해야 할 때 사용됩니다.
MySQL에서 WITH RECURSIVE는 8.0 버전 이상에서 지원됩니다.

---

기본 문법

WITH RECURSIVE cte_name AS (
    -- Anchor 부분: 재귀의 시작점
    초기_쿼리

    UNION ALL

    -- Recursive 부분: 반복적으로 실행되는 쿼리
    재귀_쿼리
)
SELECT * 
FROM cte_name;

1. Anchor 쿼리: 재귀를 시작하는 기본 데이터를 반환합니다.
2. Recursive 쿼리: Anchor 쿼리에서 반환된 결과를 기반으로, 반복적으로 데이터를 처리합니다.
3. UNION ALL: Anchor와 Recursive 쿼리를 결합합니다.
4. 종료 조건: Recursive 쿼리는 특정 조건이 충족될 때까지 반복되므로, 종료 조건이 반드시 필요합니다.

---

예제 1: 숫자 생성

1부터 10까지 숫자를 생성하는 간단한 예제입니다.

WITH RECURSIVE numbers AS (
    -- Anchor: 시작 값 정의
    SELECT 1 AS num

    UNION ALL

    -- Recursive: 1씩 증가시키면서 num 값을 재귀적으로 계산
    SELECT num + 1
    FROM numbers
    WHERE num < 10 -- 종료 조건
)
SELECT * 
FROM numbers;

결과:

+-----+
| num |
+-----+
|  1  |
|  2  |
|  3  |
|  4  |
|  5  |
|  6  |
|  7  |
|  8  |
|  9  |
| 10  |
+-----+

---

예제 2: 계층적 데이터 탐색 (조직도)

데이터 예제

employees 테이블:

id name manager_id

1	Alice	NULL
2	Bob	1
3	Charlie	1
4	Dave	2
5	Eve	2

문제

• manager_id를 기반으로 조직도를 출력하고, 각 직원의 계층 레벨을 구합니다.

쿼리

WITH RECURSIVE hierarchy AS (
    -- Anchor: 최상위 관리자
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: 상위 관리자를 기반으로 하위 직원 검색
    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
)
SELECT * 
FROM hierarchy
ORDER BY level;

결과:

id name manager_id level

1	Alice	NULL	1
2	Bob	1	2
3	Charlie	1	2
4	Dave	2	3
5	Eve	2	3

---

예제 3: 그래프 데이터 탐색

문제

노드 A에서 시작해 연결된 모든 노드를 탐색하는 예제입니다.

데이터 예제: edges 테이블

from_node to_node

A	B
A	C
B	D
C	E
D	F

쿼리

WITH RECURSIVE graph_path AS (
    -- Anchor: 시작 노드
    SELECT from_node, to_node
    FROM edges
    WHERE from_node = 'A'

    UNION ALL

    -- Recursive: 현재 노드의 다음 노드를 찾음
    SELECT e.from_node, e.to_node
    FROM edges e
    JOIN graph_path gp ON e.from_node = gp.to_node
)
SELECT * 
FROM graph_path;

결과:

from_node to_node

A	B
A	C
B	D
C	E
D	F

---

특징과 장점

1. 계층적 데이터 처리
   • 조직도, 디렉토리 구조, 그래프 탐색 등의 문제를 쉽게 해결할 수 있습니다.
2. 가독성 향상
   • 재귀 CTE를 사용하면 복잡한 쿼리를 단계적으로 나눌 수 있어 가독성이 좋아집니다.
3. 반복적 데이터 생성 가능
   • 숫자 생성, 반복 연산 등 반복적으로 데이터를 생성할 때 유용합니다.
4. 동적 쿼리 가능
   • 쿼리 내에서 결과를 점진적으로 확장하면서 원하는 결과를 도출할 수 있습니다.

---

주의사항

1. 종료 조건 필수
   • 종료 조건을 명확히 설정하지 않으면 무한 루프에 빠져 성능 문제가 발생할 수 있습니다.
2. 성능 주의
   • 재귀 호출이 많아질수록 성능이 저하될 수 있으므로, 데이터 크기와 호출 횟수를 고려해야 합니다.
3. MySQL 8.0 이상
   • WITH RECURSIVE는 MySQL 8.0 이상에서만 지원됩니다.

---

WITH RECURSIVE는 복잡한 계층 구조나 반복적인 작업을 간단하게 처리할 수 있는 강력한 도구입니다! 😊