[데이터베이스] Ch5. SQL 고급3 - 재귀 질의

재귀 질의
(recursive query)

• 데이터베이스에는 계층도를 가진 데이터를 저장하는 경우가 있음
• 이때 계층도에서 원하는 정보를 얻기 위해 재귀 질의 사용 가능
• e.g) 조직도, 프로젝트의 구조

위 예시를 보자.
김쏜 - 김마미 , 김마미 - 김그랜파 는 각각 자식 - 부모 관계를 구축한다.
이 관계를 계층도로 만들 경우 밑의 그림처럼 나타낼 수 있다.

재귀 질의 문법

• 재귀 질의는 With 절을 이용한다.

WITH R AS (<query involving R>) <query involving R>

• 사용법

WITH R AS ( < base query > 
   UNION ALL
   < recursive query involving R > )
< query involving R >

// base query 에는 R이 참조되지 않고, recursive query 에선 R이 참조된다.
// R 은 자기 자신을 참조하지 않고, 이전의 결과를 참조한다.
// 즉, 이전의 결과가 empty table 이면 멈추게 된다.

위 예시만 보면 잘 이해가 가지 않는다. 밑의 다양한 예시들을 보며 이해하자

1부터 3까지의 숫자 세기

WITH countUp AS ( SELECT 1 as n
// countUp 임시 결과 집합 생성
// 1을 n 으로 명명.
   UNION ALL
   SELECT n+1
   FROM countUp
   WHERE n < 3 )
// n < 3 이 되기 전까지 UNION ALL

SELECT *
FROM countUp

ParentOf 릴레이션에서 'Frank'의 모든 조상을 찾기

WITH Ancestor AS ( SELECT parent AS p FROM ParentOf WHERE child = 'Frank'
UNION ALL
SELECT parent
FROM Ancestor, ParentOf
WHERE Ancestor.p = ParentOf.child )

SELECT * FROM Ancestor

Postgres 에서 재귀 질의

WITH RECURSIVE cte_name AS (
   <CTE_query_definition> -- non recursive term
   UNION [ALL]
   <CTE_query_definition> -- recursive term
) SELECT * FROM cte_name;

Postgres 에서 재귀 질의 예시

DROP TABLE IF EXISTS employees;

CREATE TABLE employees (
   employee_id serial PRIMARY KEY,
   full_name VARCHAR NOT NULL,
   manager_id INT
);

INSERT INTO employees (
   employee_id,
   full_name,
   manager_id
)
VALUES
   (1, 'Michael', NULL),
   ....
   (20, ...)
;

// employee_id = 2 인 직원의 직접, 간접적인 모든 부하 직원을 찾는다

WITH RECURSIVE subordinates AS ( 
   SELECT 
      employee_id,
      manager_id,
      full_name
   FROM
      employees
   WHERE
      employee_id = 2
 UNION
   SELECT
      e.employee_id, e.manager_id, e.full_name
   FROM
      employees e INNER JOIN subordinates s ON s.employee_id = e.manager_id
) SELECT * FROM subordinates;