계층 쿼리: CONNECT BY 와 재귀 CTE

4. 응용 변형 예제

소스: sql-src/adv_04_hierarchy/02_cycle_limit.sql, 03_dialects.sql. 02 는 김대표와 남본부가 서로를 상사로 가리키는 순환 데이터 4행, 03 은 3행을 씁니다.

변형 1: 순환 데이터에서 깊이 제한으로 멈추기

02 의 데이터는 김대표(1)의 mgr 가 2, 남본부(2)의 mgr 가 1 인 순환입니다. 제한 없이 재귀하면 끝나지 않으므로 재귀부에 WHERE t.lvl < 5 를 답니다.

WITH RECURSIVE t (id, name, lvl) AS (
  SELECT id, name, 1
    FROM emp
   WHERE id = 1
   UNION ALL
  SELECT e.id, e.name, t.lvl + 1
    FROM emp e
    JOIN t ON e.mgr = t.id
   WHERE t.lvl < 5
)
SELECT
       lvl
     , name
  FROM t
 ORDER BY lvl, id;

LVL | NAME
----+-------
1   | 김대표
2   | 남본부
3   | 김대표
3   | 박팀장
4   | 남본부
4   | 서팀장
5   | 김대표
5   | 박팀장
(8행)

김대표와 남본부가 번갈아 반복해 나오다가 5단계에서 잘립니다. 멈추기는 했지만 같은 사람이 여러 번 나오는 잘못된 결과입니다. 깊이 제한은 무한 반복을 막는 안전장치이고, 순환을 걸러 내는 방법은 아닙니다.

변형 2: 경로 문자열로 순환 차단

이미 지나온 사원을 다시 만나면 그 가지를 끊는 방법입니다. 경로를 /김대표/남본부/ 처럼 양쪽에 / 를 붙여 저장하고, 다음 사원의 이름이 경로에 이미 있는지 검사합니다.

WITH RECURSIVE t (id, name, lvl, path) AS (
  SELECT id, name, 1, CAST('/' || name || '/' AS VARCHAR(200))
    FROM emp
   WHERE id = 1
   UNION ALL
  SELECT e.id, e.name, t.lvl + 1, CAST(t.path || e.name || '/' AS VARCHAR(200))
    FROM emp e
    JOIN t ON e.mgr = t.id
   WHERE POSITION('/' || e.name || '/' IN t.path) = 0
)
SELECT
       lvl
     , name
     , path
  FROM t
 ORDER BY lvl, id;

LVL | NAME   | PATH
----+--------+------------------------------
1   | 김대표 | /김대표/
2   | 남본부 | /김대표/남본부/
3   | 박팀장 | /김대표/남본부/박팀장/
4   | 서팀장 | /김대표/남본부/박팀장/서팀장/
(4행)

남본부의 자식으로 김대표가 나오려 하면 경로에 /김대표/ 가 이미 있어 걸러집니다. 앞뒤 / 를 붙이는 이유는 김 처럼 이름 일부가 다른 이름에 포함돼 오탐하는 것을 막기 위해서입니다.

문자열 검색 함수는 DB 마다 이름이 다릅니다. POSITION(부분 IN 문자열) 은 표준이고 H2 에서 실행되며, Oracle·Tibero 는 INSTR, MySQL 은 LOCATE·INSTR, MSSQL 은 CHARINDEX 입니다.

실무에서는 이름보다 id 를 경로에 쓰는 편이 동명이인에 안전합니다.

변형 3: CONNECT BY 의 위로 전개·ROOT·ISLEAF (Oracle·Tibero)

PRIOR 위치만 바꾸면 방향이 바뀝니다.

-- Oracle · Tibero
SELECT
       LEVEL
     , name
  FROM emp
 START WITH id = 7
CONNECT BY PRIOR mgr = id;

도식(H2 미지원, 실행 결과 아님).

LEVEL | NAME
------+-------
1     | 강민호
2     | 박팀장
3     | 남본부
4     | 김대표

예제 2 의 재귀 CTE 결과와 같은 행입니다. SELECT CONNECT_BY_ROOT name, CONNECT_BY_ISLEAF 처럼 붙이면 루트 값(START WITH 행의 값)과 말단 여부(자식이 없으면 1)를 각 행에 함께 얻습니다.

주의

CONNECT_BY_ROOT·CONNECT_BY_ISLEAF 는 Oracle 10g 부터입니다. 그 이전 버전의 쿼리를 옮길 때는 이 기능을 쓸 수 없습니다.

변형 4: WHERE 와 CONNECT BY 조건의 차이 (Oracle·Tibero)

CONNECT BY 계층은 먼저 전개되고, 그 뒤에 WHERE 가 행을 거릅니다. 그래서 WHERE dept = '개발' 은 전개된 행 중 개발 부서 행만 남길 뿐 가지를 자르지 않습니다. 가지 전체를 자르려면 CONNECT BY 조건에 씁니다.

