실행 가능한 SQLite 문서: 재귀 CTE

Q: SQLite에서 재귀 CTE가 뭔가요?

재귀 CTE는 자기 자신을 반복 참조해서 결과 집합을 만들어내는 WITH RECURSIVE 쿼리입니다. UNION ALL로 두 부분이 연결되는데, 시작 행을 만드는 앵커(anchor) 쿼리와, 직전 단계의 결과로부터 새 행을 뽑아내는 재귀(recursive) 쿼리로 구성됩니다. SQLite는 재귀 쿼리가 더 이상 새 행을 만들지 않을 때까지 이 과정을 반복합니다.

Q: WITH RECURSIVE는 어떤 상황에 쓰면 좋나요?

트리나 그래프를 따라 내려가야 할 때 가장 잘 맞습니다. 예를 들면 직원-매니저 관계, 카테고리-서브카테고리, 댓글의 대댓글 구조 같은 거죠. 또 특정 범위의 모든 날짜를 만든다거나 1부터 100까지 숫자를 채우는 등 시퀀스 생성에도 유용합니다. 한두 단계 정도라면 일반 JOIN으로도 충분하지만, 깊이를 미리 알 수 없을 만큼 들어가야 한다면 재귀 CTE가 정답입니다.

Q: 재귀 CTE에서 무한 루프는 어떻게 막나요?

재귀 쿼리에 반드시 종료 조건을 넣어야 합니다. 결국 0행을 반환하게 되는 WHERE 조건이나, 일정 값에서 멈추는 카운터를 두는 식이죠. 사이클이 있을 수 있는 그래프라면 지금까지 방문한 경로를 별도 컬럼에 누적해 두고, 이미 들른 노드는 제외해야 합니다. 그리고 안전장치로 바깥쪽 쿼리에 LIMIT을 걸어두면, 만에 하나 폭주하더라도 메모리가 터지는 사고는 막을 수 있습니다.

SQL에서 재귀라니 이상하게 들리지만, 직접 보면 이해됩니다

대부분의 쿼리는 이미 존재하는 데이터에서 행을 가져옵니다. 그런데 SQLite 재귀 CTE는 다릅니다. 자신의 출력 결과를 다시 입력으로 받아서 한 단계씩 행을 만들어 나가고, 더 이상 새로 추가될 게 없을 때까지 반복합니다. 깊이를 알 수 없는 트리를 따라 내려가거나, 별도의 숫자 테이블 없이 1부터 100까지의 값을 만들어내는 작업이 바로 이런 방식으로 가능해집니다.

WITH RECURSIVE의 구조는 항상 동일합니다:

WITH RECURSIVE name(columns) AS (
    -- 앵커: 시작 행
    SELECT ...
    UNION ALL
    -- 재귀: 이전 단계에서 파생된 행
    SELECT ... FROM name WHERE ...
)
SELECT * FROM name;

맨 위에 앵커, 그다음 UNION ALL, 그 아래에 재귀 쿼리. SQLite는 앵커 부분을 한 번 실행한 뒤, 직전 라운드에서 만들어진 행을 입력으로 삼아 재귀 부분을 반복 실행합니다. 새 행이 더 이상 나오지 않으면 거기서 멈춰요.

1부터 10까지 숫자 시퀀스 생성하기

가장 간단한 SQLite 재귀 CTE 예제는 숫자 시퀀스를 만드는 겁니다. 테이블도 필요 없어요:

WITH RECURSIVE counter(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM counter WHERE n < 10
)
SELECT n FROM counter;

동작을 한 단계씩 따라가 보겠습니다.

앵커 부분이 행 하나를 만들어 냅니다: n = 1.
재귀 단계는 그 행을 받아 n + 1 = 2를 계산하고, 2 < 10이 참이므로 결과에 포함시킵니다.
다음 반복에서는 n = 2를 받아 n = 3을 만들어 냅니다. 이런 식으로 계속 이어집니다.
n이 10에 도달하면 10 < 10이 거짓이 되어 재귀 단계가 더 이상 행을 반환하지 않고, SQLite는 여기서 멈춥니다.

여기서 WHERE n < 10이 바로 종료 조건입니다. 이 조건이 빠지면 쿼리는 무한히 돌게 되니 주의하세요.

SQLite로 날짜 범위 생성하기

