2025-08-24 19:26

• 계층형 질의는 조직도나 카테고리처럼 부모-자식 관계를 가진 데이터를 쉽게 조회하기 위해 만들어졌습니다.

• START WITH로 시작점을 정하고 CONNECT BY로 관계를 정의하여 원하는 계층 구조를 탐색할 수 있습니다.

• LEVEL, SYS_CONNECT_BY_PATH 같은 특별한 기능들을 활용하면 계층의 깊이나 전체 경로를 파악하는 등 다채로운 분석이 가능합니다.

데이터 관계를 파헤치는 비밀 무기 계층형 질의 완벽 핸드북

데이터베이스를 다루다 보면 단순한 행과 열의 나열을 넘어, 데이터 간의 ‘관계’를 파악해야 하는 순간이 찾아옵니다. 특히 회사 조직도, 상품 카테고리, 포럼의 댓글 구조처럼 상하 관계가 명확한 데이터를 마주했을 때, 우리는 어떻게 이 관계의 실타래를 풀어야 할까요? 바로 이때, ‘계층형 질의(Hierarchical Query)‘라는 강력한 무기가 등장합니다.

이 핸드북은 계층형 질의가 왜 필요하게 되었는지 그 탄생 배경부터 시작하여, 핵심 구조와 사용법, 그리고 실무에서 마주할 수 있는 심화 내용까지 모든 것을 담았습니다. 마치 잘 짜인 탐험 지도를 손에 쥔 것처럼, 이 글을 끝까지 읽고 나면 복잡하게 얽힌 데이터의 계층을 자유자재로 탐험하는 전문가가 되어 있을 것입니다.

1. 계층형 질의는 왜 만들어졌을까?

컴퓨터가 없던 시절을 상상해 봅시다. 거대한 회사의 조직도를 종이에 그린다고 생각해 보세요. 사장님을 맨 위에 그리고, 그 아래에 부사장, 전무, 상무를 선으로 연결합니다. 각 상무 아래에는 팀장들을, 팀장들 아래에는 팀원들을 차례로 그려 나갈 겁니다. 이처럼 세상에는 자연스럽게 ‘부모’와 ‘자식’의 관계를 맺는 데이터가 아주 많습니다.

이런 데이터를 관계형 데이터베이스에 저장하는 것은 간단합니다. 예를 들어, 직원 테이블에 직원 ID와 함께 ‘누가 나의 상사인가’를 나타내는 상사 ID 컬럼을 추가하면 됩니다.

직원_ID	이름	직급	상사_ID
100	김사장	사장	NULL
110	이부장	부장	100
120	박팀장	팀장	110
121	최사원	사원	120
122	정사원	사원	120
130	강팀장	팀장	110

문제는 이 데이터를 ‘조회’할 때 발생합니다. “박팀장 아래에 있는 모든 직원을 찾아주세요” 또는 “최사원의 모든 상사를 보고해주세요”와 같은 질문에 답하려면 어떻게 해야 할까요? 일반적인 SQL로는 한 번에 답을 찾기 어렵습니다. 박팀장의 직속 부하를 찾고, 그 부하의 부하를 또 찾는 과정을 몇 번이나 반복해야 할지 알 수 없기 때문입니다.

이러한 ‘깊이를 알 수 없는 순환적인 관계’ 를 쉽고 우아하게 해결하기 위해 탄생한 것이 바로 계층형 질의입니다. 계층형 질의는 데이터베이스에게 “여기서 시작해서, 이 규칙에 따라 연결된 모든 데이터를 찾아줘”라고 한 번에 명령할 수 있게 해주는 특별한 문법입니다.

2. 계층형 질의의 핵심 구조 파헤치기

계층형 질의는 마치 레고 블록을 조립하는 것과 같습니다. 몇 가지 핵심적인 키워드(블록)를 조합하여 원하는 계층 구조를 만들어낼 수 있습니다. 오라클(Oracle) 데이터베이스에서 가장 널리 사용되는 CONNECT BY 구문을 기준으로 핵심 구조를 살펴보겠습니다.

• START WITH: 계층 탐색을 어디서 시작할지 정하는 출발점입니다. 조직도로 비유하면, “사장님부터 시작해” 또는 “최사원부터 시작해”라고 지정하는 것과 같습니다.

• CONNECT BY: 계층 관계의 연결 규칙을 정의합니다. 부모와 자식 행을 어떻게 연결할지 데이터베이스에 알려주는 가장 핵심적인 부분입니다.

