티스토리 뷰

카테고리 없음

계층형 질의 START WITH, CONNECT BY 총정리

bhey35 2026. 3. 12. 13:00

관계형 데이터베이스(RDBMS)의 2차원 평면 테이블 구조에서 사내 조직도, 메뉴 카테고리, 부품 명세서(BOM)와 같은 부모-자식 관계의 '트리(Tree) 구조'를 표현하기란 쉽지 않습니다. 복잡한 서브쿼리나 윈도우 함수가 데이터를 수평적으로 분할하고 분석하는 데 탁월하다면, 수직적인 계층 구조를 탐색하고 전개하는 데에는 오라클(Oracle)의 '계층형 질의(Hierarchical Query)'가 절대적인 권력을 쥡니다. 무한 루프에 빠질 것 같은 재귀 연산을 단 몇 줄의 쿼리로 우아하게 풀어내는 START WITH와 CONNECT BY의 작동 원리부터, 타 데이터베이스와의 구조적 차이점까지 핵심만 1,500자로 압축하여 완벽하게 해부해 드립니다.

1. 트리의 뿌리와 가지 방향: START WITH & CONNECT BY

트리 구조를 만들려면 먼저 어디서부터 시작할지 '뿌리(Root)'를 정해야 합니다. START WITH 구문이 바로 이 최상위 노드를 지정하는 역할을 하며, START WITH mgr IS NULL처럼 사장님(관리자가 없는 사람)을 찾는 조건이 들어갑니다. 뿌리를 찾았다면 CONNECT BY를 통해 부모와 자식의 관계를 연결합니다. 여기서 핵심은 PRIOR 키워드의 위치입니다. CONNECT BY PRIOR empno = mgr이라고 쓰면 '직전(PRIOR) 행의 사원번호가 다음 행의 관리자번호가 된다'는 뜻으로 위에서 아래로 파고드는 '순방향(Top-Down)' 탐색을, 반대로 쓰면 말단 직원부터 사장님까지 거슬러 올라가는 '역방향(Bottom-Up)' 탐색을 수행하게 됩니다.

요약: START WITH는 트리의 시작점(루트 노드)을 지정하며, CONNECT BY PRIOR 구문은 PRIOR 키워드의 위치에 따라 위에서 아래로(Top-Down), 혹은 아래에서 위로(Bottom-Up) 계층을 탐색하는 방향을 결정하는 핵심 연결 고리입니다.

계층형 질의 START WITH&#44; CONNECT BY 총정리 — 계층형 질의 START WITH, CONNECT BY 총정리

2. 계층의 깊이와 시각화 마법: LEVEL 가상 컬럼

트리 구조를 화면에 예쁘게 출력하려면 해당 노드가 몇 번째 깊이에 있는지 파악해야 합니다. 오라클은 계층형 쿼리가 실행될 때 LEVEL이라는 강력한 가상 컬럼(Pseudo-column)을 자동으로 제공합니다. 최상위 루트 노드는 LEVEL 1이 되고, 아래로 한 단계씩 깊어질 때마다 숫자가 1씩 증가합니다. 이를 LPAD 함수와 결합하여 LPAD(' ', 2*(LEVEL-1)) || ename 처럼 작성하면, 레벨이 깊어질수록 이름 앞에 공백이 늘어나며 애플리케이션의 도움 없이도 SQL 쿼리 결과창에서 완벽하게 들여쓰기 된 직관적인 조직도 텍스트 뷰를 즉시 확인할 수 있습니다.

요약: LEVEL 가상 컬럼은 현재 노드의 계층적 깊이(Depth)를 숫자로 반환해 줍니다. 이를 문자열 조작 함수(LPAD 등)와 결합하면 부모-자식 간의 들여쓰기를 구현하여 트리 구조를 시각적으로 아주 쉽게 표현할 수 있습니다.

3. 형제간 정렬과 잎사귀 찾기: ORDER SIBLINGS BY & ISLEAF

