재귀 CTE
재귀 CTE는 자기 자신을 참조하는 공통 테이블 식(CTE)입니다.
사용자 추천이나 조직도 같은 계층형 관계, 또는 수열 생성 같은 반복 로직을 다룰 때 유용합니다.
이 강의에서는 재귀 CTE를 사용해 계층형 또는 자기 참조 데이터를 처리하는 방법을 살펴봅니다.
재귀 CTE의 구성
재귀 CTE는 두 부분으로 이루어집니다.
- 앵커 멤버: 기저 사례(시작 행)
- 재귀 멤버: CTE 자신을 참조하며 앵커를 기반으로 확장하는 쿼리
두 부분은 UNION ALL로 결합합니다.
구문
재귀 CTE의 기본 구문은 다음과 같습니다.
재귀 CTE 구문
WITH RECURSIVE cte_name AS (
-- 앵커 멤버
SELECT ...
UNION ALL
-- 재귀 멤버
SELECT ...
FROM cte_name
JOIN ...
ON ...
)
SELECT * FROM cte_name;
재귀는 새 행이 더 이상 생성되지 않을 때까지 계속됩니다.
예시: 추천 체인
다음 쿼리는 누구에게도 추천받지 않은 루트 사용자부터 전체 추천 체인을 추적합니다.
referrals
| user_id | name | referred_by |
|---|---|---|
| 1 | Sofia | NULL |
| 2 | Ethan | 1 |
| 3 | Aisha | 2 |
| 4 | Noah | 2 |
| 5 | Mia | 3 |
재귀 CTE 예시
-- Sofia부터 시작하는 추천 체인을 추적하는 재귀 CTE
WITH RECURSIVE referral_chain AS (
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 |
이는 지민이 직접 혹은 간접적으로 추천한 모든 사용자와, 추천 체인에서의 깊이(depth)를 반환합니다.
참고 사항
- 중복 제거가 꼭 필요하지 않다면
UNION ALL을 사용하세요. - 항상 명확한 종료 조건이 존재하도록 하세요(예: 유한한 자식 행).
- 모든 데이터베이스가 재귀 CTE를 지원하는 것은 아닙니다 — 사용하는 SQL 엔진의 호환성을 확인하세요.
다음 내용이 궁금하다면?
코드프렌즈 PLUS 멤버십 가입 or 강의를 등록해 주세요!