원리는 똑같지만 실무 리포트에서 꽤 유용합니다. 특정 기간 안의 모든 날짜를 채워 넣어, 아무 일도 없었던 날까지 빠짐없이 표시하는 용도로 자주 씁니다.

WITH RECURSIVE days(d) AS (
    SELECT date('2024-01-01')
    UNION ALL
    SELECT date(d, '+1 day') FROM days WHERE d < '2024-01-07'
)
SELECT d FROM days;

이걸 이벤트 테이블과 LEFT JOIN해서 사용하면 이벤트가 없는 날도 0으로 정확히 집계할 수 있습니다. 그냥 GROUP BY date만 쓰면 이벤트가 없는 날은 아예 결과에서 빠져버리지만, 날짜 시퀀스를 미리 만들어두면 매일 한 행씩 보장되죠.

부모-자식 트리 순회하기

재귀 CTE의 대표적인 활용 사례입니다. 아래는 각 행이 자신의 관리자를 가리키는 직원 테이블 예시입니다:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

WITH RECURSIVE org(id, name, manager_id, depth) AS (
    SELECT id, name, manager_id, 0
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, org.depth + 1
    FROM employees e
    JOIN org ON e.manager_id = org.id
)
SELECT printf('%.*c%s', depth * 2, ' ', name) AS tree
FROM org;

앵커 부분에서는 루트(매니저가 없는 사람)를 골라냅니다. 재귀 단계에서는 employees 테이블을 다시 CTE와 조인하면서, manager_id가 이미 CTE에 들어 있는 id와 일치하는 행들을 찾아냅니다. 매번 반복할 때마다 한 단계씩 더 깊이 내려가는 거죠. depth는 출력할 때 들여쓰기 용도로 붙여둔 단순한 카운터입니다.

이 방식은 깊이에 상관없이 모든 트리에 그대로 통합니다. 2단계든 10단계든 쿼리는 바뀌지 않습니다.

특정 행의 모든 상위 계층(조상) 찾기

이번엔 방향을 뒤집어 봅시다. 루트에서 아래로 내려가는 대신, 특정 직원에서 시작해 매니저 체인을 따라 위로 거슬러 올라가 전체 보고 라인을 찾아내는 방식입니다:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

WITH RECURSIVE chain(id, name, manager_id) AS (
    SELECT id, name, manager_id
    FROM employees
    WHERE name = 'Diego'
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN chain ON chain.manager_id = e.id
)
SELECT name FROM chain;

앵커는 시작 사원이고, 재귀 단계마다 한 단계씩 부모(매니저)로 거슬러 올라갑니다. 그러다가 루트에 도달하면 manager_id IS NULL이 되어 조인 결과가 비어버리고, SQLite는 거기서 재귀를 멈춥니다.

이 패턴은 브레드크럼, 댓글 스레드, 카테고리 경로처럼 "맨 위까지 거슬러 올라가야 하는" 모든 상황에 유용합니다.

종료 조건과 무한 루프 방지

재귀 CTE에서 가장 흔히 만나는 버그가 바로 종료 조건을 빠뜨리거나, 작성은 했지만 절대 발동하지 않게 만든 경우입니다. 다음 두 예시를 비교해 보세요.

-- 無限に実行されます:
WITH RECURSIVE bad(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM bad
)
SELECT n FROM bad;

WHERE 절이 빠져 있어서 결과가 0행이 되는 시점이 영영 오지 않습니다. SQLite는 그저 묵묵히 무한대까지 세려고 들겠죠.

방어적으로 익혀둘 습관 두 가지입니다.

재귀 파트(recursive part)에는 항상 증가 범위를 제한하는 WHERE 절을 둡니다.
개발 중에는 바깥 SELECT에 LIMIT을 안전장치로 걸어두세요. 종료 조건을 잘못 잡았더라도 쿼리는 어쨌든 끝나게 됩니다.

WITH RECURSIVE counter(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM counter
)
SELECT n FROM counter LIMIT 5;

CTE 자체에는 종료 조건이 없지만, 바깥 쿼리의 LIMIT 5가 일찍 끊어 줍니다. SQLite는 똑똑해서 LIMIT이 요구하는 것 이상으로 재귀를 계속 돌리지 않습니다. 탐색 용도로는 유용하지만, 실제 운영 코드에서는 제대로 된 종료 조건을 대신할 수 없습니다.

