[MySQL 심층 탐구] SQL로 무한 루프를? WITH RECURSIVE(재귀 쿼리) 완벽 마스터

데이터베이스를 다루다 보면 "이걸 SQL 한 번으로 처리할 수 있을까?" 고민되는 순간이 있습니다. 가장 대표적인 경우가 바로 계층형 데이터(Hierarchical Data)를 탐색할 때입니다.

대댓글의 대댓글을 찾거나, 회사의 조직도를 최상위 사장님부터 말단 사원까지 한 번에 뽑아내야 할 때, 우리에게 필요한 것은 바로 WITH RECURSIVE입니다. 오늘은 이 재귀 쿼리의 작동 원리와 실전 활용법을 분석해 보겠습니다.

---

1. 재귀 쿼리(Recursive CTE)란 무엇인가?

일반적인 WITH 절이 단순히 쿼리를 가독성 있게 정리하는 용도라면, WITH RECURSIVE는 자기 자신을 참조하여 반복적으로 실행되는 쿼리입니다. 프로그래밍 언어의 for문이나 while문, 혹은 재귀 함수와 매우 흡사한 동작을 SQL 레이어에서 수행합니다.

MySQL 8.0부터 지원되는 이 기능은 복잡한 자바나 파이썬 로직 없이도 DB 안에서 계층 구조를 순식간에 탐색하게 해줍니다.

---

2. WITH RECURSIVE의 3대 핵심 구조

재귀 쿼리는 반드시 다음 세 가지 요소로 구성되어야 합니다. 하나라도 빠지면 쿼리가 실행되지 않거나 무한 루프에 빠질 수 있습니다.

1. 앵커(Anchor) 멤버: 재귀의 시작점입니다. 반복문의 '초기값'에 해당하며, 단 한 번만 실행됩니다.
2. 재귀(Recursive) 멤버: 자기 자신(CTE 이름)을 참조하여 다음 단계의 데이터를 찾아가는 부분입니다. UNION 또는 UNION ALL로 앵커와 연결됩니다.
3. 정지 조건 (Implicit Termination): 재귀 멤버에서 더 이상 가져올 데이터가 없으면 MySQL은 자동으로 반복을 중단합니다.

---

3. 실전 예제: 조직도(Manager-Employee) 탐색하기

1, 2편에서 만든 employees 테이블을 확장하여, "어떤 직원이 누구의 지시를 받는지" 계층적으로 뽑아내는 예제를 만들어 보겠습니다.

아래는 각각 1편과 ,2편입니다. 아래의 실습예제를 똑같이 미리 실행해야 밑의 실습도 가능합니다.

https://gapal.tistory.com/97

https://gapal.tistory.com/98

 

[Step 1] 데이터 보강 (상사 컬럼 추가)

먼저 기존 테이블에 manager_id 컬럼을 추가하고 데이터를 업데이트합니다.

-- 테이블 구조 수정: 상사 ID 컬럼 추가
ALTER TABLE employees ADD COLUMN manager_id INT NULL;

-- 데이터 업데이트: 계층 구조 형성
-- James(101)가 사장님, Anna(102)의 상사는 James, Kevin(103)의 상사는 Anna
UPDATE employees SET manager_id = NULL WHERE emp_no = 101; 
UPDATE employees SET manager_id = 101 WHERE emp_no = 102;
UPDATE employees SET manager_id = 102 WHERE emp_no = 103;

[Step 2] 재귀 쿼리 작성 (조직도 출력)

이제 최상위 관리자부터 하위 직원까지의 '레벨(깊이)'과 '경로'를 한 번에 조회해 보겠습니다.

WITH RECURSIVE EmployeeHierarchy AS (
    -- [1] Anchor: 최상위 사장님(manager_id가 NULL인 사람)을 찾음
    SELECT 
        emp_no, first_name, manager_id, 
        1 AS level, 
        CAST(first_name AS CHAR(200)) AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- [2] Recursive: 사장님 밑의 사원들을 반복적으로 조인하여 찾음
    SELECT 
        e.emp_no, e.first_name, e.manager_id, 
        eh.level + 1, 
        CONCAT(eh.path, ' > ', e.first_name)
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.emp_no
)
-- [3] 결과 출력
SELECT * FROM EmployeeHierarchy ORDER BY level;

1. 재귀 쿼리의 3단계 논리 흐름

이 쿼리는 한 번에 결과가 나오는 것이 아니라, 재귀이기 때문에 총 3번의 반복을 거쳐 최종 결과를 완성합니다.

[1단계] Anchor Member 실행 (초기값 설정)

가장 먼저 UNION ALL 윗부분인 앵커 쿼리가 단 한 번 실행됩니다.

