[SQLD] 09. 계층형 질의 (Hierarchical Query)

계층형 질의 (Hierarchical Query)

계층형 질의는 테이블 내에 존재하는 부모-자식 관계의 계층 구조 데이터를 조회하기 위한 SQL 기능임. 주로 조직도, 상품 카테고리, 게시판의 댓글/답글 구조 등을 조회할 때 사용됨. Oracle에서 지원하는 강력한 기능 중 하나임.

---

1. 계층형 질의의 구성 요소

계층형 질의는 SELECT, FROM 절과 함께 다음의 전용 절(Clause)들을 사용하여 구성됨.

키워드	역할	설명
START WITH	시작 조건	계층 구조 탐색을 시작할 최상위 루트(Root) 행을 지정함.
CONNECT BY	연결 조건	부모 행과 자식 행 사이의 관계를 정의함.
PRIOR	연결 방향 지정	CONNECT BY 절에서 사용되며, 부모 행의 컬럼을 가리키는 연산자임. PRIOR의 위치에 따라 순방향 또는 역방향 전개가 결정됨.
LEVEL	계층 수준 (의사컬럼)	계층 구조에서의 깊이를 나타내는 가상 컬럼(Pseudo-column). 루트 노드는 1, 그 자식은 2로 1씩 증가함.
ORDER SIBLINGS BY	형제 노드 정렬	전체 계층 구조는 유지하면서, 동일한 부모를 가진 형제 노드(Sibling) 사이의 정렬 순서를 지정함.

---

2. 계층 구조 전개 방식

2.1 순방향 전개 (Top-Down)

• 부모에서 자식으로 계층을 탐색하는 가장 일반적인 방식임.
• CONNECT BY PRIOR 부모키 = 자식키 형태로 작성됨. PRIOR가 부모 행의 컬럼 앞에 위치함.

/*
 * -- 조직도 순방향 전개 예시 --
 * 최상위 관리자(manager_id IS NULL)부터 시작하여,
 * 자신의 user_id가 다른 사람의 manager_id와 같은 관계(부하직원)를 찾아 아래로 전개함.
 * LEVEL을 이용하여 들여쓰기(indent)를 적용함.
 */
SELECT
    LEVEL,
    LPAD(' ', (LEVEL-1) * 4) || name AS employee_org,
    user_id,
    manager_id
FROM
    User
START WITH
    manager_id IS NULL -- 시작점: 최상위 노드
CONNECT BY
    PRIOR user_id = manager_id; -- 연결 규칙: 부모의 user_id = 자식의 manager_id

2.2 역방향 전개 (Bottom-Up)

• 자식에서 부모로 계층을 거슬러 올라가는 방식임.
• CONNECT BY PRIOR 자식키 = 부모키 형태로 작성됨. PRIOR가 자식 행의 컬럼 앞에 위치함.

/*
 * -- 특정 직원의 상위 보고 체계 조회 --
 * 'choonsik' 직원부터 시작하여,
 * 자신의 manager_id가 다른 사람의 user_id와 같은 관계(상사)를 찾아 위로 전개함.
 */
SELECT
    LEVEL,
    name,
    user_id,
    manager_id
FROM
    User
START WITH
    name = 'choonsik' -- 시작점: 최하위 노드
CONNECT BY
    PRIOR manager_id = user_id; -- 연결 규칙: 자식의 manager_id = 부모의 user_id

---

3. 계층 내 정렬 (ORDER SIBLINGS BY)

일반 ORDER BY 절을 사용하면 계층 구조가 모두 깨지고 지정된 컬럼 기준으로 전체 결과가 정렬됨. 계층 구조를 유지하면서 특정 그룹 내에서만 정렬하고 싶을 때는 ORDER SIBLINGS BY를 사용해야 함.

/*
 * -- 형제 노드 정렬 예시 --
 * 전체 조직도 계층은 유지하되,
 * 동일한 관리자(부모)를 가진 직원(형제)들 사이에서는 가입일(registration_date) 순으로 정렬함.
 */
SELECT
    LEVEL,
    LPAD(' ', (LEVEL-1) * 4) || name AS employee_org,
    registration_date
FROM
    User
START WITH
    manager_id IS NULL
CONNECT BY
    PRIOR user_id = manager_id
ORDER SIBLINGS BY
    registration_date ASC;

---

4. 실무 예시: 게시판 댓글/답글 조회

계층형 쿼리는 자기 자신을 참조하는 테이블(Self-Referencing Table) 구조에서 매우 유용함. Comment 테이블의 comment_id(부모키)와 parent_comment_id(자식키) 관계가 대표적임.

/*
 * -- 특정 게시물의 댓글 및 답글 계층형 조회 --
 * 67번 게시물의 원본 댓글(parent_comment_id IS NULL)부터 시작하여,
 * 답글들을 계층적으로 조회함.
 * 같은 댓글에 달린 답글들은 최신순으로 정렬함.
 */
SELECT
    LEVEL,
    LPAD('└> ', (LEVEL-1) * 4) || comment_text AS comment_thread,
    creation_date
FROM
    Comment
START WITH
    post_id = 67 AND parent_comment_id IS NULL
CONNECT BY
    PRIOR comment_id = parent_comment_id
ORDER SIBLINGS BY
    creation_date DESC;

---

5. 시험 문제 유형 및 함정 포인트

• PRIOR의 위치: CONNECT BY 절에서 PRIOR 연산자의 위치에 따라 순방향/역방향 전개가 결정되므로, 쿼리 결과를 예측하는 문제가 자주 출제됨.
  • PRIOR 부모 = 자식: 순방향
  • PRIOR 자식 = 부모: 역방향
• ORDER BY vs ORDER SIBLINGS BY: 두 명령어의 차이점을 명확히 알아야 함. 계층 구조를 유지하며 정렬해야 하는 경우 ORDER SIBLINGS BY를 사용해야 한다는 점이 핵심.
• 가상 컬럼 LEVEL: LEVEL의 의미(루트=1)와 활용법(들여쓰기 등)을 묻는 문제가 나올 수 있음.
• WHERE 절과의 관계: WHERE 절은 계층형 쿼리의 모든 전개가 끝난 후 최종 결과에 대한 필터링을 수행함. 이로 인해 부모 노드가 WHERE 조건에 의해 필터링되면, 그 하위 자식 노드들도 모두 결과에서 제외됨. (단, CONNECT BY 절의 조건을 필터링하는 것과는 다름)

6. DBMS별 문법 차이

• 계층형 질의 (START WITH, CONNECT BY): Oracle의 고유한 기능으로, SQLD 시험에서는 Oracle 문법을 기준으로 출제됨.
• 재귀적 CTE (Recursive CTE): 다른 DBMS(SQL Server, MySQL 8.0+, PostgreSQL 등)에서는 WITH RECURSIVE 구문을 사용하는 재귀 공통 테이블 표현식(Recursive CTE)으로 계층 구조를 구현함. 문법이 완전히 다르지만 동일한 목적을 수행함.

구분	Oracle	ANSI SQL (및 대부분의 DBMS)
문법	START WITH ... CONNECT BY ...	WITH RECURSIVE ...
특징	직관적이고 간결한 문법	더 복잡하지만 유연성이 높고 표준에 가까움

SQLD 자격증을 준비하는 입장에서는 Oracle의 CONNECT BY 문법을 중심으로 학습하는 것이 중요함.