계층형 쿼리 완벽 정리
Oracle CONNECT BY·LEVEL·SYS_CONNECT_BY_PATH와 SQL Server CTE 재귀 구문까지 한눈에 정리
1. 계층형 쿼리란?
정의
테이블에 저장된 데이터가 부모-자식 관계(셀프 조인 구조)를 가질 때, 이를 트리 형태로 조회하는 쿼리입니다. 조직도, 메뉴 구조, BOM(Bill of Materials) 등에서 사용됩니다.
예시 테이블 — EMP (사원)
| EMP_ID | NAME | MGR_ID |
|---|---|---|
| 100 | 김사장 | NULL |
| 200 | 이부장 | 100 |
| 300 | 박과장 | 200 |
| 400 | 최대리 | 300 |
| 500 | 정부장 | 100 |
MGR_ID는 상위 직원의 EMP_ID를 참조하며, 최상위(루트)는 MGR_ID가 NULL입니다.
2. Oracle 계층형 쿼리 기본 구문
기본 구조
SELECT LEVEL, EMP_ID, NAME, MGR_ID
FROM EMP
START WITH MGR_ID IS NULL
CONNECT BY PRIOR EMP_ID = MGR_ID;
| 키워드 | 역할 |
|---|---|
| START WITH | 계층의 시작점(루트 노드)을 지정하는 조건 |
| CONNECT BY PRIOR | 부모-자식 관계를 정의하는 조건 |
| LEVEL | 루트를 1로 시작하는 깊이(의사 컬럼) |
실행 결과
| LEVEL | EMP_ID | NAME | MGR_ID |
|---|---|---|---|
| 1 | 100 | 김사장 | NULL |
| 2 | 200 | 이부장 | 100 |
| 3 | 300 | 박과장 | 200 |
| 4 | 400 | 최대리 | 300 |
| 2 | 500 | 정부장 | 100 |
3. CONNECT BY PRIOR 방향 (순방향 vs 역방향)
PRIOR 위치에 따른 탐색 방향
PRIOR는 "이전에 읽은 행"을 의미합니다. PRIOR가 붙는 컬럼이 부모 역할을 합니다.
| 방향 | 구문 | 의미 |
|---|---|---|
| 순방향 (Top-Down) | CONNECT BY PRIOR EMP_ID = MGR_ID | 부모 → 자식 방향으로 탐색. 루트에서 시작하여 아래로 내려감 |
| 역방향 (Bottom-Up) | CONNECT BY PRIOR MGR_ID = EMP_ID | 자식 → 부모 방향으로 탐색. 특정 노드에서 루트까지 올라감 |
역방향 예시
SELECT LEVEL, EMP_ID, NAME, MGR_ID
FROM EMP
START WITH EMP_ID = 400 -- 최대리부터 시작
CONNECT BY PRIOR MGR_ID = EMP_ID;
결과: 최대리(LEVEL 1) → 박과장(LEVEL 2) → 이부장(LEVEL 3) → 김사장(LEVEL 4) 순서로 루트까지 올라갑니다.
암기 팁
"PRIOR는 부모 쪽에 붙인다" — 순방향에서는 PRIOR가 부모 컬럼(EMP_ID)에, 역방향에서는 PRIOR가 부모를 가리키는 컬럼(MGR_ID)에 붙습니다. 즉, PRIOR 쪽이 이미 읽은(부모) 행입니다.
4. 계층형 쿼리 주요 함수·키워드
의사 컬럼·함수 정리
| 키워드 | 설명 | 예시 |
|---|---|---|
| LEVEL | 현재 깊이 (루트=1) | WHERE LEVEL <= 3 |
| CONNECT_BY_ROOT | 루트 노드의 컬럼 값 반환 | CONNECT_BY_ROOT NAME |
| CONNECT_BY_ISLEAF | 리프(말단) 노드이면 1, 아니면 0 | CONNECT_BY_ISLEAF = 1 |
| SYS_CONNECT_BY_PATH | 루트→현재 노드까지 경로를 문자열로 반환 | SYS_CONNECT_BY_PATH(NAME, '/') |
| ORDER SIBLINGS BY | 같은 레벨(형제) 노드 간 정렬 | ORDER SIBLINGS BY NAME |
SYS_CONNECT_BY_PATH 예시
SELECT LEVEL,
LPAD(' ', (LEVEL-1)*2) || NAME AS TREE,
SYS_CONNECT_BY_PATH(NAME, '/') AS PATH
FROM EMP
START WITH MGR_ID IS NULL
CONNECT BY PRIOR EMP_ID = MGR_ID;
| LEVEL | TREE | PATH |
|---|---|---|
| 1 | 김사장 | /김사장 |
| 2 | 이부장 | /김사장/이부장 |
| 3 | 박과장 | /김사장/이부장/박과장 |
| 4 | 최대리 | /김사장/이부장/박과장/최대리 |
| 2 | 정부장 | /김사장/정부장 |
ORDER SIBLINGS BY vs ORDER BY
- ORDER BY — 계층 구조를 무시하고 전체를 정렬하므로 트리 구조가 깨짐
- ORDER SIBLINGS BY — 같은 부모를 가진 형제 노드끼리만 정렬하므로 트리 구조가 유지됨
5. LPAD를 이용한 트리 출력
LPAD 트리 표현 패턴
SELECT LPAD(' ', (LEVEL-1)*2) || NAME AS ORG_CHART
FROM EMP
START WITH MGR_ID IS NULL
CONNECT BY PRIOR EMP_ID = MGR_ID;
LPAD(문자열, 길이)는 왼쪽에 공백(또는 지정 문자)을 채워 들여쓰기 효과를 냅니다. LEVEL이 깊어질수록 공백이 늘어나 트리 형태가 됩니다.
결과:
김사장
이부장
박과장
최대리
정부장
LPAD 공식
LPAD(' ', (LEVEL-1) * N) — N은 들여쓰기 간격. 보통 2 또는 4를 사용합니다. LEVEL 1(루트)은 (1-1)*2 = 0으로 들여쓰기 없음, LEVEL 2는 2칸, LEVEL 3은 4칸입니다.
6. WHERE절 위치에 따른 차이
WHERE vs CONNECT BY 조건
| 위치 | 동작 | 하위 노드 |
|---|---|---|
| WHERE 조건 | 계층 전개 후 해당 행만 제거 | 하위 노드는 남아있음 |
| CONNECT BY 조건 | 해당 노드에서 계층 전개 중단 | 해당 노드와 하위 노드 모두 제거 |
예: WHERE NAME != '이부장'은 이부장만 제거하고 하위(박과장, 최대리)는 유지. CONNECT BY 조건에서 이부장을 제외하면 이부장 이하 전체가 사라집니다.
7. SQL Server CTE 재귀 구문
개념
SQL Server(및 표준 SQL)에서는 CONNECT BY 대신 WITH RECURSIVE (재귀 CTE)를 사용합니다. CTE(Common Table Expression)가 자기 자신을 참조하여 반복적으로 실행됩니다.
기본 구문
WITH RECURSIVE EMP_TREE AS (
-- 앵커 멤버 (시작점)
SELECT EMP_ID, NAME, MGR_ID, 1 AS LVL
FROM EMP
WHERE MGR_ID IS NULL
UNION ALL
-- 재귀 멤버 (반복)
SELECT E.EMP_ID, E.NAME, E.MGR_ID, T.LVL + 1
FROM EMP E
JOIN EMP_TREE T ON E.MGR_ID = T.EMP_ID
)
SELECT * FROM EMP_TREE;
| 구성 요소 | 역할 | Oracle 대응 |
|---|---|---|
| 앵커 멤버 | 재귀의 시작점 (루트 행) | START WITH |
| UNION ALL | 앵커와 재귀를 결합 | - |
| 재귀 멤버 | 자기 자신(CTE)을 참조하여 반복 | CONNECT BY PRIOR |
SQL Server 주의사항
- SQL Server에서는 RECURSIVE 키워드를 생략 — WITH EMP_TREE AS (...) 형태로 작성합니다 (RECURSIVE 없이도 재귀 동작)
- 무한 루프 방지 — OPTION (MAXRECURSION 100)으로 최대 재귀 깊이를 제한합니다 (기본값: 100)
- LEVEL 직접 관리 필요 — Oracle처럼 LEVEL 의사 컬럼이 없으므로 직접 LVL + 1로 계산합니다
8. Oracle CONNECT BY vs CTE 비교
| 항목 | Oracle CONNECT BY | CTE 재귀 |
|---|---|---|
| 시작점 | START WITH | 앵커 멤버 (WHERE절) |
| 관계 정의 | CONNECT BY PRIOR | 재귀 멤버 JOIN 조건 |
| 깊이 | LEVEL (자동 제공) | 직접 계산 (LVL + 1) |
| 경로 출력 | SYS_CONNECT_BY_PATH | 문자열 연결(|| 또는 +)로 직접 구현 |
| 형제 정렬 | ORDER SIBLINGS BY | 별도 정렬 로직 필요 |
| 표준 여부 | Oracle 독자 구문 | ANSI SQL 표준 |
9. 시험 포인트
SQLD 빈출 핵심 정리
- 1. PRIOR 위치가 방향을 결정한다 — PRIOR가 부모 PK 쪽이면 순방향(Top-Down), FK 쪽이면 역방향(Bottom-Up)
- 2. LEVEL은 1부터 시작 — 루트 노드의 LEVEL은 항상 1 (0이 아님)
- 3. ORDER SIBLINGS BY와 ORDER BY의 차이 — ORDER BY는 계층 구조를 깨뜨리지만, ORDER SIBLINGS BY는 형제끼리만 정렬
- 4. WHERE vs CONNECT BY 조건 차이 — WHERE는 해당 행만 제거, CONNECT BY 조건은 해당 노드 이하 전체 제거(가지치기)
- 5. SYS_CONNECT_BY_PATH의 구분자 — 첫 번째 인수는 출력할 컬럼, 두 번째 인수는 구분자 문자열
- 6. CONNECT_BY_ISLEAF — 리프 노드 판별 (자식이 없으면 1, 있으면 0)
- 7. CTE 재귀는 UNION ALL 필수 — 앵커 멤버와 재귀 멤버를 반드시 UNION ALL로 결합
- 8. CONNECT BY는 Oracle 전용 — SQL Server에서는 사용 불가, CTE 재귀로 대체해야 함
개념을 확인했다면 문제로 실력을 검증해보세요