재귀 CTE

재귀 CTE(Common Table Expression)는 자기 자신을 참조하는 CTE로, 계층형 데이터나 반복 구조를 처리할 때 유용합니다.

조직도, 사용자 추천 관계, 댓글 트리, 수열 생성 등 계층적 관계를 다루는 데 자주 사용됩니다.

재귀 CTE는 크게 두 부분으로 구성됩니다.

1. 앵커 멤버: 시작점(루트 행)을 가져오는 초기 쿼리
2. 재귀 멤버: CTE 자신을 참조해 결과를 확장하는 반복 쿼리

두 쿼리는 UNION ALL로 결합되며, 종료 조건을 만족할 때까지 반복됩니다.

복사
WITH RECURSIVE cte_name AS (
  -- 앵커 멤버: 시작점
  SELECT ...

  UNION ALL

  -- 재귀 멤버: 자기 자신을 참조
  SELECT ...
  FROM cte_name
  JOIN ...
  ON ...
)
SELECT * 
FROM cte_name;

• WITH RECURSIVE → 재귀 CTE 선언
• 종료 조건은 자동으로, 더 이상 새로운 행이 생성되지 않을 때 반복이 멈춥니다.

추천 관계를 저장한 referrals 테이블이 있다고 가정합니다.

referrals

Sofia부터 시작해, 직접 또는 간접적으로 추천받은 모든 사용자를 추적하는 쿼리는 다음과 같습니다.

복사
WITH RECURSIVE referral_chain AS (
  -- 앵커: 루트 사용자(Sofia)
  SELECT user_id, name, referred_by, 1 AS depth
  FROM referrals
  WHERE referred_by IS NULL

  UNION ALL

  -- 재귀: 자식 노드를 찾아 확장
  SELECT r.user_id, r.name, r.referred_by, rc.depth + 1
  FROM referrals r
  JOIN referral_chain rc
    ON r.referred_by = rc.user_id
)
SELECT *
FROM referral_chain
ORDER BY depth;

→ Sofia를 루트로 하는 추천 관계 트리를 단계별(depth)로 확인할 수 있습니다.

• UNION ALL: 중복 제거가 필요 없다면 UNION보다 빠릅니다.
• 종료 조건: 데이터에 유한한 계층이 있어야 무한 루프를 피할 수 있습니다.
• 호환성: MySQL(8.0+), PostgreSQL, SQLite 등은 지원하지만, 일부 DB에서는 다른 방식이 필요할 수 있습니다.