SQL Server에서의 계층형 질의문 Oracle 같이 알아보기

SQL Server에서의 계층형 질의문 Oracle 같이 알아보기

계층형 질의문(Hierarchical Query)은 데이터베이스에서 계층적 데이터를 쿼리하고 표시하는 데 사용되는 강력한 도구입니다. SQL Server와 Oracle에서 계층형 질의를 수행하는 방법을 예시와 함께 설명 하겠습니다..

 

SQL Server에서의 계층형 질의문:

SQL Server에서는 공통 테이블 표현식(Common Table Expression 또는 CTE)과 CTE를 활용한 UNION ALL을 통해 계층형 질의를 수행할 수 있습니다.

예시를 들어보겠습니다. 아래와 같은 조직 구조를 가정해 봅시다.

부서
|-- 사원1
|-- 사원2
|   |-- 사원3
|-- 사원4

위와 같은 데이터베이스 구조에서 "부서"와 "사원" 테이블이 있다고 가정하겠습니다. 부서 테이블에는 부서 ID와 부서명, 사원 테이블에는 사원 ID, 부서 ID, 사원명이 저장되어 있다고 가정합니다.

WITH 계층구조 AS (
    SELECT 사원ID, 부서ID, 사원명, 1 AS 레벨
    FROM 사원
    WHERE 상사ID IS NULL -- 최상위 부서 시작
    UNION ALL
    SELECT e.사원ID, e.부서ID, e.사원명, 레벨 + 1
    FROM 사원 e
    INNER JOIN 계층구조 h ON e.상사ID = h.사원ID
)
SELECT 사원ID, 부서ID, 사원명, 레벨
FROM 계층구조
ORDER BY 레벨, 사원ID;

이 쿼리는 CTE를 사용하여 계층적 데이터를 검색합니다. 최상위 부서(상사ID가 NULL인 사원)에서 시작하여 각 레벨에 따라 하위 부서 및 사원을 연결합니다. 결과는 "레벨" 순서로 정렬되며, 각 사원의 사원ID, 부서ID, 사원명, 레벨 정보를 반환합니다.

 

Oracle에서의 계층형 질의문:

Oracle에서는 START WITH, CONNECT BY PRIOR, ORDER SIBLINGS BY 절을 사용하여 계층형 질의를 수행합니다.

예시를 들어보겠습니다. 아래와 같은 조직 구조를 가정해 봅시다.

부서
|-- 사원1
|-- 사원2
|   |-- 사원3
|-- 사원4

SELECT 사원ID, 부서ID, 사원명, LEVEL AS 레벨
FROM 사원
START WITH 상사ID IS NULL
CONNECT BY PRIOR 사원ID = 상사ID
ORDER SIBLINGS BY 사원ID;

위 쿼리는 Oracle에서 사용되는 전형적인 계층형 질의문입니다. START WITH로 최상위 노드를 지정하고 CONNECT BY PRIOR로 계층적 관계를 정의합니다. ORDER SIBLINGS BY는 형제 노드들을 정렬하는데 사용됩니다.

결과는 레벨 순서로 정렬되며, 각 사원의 사원ID, 부서ID, 사원명, 레벨 정보를 반환합니다.

이러한 방법을 사용하면 계층적 데이터를 쉽게 검색하고 표시할 수 있으며, 조직 구조, 제품 계층, 카테고리 트리 등과 같은 다양한 시나리오에 적용할 수 있습니다.