513. 트리 구조와 계층형 쿼리 (CTE, WITH RECURSIVE)

⚠️ 이 문서는 "내 매니저는 누구고, 그 매니저의 매니저는 누구지?"처럼 데이터가 나무(Tree) 모양으로 끝없이 이어지는 족보(계층) 관계를 찾을 때, **끝을 알 수 없는 무한 조인(JOIN)의 늪에서 벗어나게 해주는 마법의 재귀 쿼리인 'CTE (WITH 절)'**를 다룹니다.

핵심 인사이트 (3줄 요약)

  1. 본질: 조직도, 카테고리, 댓글-대댓글처럼 '자신이 속한 테이블의 다른 데이터를 부모로 삼는(Self-Referencing)' 계층형 데이터를 탐색하기 위한 특수 SQL 문법이다.
  2. 해결책: 일반적인 JOIN으로는 깊이(Depth)가 얼마나 될지 모르는 트리를 탐색할 수 없으므로, 자기가 자기를 계속해서 부르는 재귀(Recursive) 함수처럼 작동하는 WITH RECURSIVE 절을 사용한다.
  3. 가치: 애플리케이션(Java) 메모리로 수만 건의 데이터를 몽땅 가져와서 for 문과 재귀 함수로 직접 족보를 짜맞추는 끔찍한 삽질을 쿼리 한 방으로 해결해 준다.

Ⅰ. 개요: 족보 찾기의 지옥 (Context & Necessity)

회사의 직원 테이블이 있다.

  • 사원: 철수 (매니저: 영희)
  • 대리: 영희 (매니저: 사장)
  • 사장: 민수 (매니저: 없음)

"철수의 제일 꼭대기 상사(사장님)가 누구인지 찾아봐!" 이걸 일반 쿼리로 짜려면 어떻게 해야 할까?

SELECT ... FROM 직원 e1 
JOIN 직원 e2 ON e1.매니저 = e2.이름 (1단계 상사)
JOIN 직원 e3 ON e2.매니저 = e3.이름 (2단계 상사) ...

만약 직급이 10단계까지 있다면 JOIN을 10번이나 써야 한다. 더 큰 문제는, 어떤 직원은 3단계 만에 사장님이 나오고 어떤 직원은 10단계 만에 사장님이 나오는데, 이걸 SQL의 고정된 JOIN 문법으로는 도저히 표현할 수가 없다는 것이다.

📢 섹션 요약 비유: 일반 JOIN은 **'미리 정해진 칸 수만큼만 올라갈 수 있는 엘리베이터'**와 같습니다. 3층을 누르면 3층까지만 가죠. 반면 재귀 CTE는 **'꼭대기가 나올 때까지 무한히 올라가는 에스컬레이터'**입니다. 끝이 어딘지 몰라도, 더 이상 올라갈 곳(매니저가 Null)이 없을 때까지 알아서 계속 타고 올라갑니다.

Ⅱ. CTE와 WITH RECURSIVE의 작동 원리 ★

CTE (Common Table Expression)는 쿼리 맨 위에 WITH로 시작해서 임시 테이블을 만드는 문법이다. 여기에 RECURSIVE를 붙이면 마법이 시작된다.

재귀 쿼리의 2대 핵심 구조

재귀 쿼리는 반드시 두 부분으로 나뉘고, 그사이를 UNION ALL로 묶는다.

  1. 앵커 멤버 (Anchor Member) - 시작점 찾기
    • 재귀를 시작할 **'뿌리(Root)'**를 먼저 1개 찾는다. (예: WHERE 매니저 IS NULL $\rightarrow$ 사장님 찾기)
  2. 재귀 멤버 (Recursive Member) - 꼬리 물기
    • 방금 찾은 결과(사장님)를 가지고, 다시 자기 자신(CTE 테이블)을 찔러서 **'사장님의 부하 직원'**을 찾는다.
    • 찾은 부하 직원을 가지고 또 찔러서 **'부하 직원의 부하 직원'**을 찾는다.
    • 더 이상 부하 직원이 없을 때까지 무한 반복한다.
-- [실전 쿼리 예시: 사장님부터 밑으로 내려가는 조직도 만들기]
WITH RECURSIVE 조직도 AS (
    -- 1️⃣ 앵커 멤버 (시작점: 사장님)
    SELECT 이름, 매니저, 1 AS 레벨 
    FROM 직원 WHERE 매니저 IS NULL
    
    UNION ALL
    
    -- 2️⃣ 재귀 멤버 (꼬리 물기: 부하 직원들)
    SELECT e.이름, e.매니저, c.레벨 + 1 
    FROM 직원 e
    JOIN 조직도 c ON e.매니저 = c.이름   -- (아까 찾은 상사랑 나를 연결!)
)
SELECT * FROM 조직도;

