계층형 질의: START WITH·CONNECT BY | SQLP

핵심 요약

부모-자식 데이터를 순회하는 START WITH·CONNECT BY PRIOR와 LEVEL·경로 함수를 익힌다.

핵심 질문

계층형 질의: START WITH·CONNECT BY에서 반드시 구분해야 할 개념과 결과 규칙은 무엇인가?
0건·1건·여러 건과 NULL·동점·중복 데이터에서 결과가 어떻게 달라지는가?
비슷해 보이는 문법과 결과가 같아지는 조건, 달라지는 조건은 무엇인가?
작은 샘플 데이터를 이용해 결과를 실수 없이 예측하는 순서는 무엇인가?

학습 목표

계층 시작점과 부모-자식 연결 방향을 판별한다.
LEVEL·CONNECT_BY_ISLEAF·SYS_CONNECT_BY_PATH를 설명한다.

개념 지도

CODE코드 영역 안에서 좌우로 이동할 수 있습니다.

Root 선택 → Parent·Child 연결 → 깊이 우선 전개 → Cycle·Leaf·경로 확인

핵심 내용

Oracle 계층형 질의는 인접 목록 구조를 트리로 펼친다.

SQL코드 영역 안에서 좌우로 이동할 수 있습니다.

SELECT empno, ename, mgr, LEVEL
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY empno;

START WITH는 루트 행, CONNECT BY는 부모와 자식의 연결 규칙을 정한다. PRIOR가 붙은 표현이 부모 행의 값이다. 위 문장은 부모의 empno와 자식의 mgr를 연결해 위에서 아래로 전개한다.

LEVEL: 루트 1부터 깊이
CONNECT_BY_ISLEAF: 자식 없는 리프 여부
SYS_CONNECT_BY_PATH: 루트부터 현재 행까지 경로
NOCYCLE: 순환으로 인한 오류를 방지하고 순환 정보를 다룰 때 사용

형제끼리 정렬하려면 일반 ORDER BY로 전체 계층을 흐트러뜨리지 말고 ORDER SIBLINGS BY를 사용한다.

흔한 오해와 주의점

PRIOR의 위치를 바꾸면 순회 방향이 달라진다.
WHERE 조건은 계층 연결 자체를 끊는 조건과 같은 효과라고 단정할 수 없다. 시작·연결·잔여 필터의 역할을 구분한다.
순환 가능한 데이터에서는 NOCYCLE과 데이터 품질을 함께 점검한다.

문항 풀이 보강: 상향·하향 탐색과 두 방향 합치기

PRIOR가 붙은 쪽이 부모 행에서 가져오는 표현이다.

SQL코드 영역 안에서 좌우로 이동할 수 있습니다.

-- 부모 empno에서 자식 mgr로 내려감
CONNECT BY PRIOR empno = mgr

-- 현재 행의 부모를 찾아 위로 올라감
CONNECT BY PRIOR mgr = empno

기준 부서의 조상과 자손을 모두 구하려면 두 방향을 각각 탐색한 뒤 UNION으로 기준 노드 중복을 제거할 수 있다.

SQL코드 영역 안에서 좌우로 이동할 수 있습니다.

SELECT dept_id, parent_dept_id
FROM dept
START WITH dept_id = 120
CONNECT BY PRIOR parent_dept_id = dept_id
UNION
SELECT dept_id, parent_dept_id
FROM dept
START WITH dept_id = 120
CONNECT BY parent_dept_id = PRIOR dept_id;

각 계층 쿼리의 LEVEL은 자신의 START WITH 행에서 1부터 시작한다. 두 분기를 합치면 LEVEL이 하나의 절대 깊이가 아니라 각 분기의 기준에 따른 값이라는 점을 유의한다.

처리 개념 순서는 FROM → START WITH → CONNECT BY → 나머지 WHERE다. 일반 WHERE가 자식 행을 제거하더라도 그 행의 후손 탐색이 이미 일어났을 수 있으므로 CONNECT BY 조건과 같은 것으로 보지 않는다.

조직도 예제로 방향 이해하기

SQL코드 영역 안에서 좌우로 이동할 수 있습니다.

SELECT LEVEL, empno, ename, mgr,
       SYS_CONNECT_BY_PATH(ename, '/') AS path,
       CONNECT_BY_ISLEAF AS is_leaf
FROM   emp
START  WITH mgr IS NULL
CONNECT BY NOCYCLE PRIOR empno = mgr
ORDER SIBLINGS BY ename;

PRIOR empno = mgr는 부모의 empno가 자식의 mgr와 같다는 뜻이다. PRIOR의 위치를 바꾸면 탐색 방향이 역전된다.

CODE코드 영역 안에서 좌우로 이동할 수 있습니다.

KING
├─ JONES
│  └─ SCOTT
└─ BLAKE

START WITH는 Root를, CONNECT BY는 부모·자식 연결을, LEVEL은 깊이를 나타낸다. ORDER SIBLINGS BY는 계층을 깨지 않고 같은 부모 아래 형제만 정렬한다.

Cycle과 경로

데이터 오류로 A→B→A가 생기면 무한 순환 위험이 있다. NOCYCLE과 CONNECT_BY_ISCYCLE로 탐지할 수 있지만 원천 관계의 무결성도 수정해야 한다.

WHERE 위치 주의

계층을 만든 뒤 WHERE로 행을 제거하는 것과 CONNECT BY 조건에서 가지를 차단하는 것은 결과가 다르다. 특정 상태의 노드와 그 하위 전체를 제외할지, 노드만 숨기고 하위는 유지할지 업무 의미를 정한다.

Recursive Subquery Factoring(WITH 절의 Anchor Query와 UNION ALL 재귀 Query)은 표준적 대안이며 검색 방향·Cycle·출력 순서를 명시적으로 설계한다.

결과를 검증하는 순서

각 Query Block이 만드는 한 행의 의미를 먼저 적습니다.
조건을 적용하기 전 원본 행과 적용 후 남는 행을 작은 표로 그립니다.
NULL 비교가 TRUE, FALSE, UNKNOWN 중 무엇인지 구분합니다.
중복 제거, 그룹화, 정렬과 행 제한이 적용되는 순서를 확인합니다.
데이터가 0건·1건·여러 건일 때도 같은 규칙이 성립하는지 검증합니다.

실무와 시험에서 함께 확인할 항목

ORDER BY가 없다면 결과 순서를 가정하지 않습니다.
문자열·숫자·날짜 비교에서는 데이터 타입과 명시적 형변환을 확인합니다.
같은 결과처럼 보이는 SQL도 NULL과 중복이 있을 때 달라질 수 있습니다.
문법을 외우기 전에 샘플 데이터 3~5행으로 결과를 직접 계산합니다.

마지막 점검

작성 순서가 아니라 SQL의 논리적 처리 순서로 결과를 계산합니다.
NULL을 0이나 빈 값과 같은 것으로 취급하지 않습니다.
ORDER BY가 없는 결과 순서와 DISTINCT 없는 중복 제거를 가정하지 않습니다.
비슷한 문법은 0건·다건·NULL 데이터를 넣어 결과가 정말 같은지 확인합니다.

복습 문제

루트 행의 LEVEL 값은?
하향 전개에서 PRIOR empno = mgr의 부모 컬럼은 무엇인가?
샘플 데이터 3행으로 결과를 직접 계산할 수 있는가?
NULL이 포함될 때 결과가 달라지는 지점은 어디인가?