[TIL] SQL - WITH RECURSIVE 개념과 사용법

WITH RECURSIVE

WITH RECURSIVE는 재귀 CTE(Common Table Expression)를 생성하는 SQL 문법이다.
반복 호출을 통해 계층적 데이터나 연속적인 숫자 목록을 쉽게 만들 수 있다.

 

WITH RECURSIVE 기본 문법

WITH RECURSIVE cte_name AS (
    -- 기저 조건 (Base Case)
    SELECT 초기값
    
    UNION ALL
    
    -- 재귀 조건 (Recursive Case)
    SELECT 재귀적으로 증가할 값 FROM cte_name WHERE 종료 조건
)
SELECT * FROM cte_name;

 

WITH RECURSIVE가 필요한 이유

• 연속된 숫자 목록(시퀀스) 생성 → 0~23시간 범위 생성 가능
• 계층 구조(트리 구조) 데이터 처리 → 예: 회사 조직도, 카테고리 구조
• 반복적인 쿼리를 단순화 → 재귀적으로 데이터 계산 가능

 

WITH RECURSIVE 예제

0~23까지 숫자 생성

WITH RECURSIVE hour_table AS (
    SELECT 0 AS hour
    UNION ALL
    SELECT hour + 1 FROM hour_table WHERE hour < 23
)
SELECT * FROM hour_table;

📌 실행 과정:

1. 초기값: 0을 hour로 시작
2. 재귀 호출: hour + 1을 계속 증가시키며 hour < 23까지 실행
3. 결과: 0, 1, 2, ..., 23 생성

 

조직도 트리 구조 조회

WITH RECURSIVE employee_hierarchy AS (
    -- 초기값 (최상위 관리자)
    SELECT employee_id, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 재귀적으로 하위 직원 탐색
    SELECT e.employee_id, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

📌 실행 과정:

1. manager_id IS NULL인 최상위 관리자부터 시작
2. JOIN을 활용해 직속 부하 직원들을 재귀적으로 탐색
3. level 값을 1씩 증가시키며 조직도를 계층적으로 조회

 

WITH RECURSIVE의 주의점

• 종료 조건이 없으면 무한 루프 발생 → WHERE hour < 23 같은 종료 조건 필수
• 일반적으로 UNION ALL을 사용 → UNION은 중복 제거로 인해 성능 저하 가능
• MySQL에서는 기본적으로 최대 재귀 깊이가 1000 → 필요 시 SET max_recursive_depth = 값; 설정 가능

 

WITH RECURSIVE를 활용한 문제 풀이

2025.02.06 - [Algorithm Solving/SQL] - [programmers] SQL Lv. 4 - 입양 시각 구하기(2)

[programmers] SQL Lv. 4 - 입양 시각 구하기(2)

 

 

참고: https://inpa.tistory.com/entry/MYSQL-%F0%9F%93%9A-RECURSIVE-%EC%9E%AC%EA%B7%80-%EC%BF%BC%EB%A6%AC