[SQL] WITH RECURSIVE

프로그래머스에서 멸종위기의 대장균 찾기 문제에서 사용한 WITH RECURSIVE 절에 대해 알아보자

 

WITH RECURSIVE는 쿼리 안에서 재귀적으로 자기 자신을 참조할 수 있는 임시 테이블을 생성한다.

계층 구조나 트리 구조 데이터를 탐색할 때 주로 사용된다.

 

멸종위기의 대장균 찾기 정답 코드를 보면서 확인해보자 !

with recursive tmp as		# tmp라는 임시 테이블 생성
(	
    select id, parent_id, 1 as generation
    from ecoli_data
    where parent_id is null	# 최상위 노드 추출 
    union all			# 재귀적 쿼리 : 첫 번째 쿼리에서 찾은 데이터를 바탕으로, 부모-자식 관계를 기반으로 하위 노드를 찾는다
    select s.id, s.parent_id, tmp.generation + 1
    from tmp join ecoli_data s
    on tmp.id = s.parent_id	# s.parent_id 값이 존재하지 않으면 재귀 종료 
)

select count(*) count, generation
from tmp
where id not in			# 부모 노드에 포함되지 않는 노드 = 자식이 없는 노드 id 찾기
	(
    select distinct parent_id
    from tmp
    where parent_id is not null
    )
group by generation
order by 2;