계층형 쿼리 마지막에 무심코 일반적인 ORDER BY를 쓰면, 공들여 엮어놓은 부모-자식 간의 트리 구조가 완전히 박살 나고 데이터가 평면적으로 뒤섞여 버립니다. 트리 형태를 고스란히 유지한 채 같은 레벨의 형제(Siblings) 노드끼리만 정렬하려면 반드시 ORDER SIBLINGS BY 절을 사용해야 합니다. 또한, CONNECT_BY_ISLEAF 가상 컬럼을 활용하면 자식이 없는 최하단 말단 노드(Leaf)인 경우 숫자 1을, 자식이 있는 부모 노드면 0을 반환하므로 조직도의 막내나 카테고리의 최종 분류 데이터만 쏙 골라낼 때 매우 유용하게 쓰입니다.

요약: 트리가 망가지지 않게 같은 계층끼리만 정렬하려면 ORDER SIBLINGS BY를 사용해야 합니다. CONNECT_BY_ISLEAF는 말단 노드 여부를 1과 0으로 반환하여 최하위 자식 데이터만 필터링할 때 핵심적인 역할을 합니다.

4. 무서운 가지치기(Pruning) 전략: WHERE vs CONNECT BY 조건

특정 부서나 불필요한 노드를 트리에서 제외하고 싶을 때, 조건을 어디에 명시하느냐에 따라 쿼리 결과가 천양지차로 달라집니다. WHERE 절에 조건을 주면 트리 계층이 모두 완성된 후 마지막 필터링 단계에서 해당 '단일 노드'만 쏙 숨깁니다(그 아래 자식들은 그대로 출력됨). 하지만 CONNECT BY 절에 AND deptno != 10처럼 조인 조건을 추가하면, 해당 노드뿐만 아니라 그 아래에 주렁주렁 매달린 '자식 가지 전체'를 아예 탐색조차 하지 않고 싹둑 잘라내는(Pruning) 무시무시한 가지치기가 발생합니다. 비즈니스 로직에 맞춰 이 둘을 명확히 구분하여 사용해야 데이터 누락을 막을 수 있습니다.

요약: 트리 구조에서 특정 데이터를 제외할 때, WHERE 절을 쓰면 해당 노드만 숨겨지고 자식들은 유지됩니다. 반면 CONNECT BY 절에 조건을 주면 해당 노드를 포함한 하위 가지 전체가 통째로 잘려나가는(Pruning) 차이가 있습니다.

5. 아키텍처의 차이: 오라클 계층 질의 vs 타 DB의 재귀 CTE

오라클 개발자들에게는 START WITH 문법이 호흡처럼 자연스럽지만, 이기종 데이터베이스 마이그레이션 시 SQL Server(MS SQL)나 PostgreSQL 같은 환경을 만나면 이 문법은 문법 에러를 뿜으며 전혀 작동하지 않습니다. 타 RDBMS 환경에서는 ANSI 표준인 재귀 CTE(Common Table Expression), 즉 WITH RECURSIVE 구문을 사용하여 계층을 전개하기 때문입니다. CTE 방식은 UNION ALL을 기준으로 상단에 앵커 멤버(루트 조건)를 정의하고, 하단에 자기 자신을 반복 조인하는 재귀 멤버를 배치하는 구조를 가집니다. 따라서 시스템 이관 작업을 수행할 때는 이 두 가지 계층형 쿼리 패러다임의 구조적 차이를 완벽히 이해하고 로직을 1:1로 변환(Conversion)하는 능력이 필수적입니다.

요약: 오라클의 고유 문법인 START WITH / CONNECT BY는 SQL Server 등 타 DB에서는 호환되지 않습니다. 이기종 환경에서는 ANSI 표준 구문인 WITH RECURSIVE (재귀 CTE) 방식을 사용하여 동일한 트리 구조 탐색 로직을 구현해야 합니다.