“ 매주 목요일마다 당신이 항상 하던대로 신발끈을 묶으면 신발이 폭발한다고 생각해보라.
컴퓨터를 사용할 때는 이런 일이 항상 일어나는데도 아무도 불평할 생각을 안 한다. ”- Jef Raskin
맥의 아버지 - 애플컴퓨터의 매킨토시 프로젝트를 주도
반응형
SQL 재귀 쿼리 완전 정리
PostgreSQL · Oracle · MySQL 8.0 기준으로 재귀 CTE와 CONNECT BY를 조직도 예제로 설명하며 무한 재귀 원인과 방지 전략, 경로·깊이·상하위 탐색 패턴을 자세히 정리합니다.
목차
재귀 쿼리
재귀 쿼리는 자기 자신을 참조하여 계층 구조를 단계적으로 확장하는 질의 방식입니다. 반복적인 자기 조인을 줄이고, 깊이가 달라져도 동일 패턴으로 전체 트리를 탐색합니다. 표준 SQL에서는 WITH RECURSIVE CTE 형태로 제공되며, Oracle은 추가로 CONNECT BY 구문을 제공합니다.
핵심 개념 앵커(초기 집합), 재귀(자기참조), 종료(새 행이 없을 때) 조합으로 트리를 완성합니다. 데이터가 바뀌어도 쿼리 구조가 유지되므로 재사용성이 높습니다.
재귀 쿼리가 필요한 이유
조직도, 제품 BOM, 댓글 트리, 폴더 구조처럼 부모와 자식 관계가 연쇄적으로 이어지는 데이터는 깊이가 일정하지 않습니다. 전통적인 조인은 깊이만큼 FROM/JOIN 절을 늘려야 하며, 깊이가 변하면 쿼리를 다시 작성해야 합니다. 재귀 쿼리는 동일한 템플릿으로 모든 깊이를 순회하므로 유지보수 비용을 안정적으로 낮춥니다.
- 유연성: 깊이를 모를 때도 전체 트리를 안정적으로 순회합니다.
- 가독성: 앵커와 재귀 단계가 분리되어 의도가 명확합니다.
- 확장성: 경로, 깊이, 루트·말단 여부 등 부가 정보를 쉽게 도출합니다.
재귀 CTE 동작 모델
재귀 CTE는 WITH RECURSIVE cte AS (앵커 UNION ALL 재귀) 형태로 작성합니다. 내부 실행은 다음과 같습니다.
- 앵커 단계에서 루트 후보를 한 번 조회하여 작업 집합에 적재합니다.
- 재귀 단계에서 직전 단계 결과를 입력으로 사용해 자식 행을 찾고 누적합니다.
- 새로운 행이 더 이상 생성되지 않으면 반복이 종료되고 누적 결과를 반환합니다.
실무 포인트
- UNION ALL vs UNION: ALL은 중복 제거가 없어 빠릅니다. 중복이나 순환 가능성이 있으면
UNION또는 별도 차단 로직을 사용합니다. - 정렬: 출력 순서는 엔진 구현에 좌우됩니다. 사용자 표시용 순서가 필요하면
ORDER BY를 명시합니다. - 인덱스: 재귀부 조인 키(
manager_id등)에 인덱스가 없으면 반복 스캔 비용이 급증합니다. - 깊이 제한: 오류나 순환으로 인한 증폭을 막기 위해
lvl < N같은 제한을 둡니다.
무한 재귀 원인과 방지
원인
- 종료 조건이 없어 탐색이 끝나지 않는 경우
- A→B→C→A처럼 동일 노드를 재방문하는 순환 데이터
- 동일 노드가 여러 경로에서 반복 추가되는 중복 누적
방지 전략
lvl기반 깊이 제한- 경로 문자열 또는 배열에 방문 이력 기록 후 재방문 차단
UNION으로 중복 제거 또는 표준CYCLE절 사용(지원 DBMS)- Oracle
CONNECT BY NOCYCLE, PostgreSQL·OracleSEARCH/CYCLE활용
예제 스키마와 데이터
직원 조직도 예시를 사용합니다. 한 직원은 0명 또는 1명의 상사를 가지며 상사가 없으면 루트입니다.
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50),
manager_id INT
);
INSERT INTO employees VALUES
(1,'김하늘',NULL),
(2,'박준호',1),
(3,'이서연',1),
(4,'최민수',2),
(5,'장예린',2);
CREATE INDEX ix_employees_manager ON employees(manager_id);핵심 키는 manager_id입니다. 이 값으로 부모에서 자식으로 탐색하므로 인덱스를 반드시 생성합니다.
PostgreSQL 작성법
기본 재귀 CTE
WITH RECURSIVE org(emp_id, name, manager_id, lvl) AS (
SELECT emp_id, name, manager_id, 1
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.name, e.manager_id, o.lvl + 1
FROM employees e
JOIN org o ON e.manager_id = o.emp_id
)
SELECT emp_id,name,manager_id,lvl
FROM org
ORDER BY lvl, emp_id;설명
- 앵커는 루트 직원을 가져옵니다.
- 재귀부는
e.manager_id = o.emp_id로 부모와 자식을 연결합니다. lvl은 깊이 계산과 깊이 제한 조건에 활용합니다.- 표시 순서가 필요하면
ORDER BY를 명시합니다.
경로 누적과 말단 판별
WITH RECURSIVE org(emp_id, name, manager_id, path, lvl) AS (
SELECT emp_id, name, manager_id, name::text, 1
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.name, e.manager_id,
org.path || ' > ' || e.name,
org.lvl + 1
FROM employees e
JOIN org ON e.manager_id = org.emp_id
)
SELECT o.*,
NOT EXISTS (SELECT 1 FROM employees c WHERE c.manager_id = o.emp_id) AS is_leaf
FROM org o
ORDER BY lvl, emp_id;설명
path는 루트에서 현재까지의 경로를 문자열로 누적합니다.NOT EXISTS로 자식이 없는 노드를 말단으로 식별합니다.- 경로 문자열은 트리 렌더링과 브레드크럼 생성에 유용합니다.
SEARCH/CYCLE 절
WITH RECURSIVE org(emp_id, manager_id) AS (
SELECT emp_id, manager_id FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.manager_id FROM employees e JOIN org o ON e.manager_id = o.emp_id
)
SEARCH BREADTH FIRST BY emp_id SET bfs_ord
CYCLE emp_id SET is_cycle TO TRUE DEFAULT FALSE
SELECT emp_id, manager_id, bfs_ord, is_cycle FROM org;탐색 순서를 자동 컬럼으로 부여하고, 순환을 표준 문법으로 탐지할 수 있습니다.
Oracle 작성법
표준 재귀 CTE
WITH org (emp_id, name, manager_id, lvl, path) AS (
SELECT emp_id, name, manager_id, 1, CAST(name AS VARCHAR2(4000))
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.name, e.manager_id,
o.lvl + 1,
o.path || ' > ' || e.name
FROM employees e
JOIN org o ON e.manager_id = o.emp_id
)
SELECT emp_id, name, manager_id, lvl, path
FROM org
ORDER BY lvl, emp_id;CONNECT BY 계층 쿼리
SELECT
emp_id, name, manager_id, LEVEL AS lvl,
SYS_CONNECT_BY_PATH(name, ' > ') AS path,
CONNECT_BY_ISLEAF AS is_leaf
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY emp_id;LEVEL은 깊이를 자동 제공합니다.ORDER SIBLINGS BY로 형제 정렬 기준을 지정합니다.- 순환 데이터는
NOCYCLE과CONNECT_BY_ISCYCLE로 제어합니다.
MySQL 8.0 작성법
기본 재귀 CTE
WITH RECURSIVE org(emp_id, name, manager_id, lvl) AS (
SELECT emp_id, name, manager_id, 1
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.name, e.manager_id, o.lvl + 1
FROM employees e
JOIN org o ON e.manager_id = o.emp_id
)
SELECT emp_id,name,manager_id,lvl
FROM org
ORDER BY lvl, emp_id;문자열 경로 기반 순환 차단
WITH RECURSIVE org(emp_id, name, manager_id, path, lvl) AS (
SELECT emp_id, name, manager_id, CONCAT('/', emp_id, '/'), 1
FROM employees WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, e.name, e.manager_id,
CONCAT(o.path, e.emp_id, '/'),
o.lvl + 1
FROM employees e
JOIN org o ON e.manager_id = o.emp_id
WHERE INSTR(o.path, CONCAT('/', e.emp_id, '/')) = 0
)
SELECT emp_id,name,manager_id,lvl FROM org;- 표준
CYCLE절이 없으므로 경로 문자열로 재방문을 차단합니다. - 대규모 데이터에서는 JSON 경로와 가상 컬럼 인덱스를 고려할 수 있습니다.
실전 패턴 모음
특정 노드의 전체 하위
WITH RECURSIVE sub(emp_id, name, manager_id, lvl) AS (
SELECT emp_id, name, manager_id, 0 FROM employees WHERE emp_id = :target_id
UNION ALL
SELECT e.emp_id, e.name, e.manager_id, s.lvl + 1
FROM employees e JOIN sub s ON e.manager_id = s.emp_id
)
SELECT emp_id,name,manager_id,lvl FROM sub;현재 노드에서 루트까지 상위 경로
WITH RECURSIVE up(emp_id, name, manager_id, path, lvl) AS (
SELECT emp_id, name, manager_id, name, 0
FROM employees WHERE emp_id = :current_id
UNION ALL
SELECT e.emp_id, e.name, e.manager_id, CONCAT(e.name,' > ',up.path), up.lvl + 1
FROM employees e JOIN up ON up.manager_id = e.emp_id
)
SELECT * FROM up;조직도 구조 예시
- 김하늘 (1)
- 박준호 (2)
- 최민수 (3)
- 장예린 (3)
- 이서연 (2)
- 박준호 (2)
마무리
재귀 쿼리는 강력하지만 작성 방식과 데이터 특성에 따라 결과와 성능이 크게 달라질 수 있습니다. 이 글은 재귀 쿼리를 이해하는 과정에서 헷갈렸던 개념과 작동 흐름을 정리하기 위해 작성했습니다. 예제는 이해를 돕기 위한 최소 구성으로 제시했으며, 실제 환경에서는 스키마 구조, 데이터량, 인덱스 전략, 순환 규칙에 맞춘 세부 튜닝이 필요합니다. 글에서 부정확한 부분이나 보완해야 할 내용이 있다면 알려 주시면 감사하겠습니다. \의견을 반영하여 내용을 지속적으로 업데이트하겠습니다.