[MySQL] 재귀 쿼리 (Recursive Query)

재귀 쿼리

재귀 쿼리는 자기 자신을 참조하여 반복적으로 실행되는 쿼리를 말합니다.

이러한 재귀 쿼리는 일반적으로 계층적인 데이터 구조를 다룰 때 사용합니다.

(예: 조직도, 카테고리, 댓글 등)

 

 

 

특징

주요 특징으로는 자기 참조, 반복적 실행, 종료 조건이 있습니다.

• 자기 참조
  재귀 쿼리는 쿼리 내에서 자기 자신을 참조합니다.
• 반복적 실행
  재귀 쿼리는 초기 단계에서 실행하여 반복적으로 실행됩니다.
  초기 단계에서는 시작점을 정의하지만, 재귀적으로 실행되는 단계에서 이전의 결과를 기반으로 다음 결과를 계산합니다.
• 종료 조건
  무한 루프를 방지하기 위해 종료 조건이 필요합니다.
  종료 조건은 재귀적으로 실행되는 쿼리의 기저 조건을 정의합니다.

 

 

 

재귀 쿼리의 예시

조직도로 예시를 들어보겠습니다.

CREATE TABLE Employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT
);

-- 최상위 관리자
INSERT INTO Employees (id, name, manager_id) VALUES (1, 'John', NULL);
INSERT INTO Employees (id, name, manager_id) VALUES (10, 'Michael', NULL);

-- John의 부하 직원
INSERT INTO Employees (id, name, manager_id) VALUES (2, 'Alice', 1);
INSERT INTO Employees (id, name, manager_id) VALUES (3, 'Bob', 1);
INSERT INTO Employees (id, name, manager_id) VALUES (4, 'Charlie', 2);
INSERT INTO Employees (id, name, manager_id) VALUES (5, 'David', 3);
INSERT INTO Employees (id, name, manager_id) VALUES (6, 'Eva', 2);
INSERT INTO Employees (id, name, manager_id) VALUES (7, 'Frank', 3);
INSERT INTO Employees (id, name, manager_id) VALUES (8, 'Grace', 6);
INSERT INTO Employees (id, name, manager_id) VALUES (9, 'Henry', 6);

-- Michael의 부하 직원
INSERT INTO Employees (id, name, manager_id) VALUES (11, 'Oliver', 10);
INSERT INTO Employees (id, name, manager_id) VALUES (12, 'Patricia', 10);
INSERT INTO Employees (id, name, manager_id) VALUES (13, 'Quinn', 10);

 

조직도 내에서 위와 같은 데이터가 있다고 해봅시다.

여기서 John이 직접/간접적으로 관리하는 직원의 목록을 보고 싶다면 아래와 같이 재귀 쿼리를 만들면 됩니다.

WITH RECURSIVE Subordinates AS (
    SELECT id, name, manager_id, 1 as depth
    FROM Employees
    WHERE id = 1

  UNION ALL

    SELECT e.id, e.name, e.manager_id, s.depth + 1
    FROM Employees e
    JOIN Subordinates s ON e.manager_id = s.id -- 부하 직원의 부하 직원을 재귀적으로 찾습니다.
    WHERE s.depth < 3 -- 깊이가 3보다 작을 때만 재귀를 수행합니다.
)
SELECT * FROM Subordinates WHERE id != 1; -- 'John'을 제외한 결과만 선택합니다.

 

• WITH RECURSIVE Subordinates
  재귀적인 Common Table Expression(CTE)를 정의하는 부분입니다.
  이름을 'Subordiantes'로 정의하고 아래에서 재귀적으로 참조됩니다.
• SELECT id, name, manager_id, 1 as depth FROM Employees WHERE id = 1
  초기 쿼리로 재귀의 시작점이 되며 첫 번째 루프에서 실행됩니다.
  추가로 출력을 원하는 깊이를 설정하기 위해서 depts 컬럼을 추가했습니다.
• SELECT e.id, e.name, e.manager_id FROM Employees e JOIN Subordinates s ON e.manager_id = s.id WHERE s.depth < 3
  재귀 쿼리로 이전 단계에서 선택된 직원 부하들의 부하 직원을 선택합니다.
  이를 통해 부하 직원이 관리하는 직원을 재귀적으로 찾습니다.
  종료 조건을 추가해 깊이가 3보다 작을 때까지만 재귀를 수행하도록 합니다.
• SELECT * FROM Subordinates WHERE id != 1
  John은 제외해서 결과를 조회합니다.

---

결과를 순서대로 조회

우선 초기 단계에서는 John만 조회됩니다.

id	name	manager_id	depth
1	John	NULL	1

 

첫 번째 재귀 단계에서는 John의 직접적인 부하직원이 조회됩니다.

id	name	manager_id	depth
1	John	NULL	1
2	Alice	1	2
3	Bob	1	2

 

두 번째 재귀 단계에서는 Alice와 Bob의 부하직원이 조회됩니다.

id	name	manager_id	depth
1	John	NULL	1
2	Alice	1	2
3	Bob	1	2
4	Charlie	2	3
5	David	3	3
6	Eva	2	3
7	Frank	3	3

 

이 이후부터는 Depth가 3 이상이므로 재귀 쿼리가 종료됩니다.