[MySQL] CONNECT BY 구현하기

WITH RECURSIVE TMP AS (
    -- 최상위 부모 정의
    SELECT COD
         , NM
         , 1                          AS LVL
         , CAST(COD AS VARCHAR(100))  AS PATH
         , CAST(NM  AS VARCHAR(100))  AS PATHNM
      FROM CODTEST
     WHERE PARNT = ''
     UNION ALL
    -- 단계별 부모와 자식 매핑
    SELECT C.COD
         , C.NM
         , P.LVL + 1                      AS LVL
         , CONCAT(P.PATH  , ' > ', C.COD) AS PATH
         , CONCAT(P.PATHNM, ' > ', C.NM)  AS PATHNM
      FROM TMP        AS P
      JOIN CODTEST    AS C ON C.PARNT = P.COD
)
SELECT *
 FROM TMP

MySQL에서도 CONNECT와 같이 계층형으로 JOIN을 걸어줘야 할 때가 종종 있습니다.

대표적으로 트리형 데이터 구조를 갖는 경우로 카테고리나 폴더구조, 회사조직도를 관리하는 경우입니다.

 

이 경우 위와 같이 WITH절을 RECURSIVE로 사용하여 구현가능합니다.

WITH절에 대한 내용은 이전 글에 포함해 두었습니다.

2020.09.02 - [개발 창고/데이터베이스 개발] - [SQL] WITH절 사용하기

[SQL] WITH절 사용하기

 

핵심은 아래 3개의 빨간 블록입니다.

1/ WITH절을 RECURSIVE로 사용

2/ UNION ALL 이전에 최상위(ROOT) 목록을 지정

3/ WITH절 정의 시 사용도니 이름을 부모로 자식 테이블과 JOIN

 

만약 아래와 같이 코드/값/부모코드로 정의된 테이블이 이 있다고 가정하고

이를 아래와 같이 각 계층의 LEVEL / 마지막 값까지의 경로를 표현해 준다고 가정하면

 

가장 일반적으로 사용되는 방법이 아래와 같습니다.

SELECT COD
     , NM
     , 1                          AS LVL
     , CAST(COD AS VARCHAR(100))  AS PATH
     , CAST(NM  AS VARCHAR(100))  AS PATHNM
  FROM CODTEST
 WHERE PARNT = ''
 UNION ALL
SELECT C.COD
     , C.NM
     , 2                             AS LVL
     , CONCAT(P.COD, ' > ', C.COD)   AS PATH
     , CONCAT(P.NM, ' > ', C.NM)     AS PATHNM
  FROM CODTEST P
  JOIN CODTEST C ON C.PARNT = P.COD
 WHERE P.PARNT = ''
 UNION ALL
SELECT C.COD
     , C.NM
     , 3                                            AS LVL
     , CONCAT(P1.COD, ' > ', P2.COD, ' > ', C.COD)  AS PATH
     , CONCAT(P1.NM, ' > ', P2.NM, ' > ', C.NM)     AS PATHNM
  FROM CODTEST P1
  JOIN CODTEST P2 ON P2.PARNT = P1.COD
  JOIN CODTEST C  ON C.PARNT  = P2.COD
 WHERE P1.PARNT = ''

이 방법의 단점을 뽑으면 크게 2개 정도 있습니다.

1/ QUERY문이 길어집니다.

   ▶︎ 중간 수정 시에 변경할 분량이 많아집니다. 이로 인해 개발자가 빈번히 버그를 유도할 수 있습니다.

2/ 확장성이 제한됩니다. 

   ▶︎ 사전에 몇 Depth인지(몇 개의 UNION ALL을 걸어야 하는지)를 알아야 하며, 혹시나 더 늘어나거나 줄어드는 경우에 QUERY문을 수정해주어야 합니다.

 

때문에 조금 더 안정적인 방법의 사용을 위해서는 WITH RECURSIVE로 사용하는 것이 QUERY 라인 수를 줄여 유지보수 비용을 낮춰주는 길이 됩니다.