오라클 CONNECT BY 사용법 - 복잡한 부모-자식 데이터 구조를 한 줄의 쿼리로 해결하기

데이터베이스에 저장된 데이터가 단순한 목록이 아니라 '가계도'나 '회사 조직도'처럼 계층 구조라면 어떻게 조회해야 할까요? 수많은 Join을 쓰는 대신, 오라클에서는 CONNECT BY라는 강력한 무기를 제공합니다. 오늘은 복잡한 트리 구조 데이터를 우아하게 추출하는 핵심 문법을 정리해 드립니다.

---

1. 계층형 질의(Hierarchical Query)란?

계층형 질의는 부모 노드와 자식 노드 간의 관계를 바탕으로 데이터를 수직적으로 전개하는 쿼리입니다. 트리(Tree) 구조의 데이터를 한 번의 쿼리로 논리적으로 연결하여 추출할 수 있게 해주는 강력한 도구입니다.

2. 기본 문법 구조

오라클 계층형 쿼리의 기본 구조는 다음과 같습니다.

SQL

 

SELECT 
    LEVEL, 
    [컬럼명], 
    SYS_CONNECT_BY_PATH([컬럼], '/') AS 경로
FROM [테이블명]
WHERE [필터조건] -- 계층 전개 완료 후 결과 집합에서 적용됨
START WITH [루트노드_조건] -- 전개의 시작점
CONNECT BY [NOCYCLE] PRIOR [자식컬럼] = [부모컬럼] -- 연결 고리 정의
ORDER SIBLINGS BY [정렬기준]; -- 계층 구조를 유지하며 정렬

• LEVEL: 현재 계층의 깊이를 나타내는 가상 컬럼입니다. (Root는 1)
• START WITH: 전개를 시작할 최상위 행을 지정합니다. 보통 부모가 없는(MGR IS NULL) 행이 기준이 됩니다.
• CONNECT BY: 부모와 자식 간의 관계를 정의합니다.
• PRIOR: 키워드가 붙은 쪽이 '직전 단계(부모)'가 됩니다. 이 위치에 따라 전개 방향이 결정됩니다.

---

3. 실무 필수! 계층형 질의의 4대 핵심 포인트

① PRIOR의 위치: 방향을 결정하는 나침반

가장 헷갈리는 부분이지만, 이것만 기억하면 됩니다. PRIOR가 붙은 쪽이 '기준(부모)'입니다.

• 순방향 전개 (Top-Down): CONNECT BY PRIOR 사번 = 관리자번호
  • 내 사번이 누군가의 관리자번호가 되는 구조입니다. 즉, 위에서 아래로 내려갑니다.
• 역방향 전개 (Bottom-Up): CONNECT BY 사번 = PRIOR 관리자번호
  • 내 관리자번호가 누군가의 사번이 되는 구조입니다. 즉, 아래에서 위로 거슬러 올라갑니다.

② NOCYCLE 옵션: 무한 루프 방지

데이터 설계 실수로 'A → B → A' 형태로 서로를 참조하는 순환 구조가 발생할 수 있습니다. 이때 쿼리를 실행하면 ORA-01436 에러가 발생하며 서버에 부하를 줄 수 있습니다.

• 해결책: CONNECT BY NOCYCLE PRIOR ... 구문을 사용하면 이미 방문한 노드는 다시 방문하지 않아 무한 루프를 방지합니다.

③ ORDER SIBLINGS BY: 계층을 지키는 정렬

일반 ORDER BY를 사용하면 계층 구조가 무너지고 데이터가 뒤섞입니다. 같은 부모를 둔 자식들(형제 노드)끼리만 정렬하여 계층 형태를 유지하려면 반드시 ORDER SIBLINGS BY를 사용해야 합니다.

④ 계층형 질의 전용 가상 컬럼 및 함수

• CONNECT_BY_ISLEAF: 최하위 자식(Leaf) 노드면 1, 자식이 더 있으면 0을 반환합니다.
• SYS_CONNECT_BY_PATH(컬럼, 구분자): 루트부터 현재 노드까지의 전체 경로를 보여줍니다. (예: /경영진/기획실/운영팀)
• CONNECT_BY_ISROOT: 현재 노드가 최상위 루트면 1, 아니면 0을 반환합니다.

---

4. 실전 예제: 조직도 시각화하기

실제 사원(EMP) 테이블을 활용해 들여쓰기가 적용된 조직도를 출력하는 예시입니다.

SQL

SELECT 
    LEVEL AS LV,
    LPAD(' ', 2 * (LEVEL - 1)) || ENAME AS "조직도",
    EMPNO,
    MGR,
    CONNECT_BY_ISLEAF AS "말단여부",
    SYS_CONNECT_BY_PATH(ENAME, ' > ') AS "전체경로"
FROM EMP
START WITH MGR IS NULL -- 사장님부터 시작
CONNECT BY PRIOR EMPNO = MGR -- 순방향 전개
ORDER SIBLINGS BY ENAME; -- 형제 노드 간 이름순 정렬

[결과 해석]

1. LEVEL과 LPAD를 결합하여 시각적인 트리 구조를 만듭니다.
2. CONNECT_BY_ISLEAF로 해당 직원이 하급자가 없는 말단인지 확인합니다.
3. SYS_CONNECT_BY_PATH를 통해 해당 직원의 보고 라인을 한눈에 파악합니다.

---

🏁 마무리하며: 계층형 쿼리 활용 전략

지금까지 오라클의 CONNECT BY를 활용한 계층형 질의에 대해 심층적으로 알아보았습니다. 복잡해 보이는 문법이지만, 핵심은 결국 부모와 자식의 연결고리를 어떻게 정의하느냐에 있습니다. 실무 적용을 위해 다음 내용을 마지막으로 체크해 보세요.

• 방향 설정: 상위 조직을 찾는지(역방향), 하위 카테고리를 펼치는지(순방향)에 따라 PRIOR 위치를 정하세요.
• 데이터 무결성: 순환 참조 발생 가능성이 있다면 반드시 NOCYCLE 옵션을 검토해야 합니다.
• 성능 최적화: 계층형 쿼리는 부하가 클 수 있으므로, 연결 컬럼에는 가급적 인덱스(Index)를 생성하는 것이 좋습니다.
• 시각화: LEVEL을 활용해 사용자가 한눈에 구조를 파악할 수 있는 '트리형 뷰'를 제공해 보세요.

데이터의 깊이(Level)를 자유자재로 다루는 능력은 백엔드 개발자의 전문성을 증명하는 중요한 척도가 됩니다. 오늘 정리한 내용이 여러분의 프로젝트에서 복잡한 로직을 단순한 쿼리 한 줄로 해결하는 열쇠가 되기를 바랍니다!

---

데이터의 계층(Hierarchy)을 파악하는 것은 투자에서 상위 지표와 하위 지표의 인과관계를 분석하는 것과 닮아 있습니다. 뿌리(Root)가 되는 지표를 알면 전체 흐름이 보이듯, 쿼리에서도 START WITH를 잘 잡는 것이 중요합니다. 효율적인 쿼리 설계로 여러분의 시스템 성능과 자산을 모두 우상향시키세요!

아래 글은 postgresql에서의 계층형 구조에 대한 글입니다.

https://run-code-rich.tistory.com/entry/postgresql-with-recursive-hierarchy

[PostgreSQL] 계층형 쿼리 정복: WITH RECURSIVE로 복잡한 조직도 한 번에 불러오기