WITH RECURSIVE

burningminor·2023년 9월 4일

Common Table Expression

with cte_name as (
    cte_body
)
cte 사용 쿼리;

A common table expression or CTE allows you to create a temporary result set within a query.

CTE는 실제 DB에 존재하지 않지만 임시로 생성되어 결과값을 저장하는 테이블이다.

CTE라는 임시 테이블은 해당 쿼리가 실행될 동안만 메모리에 저장된다.
또한 Subquery와 달리 쿼리를 실행하기 앞서 CTE를 선언하고 시작하므로, 가독성이 높다.

A view is a stored SQL query that is executed each time you reference it in another query. Note that a view doesn’t store the output of a particular query – it stores the query itself.

참고로, View는 CTE와 달리 결과 테이블을 저장하지 않고 실행할 쿼리를 저장한다.

WITH RECURSIVE

CTE는 아래의 두 가지 종류가 있다.

Non-recursive
Recursive

Recursive CTE는 최종 ResultSet을 얻을 때 까지 CTE 구문을 반복실행하며,
WITH RECURSIVE 구문을 사용한다.

Recursive CTE의 SQL 구문 형식은 다음과 같다.

WITH RECURSIVE CTE_이름 AS (
	CTE 초기값
    
    UNION [ALL]
   
   	반복(재귀) 쿼리; // 실행 ResultSet이 0일 때까지 반복
)
cte 사용 쿼리;

먼저 CTE 초기값 을 실행하여 CTE_이름 CTE를 초기화한다.

이후, 반복 쿼리를, 해당 쿼리 실행 결과가 존재하지 않을 때 까지 반복하며,
각 iterate의 결과값은 CTE에 더해준다.(UNION/UNION ALL)

만약 아래와 같은 folk 테이블이 있다고 가정하자.

그리고 해당 테이블에 대하여 아래의 recursive CTE 구문을 실행한다고 하자.

먼저 Anchor part로 표시된 초기화 부분이 실행된다.
현재 ancestor CTE는 아래와 같다.

id			name		father		mother
-----------------------------------------
100			Alex		20			30

이후 Recursive part로 표시된 반복문이 실행된다.
이로 인해, Dad와 Mom이 ancestor 에 추가된다.

id			name		father		mother
-----------------------------------------
100			Alex		20			30		
20			Dad			10			Null	//추가
30			Mom			Null		Null	//추가

이제, 새로 추가된 Dad와 Mom에 대하여 또다시 반복문이 실행된다.
그 결과, Grandpa Bill이 추가된다.

id			name		father		mother
-----------------------------------------
100			Alex		20			30		
20			Dad			10			Null
30			Mom			Null		Null
10			Grandpa 	Null		Null	//추가

추가된 Grandpa Bill에 대하여 다시 반복문이 실행되고, 이번에는 해당하는 결과가 0이다.
따라서 반복문이 종료되고 CTE가 완성(?) 된다.

이후, cte 사용 쿼리(select * from ancestors;) 가 실행된다.

이로써 쿼리 실행이 종료된다.