SQL에서는 '계층형 트리 구조'를 만들 수 있다.
계층형 구조란, 상위-하위 개념을 부모-자식 관계로 표현하는 것을 의미한다.

(이미지 출처)

(이미지 출처)

oracle은 계층형 쿼리를 지원하는 문법을 별도로 지원하는 반면, 나머지 SQL 문법들은 재귀 CTE를 통해 직접 작성해야 한다.

Oracle

oracle의 경우 계층형 쿼리에 대한 문법을 직접적으로 지원한다.
SQLD 시험 준비를 할 때 배우는 계층형 쿼리 구문이 바로 이 내용이다.

[오라클] 계층형 쿼리 ( START WITH ... CONNECT BY )

SELECT
	id
    , parent_id
    , level
FROM
	table
START WITH parent_id is null	-- root 노드를 지정
CONNECT BY PRIOR id = parent_id	-- 부모-자식 노드를 연결
;

root 노드와 prior를 통해 트리가 어느 방향으로 구성되는 지 볼 수 있다
이 때 prior이 자식에 붙는다면 순방향, 부모에 붙는다면 역방향이 된다.
'CONNECT BY PRIOR 자식 컬럼 = 부모 컬럼'이라면, 부모에서 자식을 향하는 방향(Top-Down)으로 트리가 구성된다

일반적으로는 순방향을 많이 사용한다 (앞서 처음에 보여주었던 예시 이미지도 순방향)

그 외 SQL

oracle 이외의 mysql 등은 계층 쿼리 문법을 직접적으로 지원하지 않는다
그에 따라 재귀 CTE를 통해 루프를 걸어서 직접 계층 쿼리를 구성해야 한다.

WITH RECURSIVE cte_count 
AS ( 
    -- Non-Recursive 문장( 첫번째 루프에서만 실행됨 )
    SELECT 1 AS n
    UNION ALL
    -- Recursive 문장(읽어 올 때마다 행의 위치가 기억되어 다음번 읽어 올 때 다음 행으로 이동함)
    SELECT n + 1 AS num 
    FROM cte_count
    WHERE n < 3 
)
SELECT * FROM test;

출처: [MySQL] 계층형 조회 (Recursive 재귀 쿼리)

처음 Non-Recursive 쿼리에서 최초의 루트 노드를 지정해주며,
이후 Recursive 쿼리에서는 level이 1 증가할 때마다 반복한다. 이 때 PRIOR 개념은 INNER JOIN의 조건절(ON)을 통해 지정할 수 있다.

이렇게 나온 결과를 UNION ALL로 결합하며,
마지막으로 WHERE n < 3을 통해 루프가 끝나는 지점(정지 조건 termination condition)을 설정할 수 있다.

예시 문제

프로그래머스 특정 세대의 대장균 찾기 문제를 풀 때 계층 쿼리를 사용할 수 있다

WITH recursive CTE AS (
    -- Non-recursive : 첫번째 루프에서만 실행
    SELECT
        id
        , 1 as level
    FROM
        ecoli_data
    WHERE
        parent_id is null    
    UNION ALL
    -- Recursive : 반복적으로 루프 내에서 실행
    SELECT
        e.id
        , level + 1 as "level"
    FROM
        ecoli_data e
        INNER JOIN cte c
            ON e.parent_id = c.id
)
SELECT
    id
FROM
    CTE
WHERE
    level = 3
ORDER BY 
    id
;