• PRIOR: CONNECT BY 절 안에서 사용되며, 부모 행의 컬럼을 가리키는 특별한 연산자입니다. CONNECT BY PRIOR 자식_컬럼 = 부모_컬럼은 “현재 행의 자식 컬럼 값이 부모 행의 부모 컬럼 값과 같은 것을 찾아 연결하라”는 의미입니다.

• LEVEL: 계층의 깊이를 나타내는 가상의 컬럼(Pseudo-column)입니다. START WITH 절에서 지정한 루트(root) 노드가 1이 되며, 한 단계 아래로 내려갈수록 1씩 증가합니다.

이 네 가지 요소를 조합하면 다음과 같은 기본 구조가 만들어집니다.

SELECT
    LEVEL,
    -- 다른 컬럼들
FROM
    테이블명
START WITH
    시작_조건 -- 예: 상사가 없는 직원 (상사_ID IS NULL)
CONNECT BY
    PRIOR 자식_컬럼 = 부모_컬럼; -- 예: PRIOR 직원_ID = 상사_ID

이 구조를 비유하자면, “사장님(START WITH 상사_ID IS NULL)부터 시작해서, 나의 직원 ID(PRIOR 직원_ID)를 상사 ID(상사_ID)로 가지고 있는 직원들을 계속 찾아 내려가라(CONNECT BY)“는 명령이 됩니다.

3. 실전! 계층형 질의 사용법

이제 위에서 만든 직원 테이블 예시를 가지고 실제로 계층형 질의를 사용해 보겠습니다.

예제 1: Top-Down 방식 (사장부터 모든 직원 조회)

가장 일반적인 방식으로, 최상위 노드(사장)부터 시작하여 아래로 내려가면서 모든 직원을 조회하는 방법입니다.

SELECT
    LEVEL,
    LPAD(' ', (LEVEL-1) * 4) || 이름 AS 직원명, -- 깊이에 따라 들여쓰기
    직급,
    직원_ID,
    상사_ID
FROM
    직원
START WITH
    상사_ID IS NULL -- 김사장이 출발점
CONNECT BY
    PRIOR 직원_ID = 상사_ID;

실행 결과:

LEVEL	직원명	직급	직원_ID	상사_ID
1	김사장	사장	100	NULL
2	이부장	부장	110	100
3	박팀장	팀장	120	110
4	최사원	사원	121	120
4	정사원	사원	122	120
3	강팀장	팀장	130	110

LEVEL과 LPAD 함수를 함께 사용하면 위 결과처럼 계층 구조를 시각적으로 명확하게 표현할 수 있어 매우 유용합니다.

예제 2: Bottom-Up 방식 (특정 직원부터 모든 상사 조회)

반대로, 특정 직원(최사원)을 기준으로 위로 올라가면서 모든 상사를 조회할 수도 있습니다. CONNECT BY 절에서 PRIOR의 위치만 바꿔주면 됩니다.

SELECT
    LEVEL,
    이름,
    직급
FROM
    직원
START WITH
    이름 = '최사원' -- 최사원이 출발점
CONNECT BY
    직원_ID = PRIOR 상사_ID; -- 나의 직원 ID가 부모의 상사 ID와 같은 것을 찾아감

실행 결과:

LEVEL	이름	직급
1	최사원	사원
2	박팀장	팀장
3	이부장	부장
4	김사장	사장

이처럼 PRIOR의 위치를 바꾸는 것만으로 데이터 탐색의 방향(Top-Down vs Bottom-Up)을 자유자재로 제어할 수 있습니다.

4. 계층형 질의를 더 강력하게 만드는 도구들

기본적인 사용법 외에도, 계층형 질의는 분석을 더욱 풍부하게 만들어주는 여러 유용한 기능들을 제공합니다.

• ORDER SIBLINGS BY: 계층 구조는 유지하면서, 같은 부모를 가진 형제 노드(siblings) 사이의 정렬 순서를 지정합니다. 예를 들어, 같은 팀장 아래 있는 사원들을 이름순으로 정렬하고 싶을 때 사용합니다.

  ... (START WITH, CONNECT BY는 동일)
  ORDER SIBLINGS BY 이름 ASC;
  

• SYS_CONNECT_BY_PATH(컬럼, 구분자): 루트 노드부터 현재 노드까지의 전체 경로를 문자열로 반환합니다. 상품 카테고리나 파일 디렉토리 구조를 표현할 때 매우 유용합니다.

  -- 실행 결과 예시: /김사장/이부장/박팀장
  SELECT SYS_CONNECT_BY_PATH(이름, '/') AS 전체경로 FROM ...
  

