[MySQL] WITH RECURSIVE(재귀 쿼리)

WITH RECURSIVE(재귀 쿼리)

 

MySQL 8.0 버전부터 CTE(Common Table Expression)으로 재귀 쿼리를 사용할 수 있다.

 

CTE (Common Table Expression) 란?

• SQL 문법에서 WITH 로 시작하는 구문.
• 임시 테이블을 만들어서 그 쿼리 안에서 재사용할 수 있는 결과 집합을 만든다.

ex)

WITH 임시테이블명 AS (

   // 사용할 SELECT 문

) 
SELECT * FROM 임시테이블명;

 

재귀 CTE는 자기 자신을 참조하는 CTE를 의미하며

부모 - 자식 구조처럼 계층 구조 데이터를 한 번에 조회할 때 사용한다.

 

ex) 

      기술팀
       ㄴ 인프라팀
               ㄴ네트워크
       ㄴ 개발팀

              ....   

 

WITH RECURSIVE temp_dept_table AS (
    -- 1. 최상위 부서
    SELECT 
        dept_id
        , parent_id
        , dept_name AS level1
        , CAST(NULL AS CHAR(50)) AS level2
        , CAST(NULL AS CHAR(50)) AS level3
        , depth
    FROM dept_table
    WHERE depth = 1

    UNION ALL

    -- 2. 하위 부서 연결
    SELECT 
        child.dept_id
        , child.parent_id
        , parent.level1
        , IF(child.depth = 2, child.dept_name, parent.level2)
        , IF(child.depth = 3, child.dept_name, parent.level3)
        , child.depth
    FROM dept_table child
    INNER JOIN temp_dept_table parent
    ON child.parent_id = parent.dept_id
)

SELECT CONCAT_WS(' > ', level1, level2, level3) AS total_level
FROM temp_dept_table
ORDER BY level1, level2, level3;

 

 

RECURSIVE 를 사용한 쿼리를 상세히 뜯어보자.

 

1. 재귀 쿼리를 사용하기 위해 WITH RECURSIVE temp_dept_table로 임시테이블명을 정해준다.

2. 최상위 부서로 사용될 부서 목록을 먼저 조회해 준다.

3. 최상위 부서에 는 별도의 하위부서 값이 없기 때문에 level2, level3 값은 NULL 로 처리해 준다.

 

이 쿼리가 재귀 쿼리의 기준값이 되어준다.

(현재 temp_dept_table 내에는 상위 부서 목록인 총무팀, 기술팀 목록만 추가된 상태)

-- 1. 최상위 부서
SELECT 
    dept_id
    , parent_id
    , dept_name AS level1
    , CAST(NULL AS CHAR(50)) AS level2
    , CAST(NULL AS CHAR(50)) AS level3
    , depth
FROM dept_table
WHERE depth = 1

 

최상위 부서 목록 조회

 

 

상위 부서인 총무팀과 기술팀 하위 부서를 연결하여 조회하기 위한 쿼리를 작성한다.

 

4. temp_dept_table 내에는 상위 부서 목록인 총무팀, 기술팀 목록만 추가된 상태에서 child.parent_id = parent.dept_id JOIN을 통해서 자기 자신을 FROM에서 참조하여(재귀) Recursive SELECT이 실행된다. 

 

5. JOIN 결과가 없을 때까지 누적하여 UNION ALL로 결과가 합쳐진다. 

즉, 이미 찾은 부서를 부모로 삼아서 그 하위 부서들을 계속 찾아 내려간다.

     UNION ALL

    -- 2. 하위 부서 연결
    SELECT 
        child.dept_id
        , child.parent_id
        , parent.level1
        , IF(child.depth = 2, child.dept_name, parent.level2)
        , IF(child.depth = 3, child.dept_name, parent.level3)
        , child.depth
    FROM dept_table child
    INNER JOIN temp_dept_table parent
    ON child.parent_id = parent.dept_id

 

📖 재귀 CTE 공식문서에 의하면, 

최상위 데이터를 가져오는 SELECT를 Anchor 쿼리
자기 자신을 다시 참조하는 SELECT를 Recursive 쿼리라고 명칭 한다고 한다.

 

 

생성한 임시테이블 temp_dept_table 로 원하는 쿼리 형태로 조회해 준다.

SELECT CONCAT_WS(' > ', level1, level2, level3) AS total_level
FROM temp_dept_table
ORDER BY level1, level2, level3;

 

 

 

테스트용 코드.txt

0.00MB

---

참고: OpenAI ChatGPT (https://openai.com)

MySQL 작가: Icons8