-- Oracle · Tibero
SELECT
       LEVEL
     , name
     , dept
  FROM emp
 START WITH mgr IS NULL
CONNECT BY PRIOR id = mgr
       AND dept = '개발'
 ORDER SIBLINGS BY name;

도식(H2 미지원, 실행 결과 아님).

LEVEL | NAME   | DEPT
------+--------+-----
1     | 김대표 | 경영
2     | 남본부 | 개발
3     | 박팀장 | 개발
4     | 강민호 | 개발
4     | 노지은 | 개발
3     | 서팀장 | 개발
4     | 문서준 | 개발

영업 부서인 이본부는 자식으로 이어지는 조건을 통과하지 못해 그 아래 최팀장·오하늘·임도윤까지 통째로 빠집니다. 시작 행인 김대표는 조건 검사 없이 들어가서 경영 부서인데도 남습니다.

변형 5: 순환 처리와 Oracle 재귀 WITH (문법 검토만)

CONNECT BY 는 순환 데이터를 만나면 오류로 멈춥니다. NOCYCLE 을 붙이면 순환하는 행에서 전개를 끊고, CONNECT_BY_ISCYCLE 이 그런 행을 1 로 표시합니다(10g 부터).

쓰는 위치는 CONNECT BY NOCYCLE PRIOR id = mgr 이고, SELECT 에 CONNECT_BY_ISCYCLE 을 넣으면 순환 행이 1 로 표시됩니다.

Oracle 11gR2 의 재귀 WITH 는 RECURSIVE 없이, WITH 뒤 컬럼 목록을 반드시 씁니다. 순환 검사와 깊이 우선 정렬은 전용 절이 맡습니다.

문법은 WITH t (컬럼 목록) AS (앵커 UNION ALL 재귀부) SEARCH DEPTH FIRST BY name SET seq CYCLE id SET is_cycle TO 'Y' DEFAULT 'N' SELECT ... 입니다.

SEARCH DEPTH FIRST BY 는 트리 순서를 seq 컬럼에 담아 ORDER BY seq 로 정렬하게 하고, CYCLE ... SET 은 순환 행을 표시하고 전개를 끊습니다. 이 두 절은 H2 에서 실행되지 않아 문법 검토만 합니다.

변형 6: SET MODE 별 재귀 CTE 실행

03 파일에서 세 모드와 Regular 모드로 같은 재귀 CTE 를 실행했습니다.

• WITH RECURSIVE 는 Regular·Oracle·MySQL·MSSQLServer 네 모드에서 모두 실행됩니다(결과 동일).
• RECURSIVE 를 뺀 WITH t (...) AS (...) 는 Regular·Oracle 모드에서 오류입니다. 실제 Oracle·MSSQL 은 이 형태를 쓰는데 H2 에서 재현 안 됩니다.

오류 메시지는 Table "T" not found 입니다.

MySQL 에서는 반대로 RECURSIVE 가 필수입니다. 그래서 세 DB 에 그대로 옮겨 실행되는 재귀 CTE 문장은 없고, RECURSIVE 키워드 하나는 옮길 때 항상 고쳐야 합니다.

MySQL 은 문장 앞에 RECURSIVE 만 붙이면 되고, MSSQL 은 RECURSIVE 없이 쓰고 문장 끝에 OPTION (MAXRECURSION 200) 을 붙여 한도를 올립니다.

깊이 제한과 재귀 한도는 다릅니다. lvl < 10 은 쿼리 논리이고, MySQL 의 cte_max_recursion_depth(1000)·MSSQL 의 MAXRECURSION(100)은 DB 가 강제하는 안전 한도입니다. 초과하면 오류가 나므로 깊은 트리는 한도를 올립니다.

변형 7: 숫자·달력 생성

재귀 CTE 는 테이블 없이 연속 숫자와 날짜를 만드는 데도 씁니다. 1~12월 시작일을 만드는 예입니다.

WITH RECURSIVE m (n) AS (
  SELECT 1
   UNION ALL
  SELECT n + 1
    FROM m
   WHERE n < 12
)
SELECT
       n AS month_no
     , DATEADD(MONTH, n - 1, DATE '2024-01-01') AS month_start
  FROM m
 ORDER BY n;

결과는 month_no 1~12 와 month_start 2024-01-01 ~ 2024-12-01 의 12행입니다(sql 파일에서 확인).

DATEADD(MONTH, ...) 는 MSSQL·H2 형식입니다. Oracle 은 ADD_MONTHS, MySQL 은 DATE_ADD(... INTERVAL n MONTH) 로 바꿉니다. 중급 10 의 날짜 채우기도 이 패턴입니다.