재귀 CTE(Common Table Expression, 공통 테이블 표현식)는 자기 자신의 이름을 참조하는 서브쿼리입니다. WITH RECURSIVE 키워드로 정의하며 반드시 종료 조건을 포함해야 합니다. 재귀 CTE는 숫자 시리즈 생성, 계층형 데이터 탐색, 그래프 순회 등 다양한 상황에서 유용하게 활용됩니다.
기본 문법
WITH RECURSIVE cte_name (col1, col2, ...) AS ( -- 비재귀 부분(기저 사례): 초기 행 생성 SELECT col1, col2 FROM table_name UNION ALL -- 재귀 부분: cte_name 자기 참조 SELECT col1, col2 FROM cte_name WHERE condition ) SELECT * FROM cte_name;
- 첫 번째
SELECT는 기저 사례(base case)로, 초기 행을 제공합니다. UNION ALL은 각 반복에서 생성된 행을 누적합니다(DISTINCT를 사용하면 중복 행이 제거됩니다).- 두 번째
SELECT는 재귀 부분으로, WHERE 조건이 거짓이 될 때까지 반복 실행됩니다.
예제 1: 처음 5개의 홀수 생성하기
WITH RECURSIVE odd_no (sr_no, n) AS ( SELECT 1, 1 UNION ALL SELECT sr_no + 1, n + 2 FROM odd_no WHERE sr_no < 5 ) SELECT * FROM odd_no;
+-------+---+ | sr_no | n | +-------+---+ | 1 | 1 | | 2 | 3 | | 3 | 5 | | 4 | 7 | | 5 | 9 | +-------+---+
기저 사례가 (1, 1)을 반환하고, 각 반복마다 sr_no는 1씩, n은 2씩 증가합니다. sr_no가 5에 도달하면 WHERE 조건이 실패하면서 재귀가 종료됩니다.
예제 2: 직원 조직도 계층 구조 탐색하기
좀 더 실용적인 활용 사례로, 관리자-직원 계층 구조를 탐색하는 방법을 살펴보겠습니다.
-- 가정: employees(id, name, manager_id) 테이블 존재 WITH RECURSIVE org_chart (id, name, level) AS ( -- 기저 사례: 최상위 관리자(상사가 없는 경우) SELECT id, name, 0 FROM employees WHERE manager_id IS NULL UNION ALL -- 재귀 사례: 직속 부하 직원 찾기 SELECT e.id, e.name, oc.level + 1 FROM employees e JOIN org_chart oc ON e.manager_id = oc.id ) SELECT * FROM org_chart ORDER BY level;
이 쿼리는 최상위 관리자(level 0)에서 시작하여 각 단계별로 모든 부하 직원을 재귀적으로 찾아내며, 전체 조직도 트리를 완성합니다.
핵심 포인트
- 무한 루프를 방지하려면 재귀 SELECT의 WHERE 절에 반드시 종료 조건을 포함해야 합니다.
- MySQL은 기본적으로 재귀 깊이를 1000회로 제한하며,
cte_max_recursion_depth변수로 조정할 수 있습니다. - 성능을 위해서는
UNION ALL을 사용하고, 중복 제거가 꼭 필요한 경우에만UNION DISTINCT를 사용하는 것이 좋습니다.
마무리
재귀 CTE는 WITH RECURSIVE를 사용해 기저 사례와 재귀 사례로 구성된 자기 참조 쿼리를 정의합니다. MySQL에서 조직도나 카테고리 트리 같은 계층형 데이터 탐색, 숫자 시리즈 생성, 그래프 순회 작업에 필수적인 강력한 기능이므로 꼭 익혀두시기 바랍니다.
