홈 › SQL 고급 › 04 / 10

계층 쿼리: CONNECT BY 와 재귀 CTE

섹션 6진행 0 / 10

2. 핵심 원리

2.1 재귀 CTE 의 구조

재귀 CTE 는 앵커(시작 행)와 재귀부(다음 단계)를 UNION ALL 로 잇습니다.

text

WITH RECURSIVE t (컬럼 목록) AS (
  앵커:  시작 행을 고르는 SELECT            -- 한 번만 실행
   UNION ALL
  재귀부: t 를 참조해 다음 단계 행을 붙이는 SELECT
)
SELECT ... FROM t;

실행 순서는 다음과 같습니다.

앵커를 실행해 첫 결과를 만듭니다.
직전 단계에서 나온 행을 t 로 보고 재귀부를 실행합니다.
재귀부가 새 행을 하나도 만들지 못하면 멈춥니다.

전개 깊이를 세는 lvl 컬럼을 앵커에서 1 로 시작해 재귀부에서 lvl + 1 로 키우는 것이 관례입니다. 대표 아래로 내려갈 때는 재귀부 조건이 e.mgr = t.id(내 부모가 직전 단계의 행), 아래에서 위로 올라갈 때는 e.id = up.mgr(내가 직전 단계 행의 부모)입니다.

핵심
재귀 CTE 는 조인 방향이 곧 전개 방향입니다. e.mgr = t.id 는 아래로, e.id = t.mgr 는 위로 갑니다. 멈추는 조건은 "더 붙일 행이 없음" 뿐이라, 순환 데이터에서는 스스로 멈추지 않습니다.

2.2 CONNECT BY 의 구성 요소

Oracle·Tibero 의 CONNECT BY 는 FROM 뒤에 계층 절을 붙이는 방식입니다.

구성 요소	뜻
`START WITH 조건`	시작 행(루트)을 고른다
`CONNECT BY PRIOR 자식 = 부모`	부모 행과 자식 행을 잇는 조건
`LEVEL`	깊이, 시작 행이 1
`SYS_CONNECT_BY_PATH(컬럼, 구분자)`	루트부터 현재 행까지의 경로 문자열
`CONNECT_BY_ROOT 컬럼`	현재 행의 루트 행 값
`CONNECT_BY_ISLEAF`	자식이 없으면 1
`ORDER SIBLINGS BY`	같은 부모의 형제끼리만 정렬
`NOCYCLE`·`CONNECT_BY_ISCYCLE`	순환을 무시하고, 순환 행을 표시

PRIOR 가 붙은 쪽이 "부모 행의 값"입니다. PRIOR id = mgr 는 부모의 id 가 내 mgr 인 행, 즉 자식을 찾으니 아래로 내려갑니다. 반대로 PRIOR mgr = id 는 부모 행의 mgr 이 내 id 인 행, 즉 상사를 찾으니 위로 올라갑니다.

2.3 DB 별 지원 범위

기능	Oracle·Tibero	MySQL	MSSQL
`CONNECT BY`	지원	없음	없음
재귀 CTE	Oracle 11gR2 부터	8.0 부터	2005 부터
`RECURSIVE` 키워드	쓰지 않는다	필수	쓰지 않는다
WITH 뒤 컬럼 목록	필수	선택	선택
재귀 깊이 한도	없음	1000	100

MySQL 의 한도는 cte_max_recursion_depth(기본 1000), MSSQL 은 OPTION (MAXRECURSION n) 힌트(기본 100, 0 은 무제한)로 바꿉니다. Oracle 의 재귀 WITH 는 CONNECT BY 에 없는 SEARCH DEPTH FIRST BY 와 CYCLE ... SET 절을 제공합니다.