Recursive Query(재귀 쿼리) 알아보기

재귀쿼리란?

재귀쿼리는 쿼리 내에서 자기자신을 참조하여 반복적으로 실행되는 쿼리이다. 즉, 쿼리가 반복적으로 실행되면서 이전 단계의 결과를 이용하여 현재 단계의 결과를 도출해 내는 것을 반복하여 최종 결과를 반환한다. 재퀴쿼리는 주로 계층적인 데이터 구조를 다룰 때 유용하게 사용된다.

재귀쿼리 구조

재귀 쿼리를 만들기 위해 CTE(공통 테이블 식, Common Table Expressions)을 활용한다. CTE는 쿼리에서 임시적으로 사용할 수 있는 이름이 지정된 일시적인 결과 집합이다. CTE는 WITH 절을 사용하여 정의되며, 재귀 쿼리의 시작 지점과 재귀 쿼리 자체를 정의하는 두 부분으로 구성된다.

WITH RECURSIVE cte_name [(col1, col2 ...)] AS (
    -- 초기 쿼리 (Non-recursive part)
    SELECT ... FROM ...

    UNION [ALL]

    -- 재귀 쿼리 (Recursive part)
    SELECT ... FROM cte_name [WHERE]
)
SELECT * FROM cte_name;

1. cte_name : 공통 테이블 식의 이름. `WITH RECURSIVE` 구문을 통해 생성되는 테이블의 이름
2. Non-recursive part : 초기에 한번만 실행되는 쿼리
3. UNION [ALL] : 이전 단계의 결과와 현재단계의 결과를 결합
4.  Recursive_part : 반복실행 되는 재귀 쿼리 구문. 여기서 Where절로 종료 조건을 설정하며, 이를 잘못 설정하면 무한 루프에빠지거나 원하지 않는 결과를 얻을 수 있으므로 조심해야 함
5. SELECT * FROM cte_name : 최종결과를 반환하는 Select 문. 재귀적으로 생성된 결과가 모두 모아져서 출력 됨

재귀쿼리 예시

employees 테이블이 아래와 같이 구성되어 있다.
여기서 manager_id가 null인것은 최상위 매니저를 의미한다.

| employee_id | employee_name | manager_id |
|-------------|---------------|------------|
| 1           | Alice         | NULL      |
| 2           | Bob           | 1         |
| 3           | Charlie       | 1         |
| 4           | David         | 2         |
| 5           | Eva           | 2         |
| 6           | Frank         | 3         |
| 7           | Grace         | 3         |
| 8           | Hanna         | 4         |
| 9           | Ian           | 4         |
| 9           | Jack          | 5         |

 

아래와 같은 재귀쿼리를 작성한다.

WITH RECURSIVE employee_hierarchy (employee_id, employee_name, manager_id, level) AS (
    -- 초기 쿼리 (Non-recursive part)
    SELECT employee_id, employee_name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 재귀 쿼리 (Recursive part)
    SELECT e.employee_id, e.employee_name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

쿼리를 좀 더 자세히 들여다 보면, 

1. Initial Query(초기 실행 쿼리): manager_id가 null인 데이터를 조회하되, level을 '0'으로 한다. 
2. Recursive Query(재귀쿼리): employees 테이블의 manager_id와 employee_hierarchy 테이블의 employee_id를 기준으로 Inner Join하고, level을 employee_hierarchy의 employee(매니저)의 level에서 +1을 해준 값으로 한다.

쿼리를 실행하면 다음과 같은 결과를 얻을 수 있다.

| employee_id | employee_name | manager_id | level |
| ----------- | ------------- | ---------- | ----- |
| 1           | Alice         | NULL       | 0     |
| 2           | Bob           | 1          | 1     |
| 3           | Charlie       | 1          | 1     |
| 4           | David         | 2          | 2     |
| 5           | Eva           | 2          | 2     |
| 6           | Frank         | 3          | 2     |
| 7           | Grace         | 3          | 2     |
| 8           | Hannah        | 4          | 3     |
| 9           | Ian           | 4          | 3     |
| 10          | Jack          | 5          | 3     |