재귀 CTE(WITH RECURSIVE)

Oracle

-- Hierarchy with CONNECT BY:
SELECT id, name, manager_id, LEVEL
FROM employee
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

-- Alternative (11gR2+): standard recursive WITH (the RECURSIVE keyword is not written)
WITH tree (id, name, manager_id) AS (
  SELECT id, name, manager_id FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree;

조직도, 부품 구성표(BOM) 같은 계층(트리) 데이터를 순회하는 데 사용한다. Oracle의 고전적인 방법은 CONNECT BY / PRIOR와 LEVEL 의사 컬럼이며, START WITH가 루트 행을 지정한다. 11gR2부터 표준 재귀 CTE도 지원한다.

PostgreSQL

WITH RECURSIVE tree (id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 1
  FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id, a.level + 1
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree ORDER BY level;

WITH RECURSIVE는 앵커 쿼리와 재귀 부분을 UNION ALL로 결합하며, 재귀 부분은 결과가 빌 때까지 반복 실행된다. 계층 구조와 그래프 탐색에 적합하다. 무한 루프는 UNION(ALL 대신)이나 순환 방지로 막는다.

MySQL

WITH RECURSIVE tree (id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 1
  FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id, a.level + 1
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree ORDER BY level;

MySQL 8.0(및 MariaDB 10.2+)은 표준을 따르는 WITH RECURSIVE 구문을 지원한다. 재귀 깊이는 cte_max_recursion_depth 시스템 변수(기본값 1000)로 제한된다. 이전 버전에서는 저장 프로시저에서 루프를 작성해야 했다.

SQL Server

WITH tree (id, name, manager_id, level) AS (
  SELECT id, name, manager_id, 1
  FROM employee WHERE manager_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.manager_id, a.level + 1
  FROM employee c JOIN tree a ON c.manager_id = a.id
)
SELECT * FROM tree ORDER BY level
OPTION (MAXRECURSION 100);

SQL Server에서는 재귀 CTE에 RECURSIVE 키워드를 쓰지 않는다. UNION ALL을 포함하고 자기 자신을 참조하는 CTE는 자동으로 재귀로 처리된다. 기본 재귀 한도는 100이며 OPTION (MAXRECURSION n)으로 바꾼다(0 = 무제한, 신중하게 사용).

MongoDB

// Closest equivalent: the $graphLookup stage
db.employee.aggregate([
  { $match: { manager_id: null } },
  { $graphLookup: {
      from: "employee",
      startWith: "$id",
      connectFromField: "id",
      connectToField: "manager_id",
      as: "subordinates"
  } }
])

MongoDB에는 재귀 CTE가 없으며 계층 순회에는 $graphLookup aggregation 단계를 사용한다. connectFromField/connectToField가 연결 필드를, maxDepth가 깊이 한도를 지정한다. 트리와 그래프 관계를 단일 쿼리로 해결한다.

Redis

# Not applicable: Redis has no recursive queries.

Redis는 쿼리 언어를 제공하지 않는다. 계층/그래프 순회는 애플리케이션 코드나 서버 측 Lua 스크립트로 수행하며, 기본 Redis에서는 트리 탐색을 클라이언트 측에서 작성한다. (과거에 쓰이던 RedisGraph 모듈은 2023년에 지원이 종료되었다.)

Elasticsearch

# Not applicable: recursive queries are not supported.
# Approach: model with parent-child (join field) or nested,
# deep hierarchies are traversed on the application side.

Elasticsearch는 재귀 쿼리를 제공하지 않는다(WITH RECURSIVE / CONNECT BY에 해당하는 기능 없음). 계층 관계는 'join'(parent/child) 필드 타입이나 트리 경로를 필드에 저장하는 방식(materialized path)으로 모델링하며, 다단계 순회는 보통 애플리케이션에서 수행한다.