MySQL Recursive CTE 완벽 가이드

🎯 재귀 CTE란?

ecursive CTE (Common Table Expression)는 자기 자신을 참조하는 임시 테이블로, 계층 구조나 반복적인 데이터 처리에 사용됩니다.

기본 구조

 

sql

WITH RECURSIVE cte_name AS (
    -- 1. 기본 케이스 (Base Case/Anchor)
    SELECT 초기값
    FROM 테이블
    WHERE 시작조건
    
    UNION ALL
    
    -- 2. 재귀 케이스 (Recursive Case)  
    SELECT 다음값
    FROM 테이블 t
    INNER JOIN cte_name c ON 조인조건  -- 자기 참조!
)
SELECT * FROM cte_name;

---

🔄 재귀 실행 과정

Step-by-Step 실행 흐름

 

1단계: Base Case 실행 → 초기 결과셋 생성
2단계: 초기 결과와 원본 테이블 조인 → 새로운 행 생성  
3단계: 새로운 행과 원본 테이블 조인 → 또 다른 행 생성
...
N단계: 더 이상 새로운 행이 생성되지 않으면 종료

시각적 예시

 

Base:     [1]
1회차:    [1] → [1,2] 
2회차:    [1,2] → [1,2,3]
3회차:    [1,2,3] → [1,2,3,4]
4회차:    [1,2,3,4] → 새로운 행 없음 → 종료

---

📊 실전 예제로 이해하기

예제 1: 숫자 시퀀스 생성

 

sql

-- 1부터 10까지 숫자 생성
WITH RECURSIVE numbers AS (
    -- Base Case: 시작점
    SELECT 1 AS n
    
    UNION ALL
    
    -- Recursive Case: n+1 생성
    SELECT n + 1
    FROM numbers
    WHERE n < 10  -- 종료 조건
)
SELECT * FROM numbers;

-- 결과: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

예제 2: 조직도/계층 구조

 

sql

-- 샘플 데이터
CREATE TABLE employees (
    id INT,
    name VARCHAR(50),
    manager_id INT
);

INSERT INTO employees VALUES
(1, 'CEO', NULL),
(2, '부사장A', 1),
(3, '부사장B', 1), 
(4, '팀장A', 2),
(5, '팀장B', 2),
(6, '사원A', 4),
(7, '사원B', 5);

-- 재귀 쿼리: 조직도 전체 구조
WITH RECURSIVE org_chart AS (
    -- Base: 최고 경영진 (CEO)
    SELECT id, name, manager_id, 1 AS level, name AS path
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive: 각 레벨의 하위 직원들
    SELECT e.id, e.name, e.manager_id, 
           o.level + 1,
           CONCAT(o.path, ' → ', e.name) AS path
    FROM employees e
    INNER JOIN org_chart o ON e.manager_id = o.id
)
SELECT level, name, path
FROM org_chart
ORDER BY level, id;

결과:

 

level | name    | path
------|---------|------------------
1     | CEO     | CEO
2     | 부사장A  | CEO → 부사장A
2     | 부사장B  | CEO → 부사장B  
3     | 팀장A   | CEO → 부사장A → 팀장A
3     | 팀장B   | CEO → 부사장A → 팀장B
4     | 사원A   | CEO → 부사장A → 팀장A → 사원A
4     | 사원B   | CEO → 부사장A → 팀장B → 사원B

---

🧬 Ecoli 예제 분석

데이터 구조 이해

 

ecoli_data 테이블:
id | parent_id | ...
1  | NULL      (1세대 - 루트)
2  | 1         (2세대 - 1의 자식)
3  | 1         (2세대 - 1의 자식)  
4  | 2         (3세대 - 2의 자식)
5  | 3         (3세대 - 3의 자식)
6  | 4         (4세대 - 4의 자식)

재귀 실행 과정

 

sql

WITH RECURSIVE g AS (
    -- Step 1: Base Case - 1세대 찾기
    SELECT id, 1 g_level          -- 결과: {1}
    FROM ecoli_data
    WHERE parent_id IS NULL
    
    UNION ALL
    
    -- Step 2~N: Recursive Case - 자식들 찾기
    SELECT a.id, b.g_level+1 
    FROM ecoli_data a             -- 전체 데이터
    INNER JOIN g b                -- 이전 단계 결과
    ON a.parent_id = b.id         -- 부모-자식 관계
)

실행 단계:

 