• CONNECT_BY_ISLEAF: 현재 노드가 계층 구조의 가장 마지막, 즉 자식이 없는 노드(leaf node)인지를 판단합니다. 마지막 노드이면 1, 아니면 0을 반환합니다. 조직도에서 팀원이 누구인지 구분할 때 사용할 수 있습니다.

• CONNECT_BY_ROOT: 현재 노드가 속한 계층의 최상위 루트 노드의 값을 반환합니다. “최사원은 어느 사업부 소속인가?”와 같이, 현재 노드의 최상위 부모 정보를 알고 싶을 때 유용합니다.

• NOCYCLE: 데이터에 무한 루프(A의 상사가 B이고, B의 상사가 A인 경우)가 존재할 때, 오류를 발생시키는 대신 루프를 중단하고 조회를 계속 진행하도록 합니다. 데이터 정합성이 깨진 상황에서 분석을 이어가야 할 때 유용합니다.

  CONNECT BY NOCYCLE PRIOR 직원_ID = 상사_ID;
  

5. 계층형 질의, 언제 어떻게 활용할까?

계층형 질의는 이론으로만 존재하는 문법이 아닙니다. 실무의 다양한 영역에서 복잡한 문제를 해결하는 데 사용됩니다.

• 조직도 및 보고 체계 분석: 특정 관리자 하위의 모든 부서원 조회, 특정 직원의 결재 라인 추적 등 인사 관리 시스템에서 핵심적으로 사용됩니다.

• 제품 BOM(Bill of Materials): 하나의 완제품을 만드는 데 필요한 모든 부품과 그 부품을 만드는 데 필요한 하위 부품들의 관계를 트리 구조로 펼쳐서 자재 소요량을 계산할 때 사용됩니다.

• 온라인 쇼핑몰 카테고리: ‘가전 → 주방가전 → 냉장고’와 같은 다단계 카테고리 구조를 사용자에게 보여주거나, 특정 상위 카테고리에 속한 모든 상품을 조회할 때 활용됩니다.

• 웹사이트 포럼 및 댓글: 원본 글에 달린 댓글과 그 댓글에 달린 대댓글의 관계를 시각적으로 표현하는 데 사용됩니다.

• 지식 관리 시스템: 특정 주제와 연관된 하위 주제, 참고 문헌 등의 관계를 네트워크처럼 분석하고 시각화하는 데 응용할 수 있습니다.

6. 계층형 질의의 대안: 재귀적 CTE

계층형 질의는 매우 강력하지만, 오라클 데이터베이스의 독자적인 문법이라는 한계가 있습니다. 다른 데이터베이스(SQL Server, PostgreSQL, MySQL 등)에서는 표준 SQL 문법인 재귀적 CTE(Recursive Common Table Expression) 를 사용하여 동일한 기능을 구현합니다.

재귀적 CTE는 WITH RECURSIVE 구문을 사용하여, 시작점(Anchor Member)과 반복해서 실행될 부분(Recursive Member)을 정의하는 방식으로 동작합니다. CONNECT BY와 원리는 비슷하지만 문법이 조금 더 복잡합니다.

만약 여러 종류의 데이터베이스를 다루어야 하는 환경이라면 재귀적 CTE를 익혀두는 것이 좋고, 오라클 환경을 주로 사용한다면 직관적이고 강력한 CONNECT BY 구문을 마스터하는 것이 효율적일 수 있습니다.

결론: 관계의 지도를 그리는 기술

계층형 질의는 단순히 데이터를 나열하는 것을 넘어, 데이터 속에 숨겨진 관계와 구조를 발견하고 시각화하는 강력한 도구입니다. 처음에는 PRIOR나 LEVEL 같은 개념이 낯설게 느껴질 수 있지만, 조직도나 가족 관계도를 떠올리며 몇 번만 연습해 보면 그 강력함과 편리함에 매료될 것입니다.

복잡하게 얽힌 데이터 앞에서 더 이상 좌절하지 마세요. 이 핸드북에서 소개한 계층형 질의의 개념과 도구들을 손에 쥐고, 데이터의 관계를 탐험하는 즐거움을 만끽하시길 바랍니다. 여러분은 이제 데이터의 숨겨진 지도를 읽고 그릴 수 있는 유능한 탐험가입니다.

레퍼런스(References)

계층형 질의