• 작업: manager_id IS NULL인 사람(사장님 James)을 찾습니다.
• 결과: (101, James, NULL, 1, 'James')라는 데이터가 임시 결과 집합에 담깁니다.
• 상태: 이제 이 James 데이터가 다음 단계의 '기준'이 됩니다.

[2단계] Recursive Member 실행 (반복 탐색)

이제 UNION ALL 아랫부분이 작동합니다. 여기서 핵심은 INNER JOIN EmployeeHierarchy입니다. 자기 자신의 이전 단계 결과와 원본 테이블을 조인하는 것이죠.

• 반복 1:
  • 기준: 1단계에서 찾은 James(101)
  • 동작: employees 테이블에서 manager_id가 101인 사람을 찾습니다.
  • 찾음: Anna(102)가 발견됩니다.
  • 계산: Level은 1+1=2가 되고, Path는 'James' > 'Anna'가 됩니다.
• 반복 2:
  • 기준: 방금 찾은 Anna(102)
  • 동작: employees 테이블에서 manager_id가 102인 사람을 찾습니다.
  • 찾음: Kevin(103)이 발견됩니다.
  • 계산: Level은 2+1=3이 되고, Path는 'James > Anna' > 'Kevin'이 됩니다.

[3단계] 정지 (Termination)

• 반복 3:
  • 기준: Kevin(103)
  • 동작: employees 테이블에서 manager_id가 103인 사람을 찾습니다.
  • 찾음: 없음.
• 최종 결과: 더 이상 조인할 데이터가 없으므로 재귀를 멈추고, 지금까지 쌓인 모든 결과(UNION ALL)를 합쳐서 화면에 뿌려줍니다.

---

2. 주요 구문 디테일 해설 (왜 이렇게 썼나?)

CAST(first_name AS CHAR(200)) AS path

• 이유: 재귀 쿼리에서 문자열을 합칠 때(CONCAT), 첫 번째 데이터(Anchor)의 길이를 기준으로 컬럼의 크기가 고정되는 경우가 많습니다. 나중에 이름들이 길게 붙으면 잘릴 수 있기 때문에, 처음부터 **200자 정도의 충분한 공간(CHAR(200))**을 확보해 두는 일종의 안전장치입니다.

INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.emp_no

• 이유: 이 부분이 재귀의 엔진입니다.
  • eh (이전 단계에서 찾은 상사)
  • e (그 상사를 모시고 있는 부하 직원)
• 이 둘을 엮음으로써 위에서 아래로(Top-Down) 내려가는 가계도를 그릴 수 있게 됩니다.

---

4. 쿼리 결과 분석

emp_no	first_name	manager_id	level	path
101	James	NULL	1	James
102	Anna	101	2	James > Anna
103	Kevin	102	3	James > Anna > Kevin

• Level: 조직 내의 직급 깊이를 나타냅니다.
• Path: 해당 직원이 어떤 상사 계통을 거쳐 보고하는지 시각적으로 보여줍니다.
• 동작 원리: James를 먼저 뽑고(Level 1), James를 상사로 둔 Anna를 찾고(Level 2), 다시 Anna를 상사로 둔 Kevin을 찾은 뒤(Level 3), 더 이상 하급자가 없으므로 멈춥니다.

---

5. 주의사항: 무한 루프와 성능 

재귀 쿼리는 강력한 만큼 위험 요소도 명확합니다.

• 무한 루프 방지: 데이터 설계 오류로 인해 A -> B -> A 형태의 순환 참조가 발생하면 쿼리는 무한히 실행됩니다.
  • 대책: SET max_recursion_depth = 100; 명령어로 최대 재귀 깊이를 제한할 수 있습니다.
• 메모리 부하: 재귀가 깊어질수록 중간 결과값이 메모리에 쌓입니다. 너무 방대한 계층 구조를 한 번에 조회할 때는 성능 모니터링이 필수적입니다.
• 가독성 vs 복잡도: 쿼리가 한눈에 들어오지 않을 정도로 복잡해진다면, 애플리케이션(Back-end) 단에서 처리하는 것과 어떤 것이 더 이득일지 트레이드 오프를 따져봐야 합니다.

---

6. 마무리 

계층형 데이터 처리는 트리 구조의 데이터를 애플리케이션 코드에만 의존하지 않고,
SQL 레벨에서 직접 다룰 수 있게 해주는 방법입니다.
이를 적절히 활용하면 계층 구조 탐색과 집계를 DB 단에서 처리할 수 있으며,
애플리케이션 로직과 SQL 중 성능과 비용 측면에서 더 효율적인 방식을 선택해 사용하는 것이 중요합니다.