1단계: {(1,1)} - 1세대
2단계: {(1,1), (2,2), (3,2)} - 2세대 추가
3단계: {(1,1), (2,2), (3,2), (4,3), (5,3)} - 3세대 추가  
4단계: {(1,1), (2,2), (3,2), (4,3), (5,3), (6,4)} - 4세대 추가
5단계: 더 이상 자식 없음 → 종료

 

---

⚠️ 주의사항 & 팁

1. 무한 루프 방지

 

sql

-- ❌ 위험: 무한 루프 가능성
WITH RECURSIVE danger AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM danger  -- 종료 조건 없음!
)

-- ✅ 안전: 종료 조건 명시
WITH RECURSIVE safe AS (
    SELECT 1 AS n
    UNION ALL  
    SELECT n + 1 FROM safe
    WHERE n < 1000  -- 최대 1000까지만
)

2. 성능 최적화

 

sql

-- ✅ 인덱스 활용을 위한 조건 순서
WITH RECURSIVE hierarchy AS (
    SELECT id, parent_id, 1 AS level
    FROM tree_table
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT t.id, t.parent_id, h.level + 1
    FROM tree_table t
    INNER JOIN hierarchy h ON t.parent_id = h.id
    WHERE h.level < 10  -- 깊이 제한으로 성능 보장
)

3. 메모리 관리

 

sql

-- 대용량 데이터의 경우 중간 결과 제한
WITH RECURSIVE large_tree AS (
    -- Base case
    UNION ALL  
    -- Recursive case
    WHERE 조건 AND level <= 5  -- 레벨 제한
)

---

🎯 코딩테스트에서의 활용

자주 출제되는 패턴

1. 계층 구조 분석

 

sql

-- "각 세대별 개체 수 구하기"
-- "리프 노드(자식 없는 노드) 찾기"  
-- "특정 세대의 조상 찾기"

2. 연속된 데이터 처리

 

sql

-- "연속된 N일간의 매출"
-- "누적 합계 계산"
-- "이동 평균 계산"

3. 그래프 탐색

 

sql

-- "친구의 친구 찾기"
-- "추천 시스템의 연결 고리"
-- "네트워크 분석"

면접 어필 포인트

1. 복잡한 계층 구조를 간단하게 처리
2. 반복 로직을 SQL로 효율적 구현
3. 대용량 데이터에서도 안정적 실행

---

💡 실무 활용 사례

카테고리 계층 구조

 

sql

-- 전자제품 → 스마트폰 → iPhone → iPhone 15
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, 1 AS depth
    FROM categories  
    WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT c.id, c.name, c.parent_id, ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT REPEAT('  ', depth-1) || name AS category_hierarchy
FROM category_tree
ORDER BY depth, name;

추천 시스템

 

sql

-- "이 상품을 본 고객들이 함께 본 상품" 3단계까지
WITH RECURSIVE recommendations AS (
    SELECT product_id, 1 AS step
    FROM user_views  
    WHERE user_id = :target_user_id
    
    UNION ALL
    
    SELECT uv.product_id, r.step + 1
    FROM user_views uv
    INNER JOIN recommendations r ON uv.user_id IN (
        SELECT user_id FROM user_views WHERE product_id = r.product_id
    )
    WHERE r.step < 3
)

---

🚀 고급 패턴

경로 추적

 

sql

WITH RECURSIVE path_finder AS (
    SELECT id, name, CAST(name AS CHAR(1000)) AS path
    FROM nodes WHERE id = 1
    
    UNION ALL
    
    SELECT n.id, n.name, 
           CONCAT(pf.path, ' → ', n.name)
    FROM nodes n
    INNER JOIN path_finder pf ON n.parent_id = pf.id
)

그래프 사이클 검사

 

sql

WITH RECURSIVE cycle_check AS (
    SELECT id, parent_id, CONCAT(',', id, ',') AS visited
    FROM tree_table WHERE parent_id IS NULL
    
    UNION ALL
    
    SELECT t.id, t.parent_id, 
           CONCAT(cc.visited, t.id, ',') 
    FROM tree_table t
    INNER JOIN cycle_check cc ON t.parent_id = cc.id
    WHERE INSTR(cc.visited, CONCAT(',', t.id, ',')) = 0  -- 사이클 방지
)

핵심 요약: 재귀 CTE는 계층 구조나 반복 로직을 SQL로 효율적으로 처리하는 강력한 도구입니다. 복잡한 데이터 분석이 필요한 환경에서는 필수 스킬이에요! 🎯