계층형 쿼리(Hierarchical Query)

Table of Contents

1. 계층형 쿼리(Hierarchical Query)란?

계층형 쿼리(Hierarchical Query)는 부모-자식 관계(트리 구조)를 가진 데이터를 조회하는 SQL 기법입니다. 일반적인 SQL 쿼리는 평면적(Flat) 데이터를 조회하는 반면, 계층형 쿼리는 트리 구조(Tree Structure)를 탐색할 수 있습니다.

예제:

조직도 (CEO → 부서장 → 직원)
제품 카테고리 (대분류 → 중분류 → 소분류)
파일 시스템 (폴더 → 하위 폴더 → 파일)
네트워크 노드 (서버 → 클라이언트)
가족 계보 (할아버지 → 아버지 → 자식)

2. 기본 문법

SELECT [컬럼들]
FROM 테이블
START WITH [루트 조건]
CONNECT BY [계층적 관계 정의]
[ORDER SIBLINGS BY 컬럼명];

START WITH : 계층 구조의 시작점을 지정
CONNECT BY PRIOR : 부모-자식 관계 정의
ORDER SIBLINGS BY : 동일한 부모를 가진 형제 노드들을 정렬

3. 예제 데이터 (EMPLOYEES 테이블)

테이블 구조

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,  -- 직원 ID
    emp_name VARCHAR2(50),       -- 직원 이름
    manager_id NUMBER,           -- 상사의 ID (NULL이면 최상위)
    salary NUMBER                -- 급여
);


데이터 샘플

INSERT INTO employees VALUES (1, 'Alice (CEO)', NULL, 10000);
INSERT INTO employees VALUES (2, 'Bob (Manager)', 1, 7000);
INSERT INTO employees VALUES (3, 'Charlie (Manager)', 1, 7000);
INSERT INTO employees VALUES (4, 'David (Staff)', 2, 5000);
INSERT INTO employees VALUES (5, 'Eve (Staff)', 2, 5000);
INSERT INTO employees VALUES (6, 'Frank (Staff)', 3, 4000);
INSERT INTO employees VALUES (7, 'Grace (Intern)', 4, 3000);
COMMIT;


데이터 구조

Alice (CEO)          -- emp_id = 1 (최상위)
 ├── Bob (Manager)    -- emp_id = 2, manager_id = 1
 │   ├── David (Staff) -- emp_id = 4, manager_id = 2
 │   │   ├── Grace (Intern) -- emp_id = 7, manager_id = 4
 │   ├── Eve (Staff)  -- emp_id = 5, manager_id = 2
 ├── Charlie (Manager) -- emp_id = 3, manager_id = 1
     ├── Frank (Staff) -- emp_id = 6, manager_id = 3

4. 계층형 쿼리 예제

(1) 기본 계층형 쿼리

SELECT emp_id, emp_name, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

결과

EMP_ID	EMP_NAME	MANAGER_ID	LEVEL
1	Alice (CEO)	NULL	1
2	Bob (Manager)	1	2
4	David (Staff)	2	3
7	Grace (Intern)	4	4
5	Eve (Staff)	2	3
3	Charlie (Manager)	1	2
6	Frank (Staff)	3	3

LEVEL : 트리 깊이를 나타냄 (CEO가 1, 그 아래 직원이 2, …)
PRIOR emp_id = manager_id : 부모(emp_id)가 자식(manager_id)과 연결됨
START WITH manager_id IS NULL : CEO(Alice)가 최상위 루트 노드임

(2) 계층 구조 시각화 (LPAD 활용)

SELECT LPAD(' ', LEVEL * 4 - 4) || emp_name AS hierarchy, emp_id, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

결과

Alice (CEO)
    Bob (Manager)
        David (Staff)
            Grace (Intern)
        Eve (Staff)
    Charlie (Manager)
        Frank (Staff)

(3) ORDER SIBLINGS BY 사용 (형제 노드 정렬)

SELECT emp_id, emp_name, manager_id, LEVEL
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY emp_name;

형제 노드(같은 부모를 가진 직원)들이 emp_name 기준으로 정렬됨.

(4) 상위(부모) → 하위(자식) 탐색 (기본)

위 쿼리는 루트(CEO)부터 아래로 탐색하는 구조입니다.
하지만 특정 직원부터 아래 조직도를 보고 싶다면 START WITH에서 지정합니다.

SELECT emp_id, emp_name, manager_id, LEVEL
FROM employees
START WITH emp_name = 'Bob (Manager)'
CONNECT BY PRIOR emp_id = manager_id;

결과 (Bob의 하위 직원만 조회)

EMP_ID	EMP_NAME	MANAGER_ID	LEVEL
2	Bob (Manager)	1	1
4	David (Staff)	2	2
7	Grace (Intern)	4	3
5	Eve (Staff)	2	2

(5) 특정 직원의 전체 경로(경로 추적)

CONNECT BY PRIOR manager_id = emp_id를 사용하면 하위(자식) → 상위(부모) 탐색이 가능함.

SELECT emp_id, emp_name, manager_id, LEVEL
FROM employees
START WITH emp_name = 'Grace (Intern)'
CONNECT BY PRIOR manager_id = emp_id;

결과 (Grace의 상위 직원 조회)

EMP_ID	EMP_NAME	MANAGER_ID	LEVEL
7	Grace (Intern)	4	1
4	David (Staff)	2	2
2	Bob (Manager)	1	3
1	Alice (CEO)	NULL	4

5. SYS_CONNECT_BY_PATH로 전체 경로 출력

SELECT emp_name, SYS_CONNECT_BY_PATH(emp_name, ' -> ') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

결과

Alice (CEO)
 -> Bob (Manager)
    -> David (Staff)
        -> Grace (Intern)
    -> Eve (Staff)
 -> Charlie (Manager)
    -> Frank (Staff)

6. CONNECT_BY_ROOT로 최상위 노드 찾기

SELECT emp_name, CONNECT_BY_ROOT emp_name AS root_manager
FROM employees
START WITH emp_name = 'Frank (Staff)'
CONNECT BY PRIOR manager_id = emp_id;

결과

EMP_NAME	ROOT_MANAGER
Frank (Staff)	Alice (CEO)

결론

기능	설명
`START WITH`	트리의 시작점 지정
`CONNECT BY PRIOR`	부모-자식 관계 설정
`ORDER SIBLINGS BY`	같은 부모를 가진 형제 노드 정렬
`SYS_CONNECT_BY_PATH`	전체 경로 출력
`CONNECT_BY_ROOT`	루트(최상위) 찾기