[MariaDB] 계층형 쿼리(Hierarchical Queries)

 

 

Hierarchical Queries

오라클에서는 CONNECT BY PRIOR 절을 사용해 계층형 쿼리를 생성한다면, MariaDB에서는 Recursive Common Table Expressions(CTE)라는 가상의 테이블을 사용한다.

---

WITH RECURSIVE recursive_name [(column1, ...)] AS (
   -- initial query (처음 호출하는 쿼리)
   -- non-recursive query
   SELECT [(column1, ...)]
   UNION [ALL]
   -- recursive query (반복 쿼리)
   SELECT [(column1, ...)] FROM recursive_name [WHERE]
)
-- parent query
SELECT * FROM recursive_name

WITH RECURSIVE 구분을 통해 쿼리가 반복되며, 반복된 결과를 parent query 영역에서 FROM 절로 가져와 사용한다.

 

 

Example

---

오라클과 비교해서 직원과 매니저 관계를 이용해 계층형 쿼리를 이해해 보자.

 

Oracle 예제

→ SELECT 절에 가상 컬럼(Pseudo-Column)인 LEVEL을 이용하여 해당 행의 depth를 표현하게 한다.

→ START WITH 절에 최상위 행을 설정한다.

→ CONNECT BY PRIOR 절로 상위에서 하위로 정렬하여(높은 직급 → 낮은 직급) 값을 반환하도록 설정되어 있다.

SELECT id, name, mng_id, LEVEL
FROM employees
START WITH id  = 1
CONNECT BY PRIOR id = mng_id;

 

결과

 

 

 

MariaDB 예제

→ WITH RECURIVE 구문으로 재귀로 사용할 수 있는 메모리 상에 가상의 테이블을 저장한다.

UNION ALL을 기준으로

→ 첫 번째 쿼리문에서 최상위 노드를 설정한다. 이때 LEVEL 값은 1이다.

→ 두 번째 쿼리문에서 기존 테이블에서 위에 설정한 최상위 노드를 상위 노드로 가진 하위 노드를 찾아 LEVEL 값을 1 더해 depth를 표현한다.

→ 다시 그 하위 노드를 상위 노드로 가진 노드를 찾아 LEVEL 값을 더해준다(없을 때까지 반복).

→ UNION ALL로 합쳐진 결과에서 값을 조회한다.

WITH RECURSIVE cte_connect_by AS (
	SELECT id, name, mng_id, 1 AS LEVEL
    FROM employees s 
    WHERE id = 1
    UNION ALL
    SELECT id, name, mng_id, LEVEL + 1 AS LEVEL
    FROM cte_connect_by r 
    INNER JOIN employees s ON r.id = s.mng_id
)
SELECT id, name, mng_id, LEVEL
FROM cte_connect_by;

 

결과

 

 

 

---

ref.

• https://www.sqlines.com/oracle-to-mariadb/connect_by_prior
• https://mine-it-record.tistory.com/447