동일 테이블에 계층적으로 상위데이터와 하위 데이터가 포함되어 있는는 데이터 구조이다.

예제

member 테이블

WITH RECURSIVE CTE(member_id, manager_id, lvl)
AS ( SELECT member.member_id, member.manager_id, 0 AS lvl
     FROM member
     WHERE manager_id is null
     -- CTE의 초기 상태 설정
     UNION ALL
     -- CTE와 아래의 재귀 쿼리를 합집합 연산
     SELECT a.member_id, a.manager_id, b.lvl + 1
     FROM member a
     JOIN CTE AS b
     ON a.manager_id = b.member_id
     -- member 테이블 a의 메니저 아이디와 CTE 테이블 b의 멤버 아이디가 같다면 조인
     )

SELECT member_id, manager_id, lvl
FROM CTE
ORDER BY member_id, lvl;
-- CTE 출력

출력 결과

쿼리문 분석

이제 위 쿼리문을 어떤 구조로 사용했는지 분석해보자

Recursive CTE Syntax 사용

WITH RECURSIVE CTE(member_id, manager_id, lvl) AS

CTEs란?

Common Table Expressions
쿼리를 통해 만들어낸 임시적인 데이터 세트

WITH [테이블명]([컬럼명]) AS ([CTE 정의])

임시 테이블인 만큼 쿼리가 끝나면 사라진다.

재귀 CTE

WITH RECURSIVE cte_name AS (
   cte_query_definition -- Anchor 쿼리
 
   UNION ALL
 
   cte_query_definition -- 재귀 쿼리
   )
 
 
SELECT *
FROM cte_name;

재귀 쿼리가 더 이상 새로운 결과를 반환하지 못하면 종료된다.

참고

최상위 튜플 가져오기

SELECT member.member_id, member.manager_id, 0 AS lvl
FROM member
WHERE manager_id is null

member 테이블에서 member.manager_id가 NULL인 튜플을 lvl을 0을로 설정하고 CTE에 삽입한다.

이러한 초기상태의 쿼리를 Anchor 쿼리라고 한다.

재귀 쿼리 CTE와 합치기

UNION ALL
-- CTE와 아래의 재귀 쿼리를 합집합 연산
SELECT a.member_id, a.manager_id, b.lvl + 1
FROM member a
JOIN CTE AS b
ON a.manager_id = b.member_id

1. CTE 테이블의 멤버 아이디와 member 테이블의 메니저 아이디가 같은 튜플을 조인
   ➡️ 상위 컬럼이 하위 컬럼과 조인하는 과정
2. CTE 테이블의 멤버 아이디와 member 테이블의 메니저 아이디가 같은 튜플의 lvl을 1 증가
   ➡️ 조인한 튜플의 계층 lvl 증가

CTE 출력

SELECT member_id, manager_id, lvl
FROM CTE
ORDER BY lvl, member_id;

재귀적 CTE 처리가 끝난 CTE 테이블을 출력한다.
ORDER BY를 이용해 lvl을 오름차 순으로 출력했다.

각 재귀의 단계 설명

초기 단계 (lvl = 0)

manager_id가 null인, 즉 최상위 계층의 튜플을 CTE에 삽입

1단계 (lvl = 1)

manager_id가 CTE 테이블에 있는 최상위 member_id(=1000)와 같은 튜플을 CTE와 조인 한후에 초기 단계의 테이블과 합집합 연산
연산과 동시에 lvl을 증가하며 다음 계층의 튜플을 삽입하게 된다.

2단계 (lvl = 2)

전 단계와 같은 방법으로 manager_id가 member_id(=1001), member_id(=1005)와 같은 튜플을 CTE와 조인 한 후 1단계의 테이블과 합집합 연산

3단계 (lvl = 3)

2단계와 같은 방법으로 마지막 남은 튜플을 합집합 연산을 하고, 재귀 쿼리는 더 이상 반환할 데이터가 없기 때문에 종료된다.