[SQL] 계층형 질의

계층형 질의

테이블에 계층형 데이터가 존재하는 경우, 데이터를 조회하기 위해 사용하는 것

주로 ORACLE과 SQL Server에서 사용

계층형 데이터

동일 테이블에 계층적으로 상위와 하위 데이터가 포함되어 있는 데이터

ex) 한 테이블에 사원과 관리자 표현

 

계층형 질의 예시(ORACLE)

SELECT LEVEL, 자식컬럼, 부모컬럼, 원하는컬럼
FROM 테이블명
START WITH 부모컬럼 IS NULL --부모컬럼이 NULL인 행이 Root(가장 상위)가 됨
CONNECT BY PRIOR 자식컬럼 = 부모컬럼 -- 상위 데이터와 하위 데이터의 연결 방식 
;

SELECT LEVEL, LPAD(' ',4*(LEVEL-1))||부모컬럼, 자식컬럼
FROM 테이블명 START WITH 자식컬럼 IS NULL CONNECT BY PRIOR 부모컬럼=자식컬럼
;

 

CONNECT BY 키워드(ORACLE)

키워드	설명
LEVEL	검색 항목의 깊이를 의미하며, 계층 구조에서 루투(최상위)의 레벨이 1
CONNECT_BY_ROOT	현재 전개할 데이터의 루트(최상위) 데이터 값 표시
CONNECT_BY_ISLEAF	현재 전개할 데이터의 리프(최하위) 여부 표시(0 or 1)
SYS_CONNECT_BY_PATH(A,B)	루트 데이터부터 현재까지 전개한 경로 표시(A; 컬럼명, B: 구분자)

 

계층형 질의 예시(SQL Server, Maria DB)

SQL Server version.2000 이전: 저장 프로시저를 재귀 호출/While 루프 문에서 임시 테이블 사용

WITH CTE AS(
    SELECT 부모컬럼, 자식컬럼, LEVEL
    FROM 테이블명
    WHERE 자식컬럼 IS NULL
    UNION ALL
    SELECT b.부모컬럼, b.자식컬럼, a.LEVEL + 1
    FROM CTE a, 테이블명 b
    WHERE a.자식컬럼 = b.부모컬럼)
    
SELECT 부모컬럼, 자식컬럼, LEVEL
FROM CTE
;

SQL Server version.2000, Maria DB 10.2 이후: CTE(Common Table Expression)을 이용하여 재귀 호출

WITH RECURSIVE CTE(부모컬럼, 자식컬럼, LEVEL) -- WITH RECURSIVE: 재귀호출을 하겠다
AS(
    SELECT 부모컬럼, 자식컬럼, 0 AS LEVEL
    FROM 테이블명
    WHERE 자식컬럼 IS NULL -- ORACLE의 START WITH와 의미상 동일
    
    UNION ALL
    
    SELECT a.부모컬럼, a.자식컬럼, b.LEVEL + 1
    FROM 테이블명 a
    JOIN CTE b
    ON a.자식컬럼 = b.부모컬럼 -- ORACLE의 CONNECT BY와 의미상 동일
)

SELECT 부모컬럼, 자식컬럼, LEVEL
FROM CTE
ORDER BY 부모컬럼, LEVEL
;