재귀 CTE: 이게 뭐고 왜 필요한지

오늘은 한 단계 더 나아가서 재귀의 마법을 다뤄볼 거야. 만약 너가 이미 Python 같은 재귀 지원 언어로 코딩해본 적 있다면, 대충 무슨 얘기인지 알 거야. 근데 혹시 이게 좀 신기하게 들려도 걱정하지 마! 아주 자세하게 풀어줄게.

재귀 CTE는 회사 조직도, 가족 트리, 파일 디렉토리처럼 계층적이고 트리 구조인 데이터를 다룰 때 쓰는 강력한 도구야.

쉽게 말하면, 자기 자신을 "호출"해서 데이터의 모든 레벨을 하나씩 순회하고 처리할 수 있는 그런 쿼리야.

재귀 CTE의 핵심 특징:

1. WITH RECURSIVE 키워드를 써.
2. 재귀 CTE는 두 부분으로 나뉘어:
   • 기본 쿼리: 재귀의 시작점(혹은 "루트")을 정해.
   • 재귀 쿼리: 이전 단계 결과를 써서 남은 데이터를 처리해.

재귀 CTE의 동작 방식은 계단을 오르는 거랑 비슷해:

• 먼저 첫 번째 계단에 올라 (이게 기본 쿼리야).
• 그 다음, 첫 계단 결과를 써서 두 번째 계단으로 올라 (재귀 쿼리).
• 이 과정을 계단이 끝날 때까지 반복해 (종료 조건 도달).

재귀 CTE 문법

바로 예시부터 보자:

WITH RECURSIVE cte_name AS (
    -- 기본 쿼리
    SELECT column1, column2
    FROM table_name
    WHERE condition_for_base_case

    UNION ALL

    -- 재귀 쿼리
    SELECT column1, column2
    FROM table_name
    JOIN cte_name ON some_condition
    WHERE stop_condition
)
SELECT * FROM cte_name;

재귀 CTE에서 UNION과 UNION ALL의 역할

모든 재귀 CTE는 반드시 기본 부분과 재귀 부분 사이에 UNION이나 UNION ALL 연산자를 써야 해.

어떤 연산자를 쓸까: UNION vs UNION ALL?

뭘 써야 할지 모르겠으면 — 거의 항상 UNION ALL을 써. 왜냐면 얘가 더 빨라: 그냥 결과를 합치기만 하고 중복 체크를 안 하거든. 즉, 계산도 적고, 리소스도 덜 들고, 결과도 더 빨라.

재귀 CTE에서는 이게 특히 중요해. 예를 들어 댓글 트리나 회사 조직 구조를 만들 때 — UNION ALL이 거의 필수야. 그냥 UNION을 쓰면, DB가 어떤 단계가 이미 나왔다고 착각해서 결과 일부를 "잘라버릴" 수 있어. 그러면 순회 로직이 망가져.

딱 중복이 진짜 문제고 꼭 없애야 할 때만 UNION을 써. 근데 항상 속도랑 깔끔함 사이에서 고민해야 해.

다른 방식 예시

-- UNION: 중복은 빠짐
SELECT 'A'
UNION
SELECT 'A';     -- 결과: 'A' 한 줄

-- UNION ALL: 중복도 남음
SELECT 'A'
UNION ALL
SELECT 'A';     -- 결과: 'A' 두 줄

재귀 쿼리에서는 중요한 단계를 놓치지 않으려면 항상 UNION ALL을 쓰는 게 안전해.

자주 나오는 예시를 보자: 직원 테이블에 employee_id, manager_id, name 컬럼이 있어. 여기서 사장(즉, manager_id = NULL인 사람)부터 시작해서 계층 구조를 만들어야 해.

직원 테이블이 있다고 치자: employees

누가 누구 밑에 있는지, 그리고 각 직원의 레벨이 뭔지 알아야 해. 이건 예를 들어 직원 트리를 UI에 보여주거나 팀 구조 리포트 만들 때 유용하지.

WITH RECURSIVE employee_hierarchy AS (
    -- 상사가 없는 사람부터 시작
    SELECT
        employee_id,
        name,
        manager_id,
        1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 부하 직원 추가하고 레벨 올리기
    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh
    ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

결과는 이렇게 나와:

이 쿼리는 사장부터 제일 아래 직원까지 계층을 "쭉" 순회하는 걸 잘 보여줘. level 컬럼은 트리 포맷팅이나 시각화할 때 딱 좋아.

예시: 상품 카테고리

이번엔 상품 카테고리 테이블이 있다고 해보자. 각 카테고리는 하위 카테고리를 가질 수 있고, 그 밑에도 또 하위 카테고리가 있을 수 있어. 이 트리 구조를 어떻게 만들까?

categories 테이블

재귀 쿼리:

WITH RECURSIVE category_tree AS (
    -- 기본: 루트 카테고리 찾기
    SELECT
        category_id,
        name,
        parent_id,
        1 AS depth
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- 재귀: 현재 카테고리의 하위 카테고리 찾기
    SELECT
        c.category_id,
        c.name,
        c.parent_id,
        ct.depth + 1
    FROM categories c
    INNER JOIN category_tree ct
    ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree;

결과:

이제 카테고리 트리랑 깊이 레벨이 한눈에 보여.

왜 재귀 CTE가 짱이냐?

재귀 CTE는 SQL에서 제일 표현력 있고 강력한 도구 중 하나야. 복잡한 중첩 로직 대신, 어디서 시작할지(기본), 어떻게 진행할지(재귀)만 쓰면 나머지는 PostgreSQL이 다 해줘.

이런 쿼리는 직원, 상품 카테고리, 디스크 디렉토리, 소셜 그래프 등 계층 구조 순회에 자주 써. 확장도 쉬워: 테이블에 새 데이터가 들어와도 쿼리가 알아서 다 포함해. 진짜 편하고 확장성도 좋아.

근데 함정도 있어. 종료 조건을 꼭 신경 써야 해 — 없으면 쿼리가 무한 루프에 빠질 수 있어. 인덱스도 중요해: 큰 테이블에서 인덱스 없이 돌리면 느려질 수 있어. 그리고 UNION ALL이 거의 항상 정답이야, 특히 계층 구조에서는. 중복 제거 때문에 재귀 단계가 날아갈 수 있으니까.

잘 짠 재귀 CTE는 복잡한 비즈니스 로직도 몇 줄로 표현할 수 있어 — 프로시저, 루프, 추가 코드 없이. 이럴 때 SQL이 진짜 멋지게 돌아간다니까.

재귀 CTE 쓸 때 흔한 실수

• 무한 재귀: 종료 조건(WHERE)을 제대로 안 걸면 쿼리가 무한 루프 돔.
• 불필요한 데이터: UNION ALL을 잘못 쓰면 중복이 쌓여.
• 성능 문제: 재귀 쿼리는 데이터가 많으면 무거워질 수 있어. 주요 컬럼(예: manager_id)에 인덱스 걸면 빨라져.

재귀 쿼리가 꼭 필요한 순간

재귀 쿼리가 뭔가 이론적인 것 같아도, 실제 개발에서 진짜 자주 써. 예를 들어:

• 회사 구조나 상품 분류 리포트 만들 때;
• 폴더 트리 순회해서 모든 하위 디렉토리 리스트 뽑을 때;
• 그래프 분석 — 소셜 네트워크, 경로, 작업 의존성 등;
• 복잡한 객체 관계를 보기 좋게 표현할 때.

뭔가가 다른 것에 의존하는 구조를 순회해야 한다면, WITH RECURSIVE가 거의 무조건 필요할 거야.