[sql] 계층형 쿼리(Hierarchical Query)

계층형 쿼리(Hierarchical Query)는 데이터베이스에서 부모-자식 관계를 가진 데이터를 조회할 때 사용하는 쿼리입니다. 주로 조직도, 파일 시스템, 제품 카테고리 등과 같이 계층 구조로 구성된 데이터를 다룰 때 유용하다.

🐲 주요 개념
부모-자식 관계: 각 데이터 항목은 부모와 자식 항목을 가질 수 있으며, 이를 통해 트리 구조를 형성한다.
루트 노드: 계층 구조의 최상위 노드를 의미한다.
Leaf 노드: 자식이 없는 노드를 의미한다.

 

🐲주요 예약어

1. START WITH: 계층 구조의 루트 노드를 지정한다.
2. CONNECT BY: 계층형 쿼리를 작성해 부모와 자식 간의 관계를 정의합니다. PRIOR 키워드를 사용한다.
3. PRIOR: 이전 단계의 값을 참조하는 데 사용된다.
4. LEVEL: 긱 노드의 깊이를 나타낸다.

계층형 쿼리를 작성하는 데는 일반적으로 CONNECT BY 절과 START WITH 절을 사용한다.

🐲 기본 구문
SELECT 데이터1,데이터2
FROM 테이블이름
START WITH 상태조건
CONNECT BY PRIOR 부모칼럼= 자식칼럼;

START WITH: 계층 구조의 시작점을 정의한다. (루트 노드)
CONNECT BY PRIOR: 부모와 자식 항목 간의 관계를 정의합니다. PRIOR 키워드는 부모 항목을 참조한다.

🐲 예시
예를 들어, 직원(Employee) 테이블이 다음과 같은 구조라고 가정한다:
EMP_ID EMP_NAME MANAGER_ID
1 Alice NULL
2 Bob 1
3 Charlie 1
4 David 2
5 Eva 2
이 테이블에서 Alice를 루트로 하는 계층형 쿼리를 작성할 수 있다.

SELECT EMP_ID, EMP_NAME, LEVEL
FROM Employee
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMP_ID = MANAGER_ID;

 

🐲  결과
EMP_ID   EMP_NAME   LEVEL
1                Alice                  1
2                Bob                   2
3                Charlie              2
4                David                3
5                Eva                   3
LEVEL: 각 항목의 깊이를 나타내며 루트 노드가 1, 그 다음 레벨이 2로 증가한다.

• 계층형 쿼리는 성능에 영향을 줄 수 있으므로, 적절한 인덱스를 설정하는 것이 중요하다.
• 무한 루프에 빠지지 않도록 데이터의 구조를 잘 관리해야 한다.
• 계층형 쿼리는 조직도나 트리 구조의 데이터를 효과적으로 조회하는 데 매우 유용하다.