재귀 CTE
재귀 CTE(Common Table Expression)는 자기 자신을 참조하는 CTE로, 계층형 데이터나 반복 구조를 처리할 때 유용합니다.
조직도, 사용자 추천 관계, 댓글 트리, 수열 생성 등 계층적 관계를 다루는 데 자주 사용됩니다.
재귀 CTE의 구조
재귀 CTE는 크게 두 부분으로 구성됩니다.
- 앵커 멤버: 시작점(루트 행)을 가져오는 초기 쿼리
- 재귀 멤버: CTE 자신을 참조해 결과를 확장하는 반복 쿼리
두 쿼리는 UNION ALL로 결합되며, 종료 조건을 만족할 때까지 반복됩니다.
기본 구문
재귀 CTE 기본 문법
WITH RECURSIVE cte_name AS (
-- 앵커 멤버: 시작점
SELECT ...
UNION ALL
-- 재귀 멤버: 자기 자신을 참조
SELECT ...
FROM cte_name
JOIN ...
ON ...
)
SELECT *
FROM cte_name;
WITH RECURSIVE→ 재귀 CTE 선언- 종료 조건은 자동으로, 더 이상 새로운 행이 생성되지 않을 때 반복이 멈춥니다.
예시: 사용자 추천 체인 추적하기
추천 관계를 저장한 referrals 테이블이 있다고 가정합니다.
referrals
| user_id | name | referred_by |
|---|---|---|
| 1 | Sofia | NULL |
| 2 | Ethan | 1 |
| 3 | Aisha | 2 |
| 4 | Noah | 2 |
| 5 | Mia | 3 |
Sofia부터 시작해, 직접 또는 간접적으로 추천받은 모든 사용자를 추적하는 쿼리는 다음과 같습니다.
재귀 CTE 예시
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;
결과
| user_id | name | referred_by | depth |
|---|---|---|---|
| 1 | Sofia | NULL | 1 |
| 2 | Ethan | 1 | 2 |
| 3 | Aisha | 2 | 3 |
| 4 | Noah | 2 | 3 |
| 5 | Mia | 3 | 4 |
→ Sofia를 루트로 하는 추천 관계 트리를 단계별(depth)로 확인할 수 있습니다.
주의할 점
UNION ALL: 중복 제거가 필요 없다면UNION보다 빠릅니다.- 종료 조건: 데이터에 유한한 계층이 있어야 무한 루프를 피할 수 있습니다.
- 호환성: MySQL(8.0+), PostgreSQL, SQLite 등은 지원하지만, 일부 DB에서는 다른 방식이 필요할 수 있습니다.
다음 내용이 궁금하다면?
코드프렌즈 PLUS 멤버십 가입 or 강의를 등록해 주세요!