[SQL] Oracle 계층형 쿼리(Hierarchical Query) 문법 정리 - START WITH / CONNECT BY / PRIOR

[ 계층형 쿼리 Hierarchy Query ]

1. 계층형 쿼리Hierarchy Query 란?

• 트리 구조 데이터를 조회하는 쿼리
• 수직적으로 관계를 맺고 있는 행들의 계층형 정보 조회 가능

SELECT 컬럼들
FROM 테이블
START WITH 루트조건
CONNECT BY PRIOR 부모컬럼 = 자식컬럼
[ORDER SIBLINGS BY 정렬기준컬럼];

구분	의미
START WITH	트리의 시작점(루트) 지정
CONNECT BY	부모-자식 연결 조건 지정
PRIOR	이전 행(부모 행)
ORDER SIBLINGS BY	같은 부모를 가진 형제끼리 정렬

 

1) SELECT 절에서 사용하는 계층형 키워드

키워드	의미	특징
LEVEL	현재 행의 깊이 (루트=1)	SELECT, WHERE, ORDER BY 사용 가능
CONNECT_BY_ROOT	최상위(루트) 값 반환	현재 행 기준으로 루트 추적
CONNECT_BY_ISLEAF	리프 노드 여부	1 = 자식 없음, 0 = 자식 있음
SYS_CONNECT_BY_PATH(컬럼명, char)	루트 → 현재까지 경로	char = 구분자

 

2) PRIOR 방향

방향	설명	조건
부모 → 자식	아래로 탐색	PRIOR 부모 = 자식
자식 → 부모	위로 탐색	PRIOR 자식 = 부모

EMP_ID	POSITION	PARENT_ID
1	사장	NULL
2	개발팀장	1
3	영업팀장	1
4	백엔드개발	2
5	프론트엔드	2
6	국내영업	3
7	해외영업	3

SELECT
    LEVEL,
    LPAD(' ', (LEVEL-1)*2) || POSITION AS POSITION_LEVEL,   -- 들여쓰기
    EMP_ID,
    PARENT_ID,
    SYS_CONNECT_BY_PATH(POSITION, '>')       AS 전체경로
FROM emp
START WITH PARENT_ID IS NULL           -- 루트 지정
CONNECT BY PRIOR EMP_ID = PARENT_ID   -- 부모→자식 방향
ORDER SIBLINGS BY EMP_ID;             -- 같은 레벨 정렬

LEVEL	POSITION_LEVEL	EMP_ID	PARENT_ID	전체경로
1	사장	1	NULL	>사장
2	개발팀장	2	1	>사장>개발팀장
3	백엔드개발	4	2	>사장>개발팀장>백엔드개발
3	프론트엔드	5	2	>사장>개발팀장>프론트엔드
2	영업팀장	3	1	>사장>영업팀장
3	국내영업	6	3	>사장>영업팀장>국내영업
3	해외영업	7	3	>사장>영업팀장>해외영업

 

 

2. DB별 계층형 쿼리 비교

DB	방식	문법
Oracle	CONNECT BY (전용 문법)	START WITH ... CONNECT BY PRIOR
MySQL 8.0+	재귀 CTE	WITH RECURSIVE
PostgreSQL	재귀 CTE	WITH RECURSIVE
SQL Server	재귀 CTE	WITH ... AS (UNION ALL)
SQLite	재귀 CTE	WITH RECURSIVE