그래프의 순환 처리

트리에는 순환이 있을 수 없지만, 일반 그래프에는 있을 수 있습니다. 데이터에 순환이 하나라도 있으면 단순한 재귀 CTE는 무한 루프에 빠지죠. 해결책은 지금까지 지나온 경로를 함께 들고 다니면서 이미 방문한 노드는 다시 들르지 않도록 막는 겁니다:

CREATE TABLE edges (a INTEGER, b INTEGER);
INSERT INTO edges VALUES (1, 2), (2, 3), (3, 1), (3, 4);

WITH RECURSIVE walk(node, path) AS (
    SELECT 1, ',1,'
    UNION ALL
    SELECT e.b, path || e.b || ','
    FROM edges e
    JOIN walk ON e.a = walk.node
    WHERE instr(path, ',' || e.b || ',') = 0
)
SELECT node, path FROM walk;

path는 이미 방문한 노드들을 쉼표로 이어 붙인 문자열입니다. 새 노드를 추가하기 전에 WHERE 절에서 해당 노드가 이 문자열에 들어 있는지 확인하죠. 이 안전장치가 없다면 1 → 2 → 3 → 1 같은 순환 구조에서 쿼리가 무한 루프에 빠집니다.

SQL에는 "방문한 노드 집합" 같은 내장 기능이 없습니다. 그래서 직접 만들어야 하는데, 보통은 문자열로 누적하거나 지금까지의 CTE 결과에 조인하는 방식을 씁니다.

재귀 CTE vs 셀프 조인

깊이가 한두 단계면 충분한 경우라면, 셀프 조인이 훨씬 간단하고 빠릅니다:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'Ada',    NULL),
    (2, 'Boris',  1),
    (3, 'Carla',  1),
    (4, 'Diego',  2),
    (5, 'Elena',  2),
    (6, 'Farouk', 3);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

여기까지가 "각 사람의 직속 매니저가 누구인지" 정도까지 다루는 방법이다. 그런데 "Ada 밑으로 몇 단계든 상관없이 그녀에게 보고하는 모든 사람"을 뽑아야 한다면 — 깊이를 알 수 없으니 — 깔끔하게 처리할 수 있는 건 재귀 CTE뿐이다. 필요한 깊이에 맞춰 도구를 고르자:

깊이가 작고 고정돼 있을 때: self join을 두세 번 정도.
깊이를 알 수 없거나 임의의 깊이일 때: WITH RECURSIVE.

멘탈 모델

SQLite 재귀 CTE는 선언적으로 작성하는 반복문이라고 보면 된다.

앵커(anchor) 는 루프의 초기값이다.
재귀 쿼리(recursive query) 는 루프 본문이다 — 현재 행들을 받아 다음 행 묶음을 만들어 낸다.
종료 조건(stopping condition) 은 루프의 탈출 조건이다 — 반환되는 행이 0개가 되면 루프가 끝난다.
UNION ALL 은 그 모든 결과를 모아 최종 결과 집합으로 누적한다.

이 그림이 머릿속에 들어오는 순간, 문법이 더 이상 낯설지 않다. SQL로 for 루프를 쓰고 있는 셈이다.

다음 단계: 인덱스

재귀 CTE는 많은 행을 훑고, 재귀 단계 안의 조인은 매 반복마다 실행된다. 조인에 쓰이는 컬럼에 인덱스가 없으면 성능은 순식간에 무너진다. 다음 장에서 다룰 주제가 바로 인덱스이고, manager_id 같은 컬럼이야말로 인덱스의 혜택을 가장 크게 보는 대표적인 예다.

자주 묻는 질문

SQLite에서 재귀 CTE가 뭔가요?

재귀 CTE는 자기 자신을 반복 참조해서 결과 집합을 만들어내는 WITH RECURSIVE 쿼리입니다. UNION ALL로 두 부분이 연결되는데, 시작 행을 만드는 앵커(anchor) 쿼리와, 직전 단계의 결과로부터 새 행을 뽑아내는 재귀(recursive) 쿼리로 구성됩니다. SQLite는 재귀 쿼리가 더 이상 새 행을 만들지 않을 때까지 이 과정을 반복합니다.

WITH RECURSIVE는 어떤 상황에 쓰면 좋나요?