Ⅲ. 실무 팁: 무한 루프(Infinite Loop)의 공포

재귀 쿼리는 프로그래머의 재귀 함수와 똑같이 **'무한 루프'**에 빠질 위험이 있다.

  • 상황: 데이터를 잘못 넣어서, 철수의 상사가 영희고, 영희의 상사가 다시 철수가 되어버렸다. (순환 참조)
  • 대참사: 재귀 쿼리가 철수 $\rightarrow$ 영희 $\rightarrow$ 철수 $\rightarrow$ 영희를 무한정 찾아대면서 DB CPU를 100%로 갉아먹고 서버가 죽어버린다.
  • 해결책:
    1. MySQL에서는 기본적으로 cte_max_recursion_depth 옵션이 1,000으로 걸려있어, 1,000번 돌면 강제로 에러를 내고 멈춰준다. (안전장치)
    2. Oracle에서는 CONNECT BY NOCYCLE 이라는 치트키 키워드를 쓰면, 한 번 방문했던 노드(철수)를 다시 만나면 알아서 쿨하게 멈춰준다.
┌──────────────────────────────────────────────────────────────┐
│           계층형 쿼리(WITH RECURSIVE)의 트리 전개 시각화                │
├──────────────────────────────────────────────────────────────┤
│                                                              │
│ [ 👨‍💼 직원 테이블 (원본) ]                                        │
│ 철수(매니저:영희) / 영희(매니저:민수) / 민수(매니저:NULL)                │
│                                                              │
│ [ 🌲 재귀 쿼리 전개 과정 ]                                        │
│                                                              │
│ 1️⃣ 앵커: 매니저가 NULL인 사람 찾아!                                 │
│    ──▶ [ Lv 1. 민수 ] (사장님)                                 │
│                                                              │
│ 2️⃣ 재귀 1회차: 아까 찾은 '민수'가 매니저인 사람 찾아!                    │
│    ──▶ [ Lv 2. 영희 ] (대리)                                   │
│                                                              │
│ 3️⃣ 재귀 2회차: 아까 찾은 '영희'가 매니저인 사람 찾아!                    │
│    ──▶ [ Lv 3. 철수 ] (사원)                                   │
│                                                              │
│ 4️⃣ 재귀 3회차: 아까 찾은 '철수'가 매니저인 사람 찾아!                    │
│    ──▶ (아무도 없음. 탐색 종료!)                                  │
└──────────────────────────────────────────────────────────────┘

Ⅳ. 결론

"데이터의 높낮이(Hierarchy)를 평면의 테이블에 우아하게 담아내는 기술." RDBMS는 본질적으로 '평면적인 2차원 표'를 다루는 시스템이기 때문에, 트리나 그래프 같은 입체적인 족보 데이터를 저장하고 다루는 데는 쥐약이다. 과거에는 이를 해결하려고 자바 코드에서 for 문을 돌리거나, Oracle 전용 문법인 START WITH ... CONNECT BY에 의존해야 했다. 하지만 ANSI SQL 표준으로 WITH RECURSIVE 구문이 채택되면서, 이제 모든 백엔드 개발자들은 DB 벤더와 상관없이 단 한 번의 깔끔한 쿼리로 댓글의 대댓글, 카테고리의 하위 카테고리를 완벽하게 뽑아올 수 있는 강력한 무기를 얻게 되었다.

📌 관련 개념 맵

  • 관련 데이터 구조: Tree (트리), Graph (그래프 DB - 468번 문서)
  • 대안 아키텍처: 인접 리스트(Adjacency List), 경로 열거(Path Enumeration), 클로저 테이블(Closure Table) (RDB에서 트리를 저장하는 다양한 설계 패턴들)
  • SQL 키워드: WITH RECURSIVE, UNION ALL, CONNECT BY (Oracle)
  • 관련 연산: 조인 종속 (자기가 자기를 참조하는 Self Join 사용)

👶 어린이를 위한 3줄 비유 설명

  1. "철수네 할아버지의 할아버지의 할아버지가 누구야?"라고 물어봤어요.
  2. 일반 조인(JOIN)은 "할아버지 찾기 카드를 3장만 줄게. 3명까지만 물어보고 와"라고 한계를 정해놓는 거예요. (끝까지 못 찾을 수도 있죠)
  3. WITH RECURSIVE(재귀)는 "가장 처음 조상님(원숭이?)이 나올 때까지 묻고 묻고 또 물어봐!"라고 한계 없이 계속 꼬리를 물고 찾아가게 허락해 주는 마법의 주문이랍니다!