[SQL] 계층형 질의(START WITH, CONNECT BY, PRIOR, LEVEL)

📌 계층형 질의(Hierarchical Query)란?

• SQL에서 테이블 내에 계층 구조를 표현할 때 사용하는 기법입니다.
• 계층 구조는 일반적으로 부모-자식 관계로 이루어진 데이터를 다룰 때 유용합니다. 예를 들어, 회사의 조직도에서는 부서가 상위 노드(부모)가 되고, 하위 부서나 직원이 하위 노드(자식)가 됩니다.
• 계층형 질의에서는 각 행이 속한 계층의 깊이를 나타내는 `LEVEL`이라는 가상 컬럼을 사용할 수 있습니다.
  • 루트 노드는 1로 시작하며, 자식 노드로 내려갈수록 값이 증가합니다.
• 주의사항
  • 계층형 질의는 Oracle에서만 지원됩니다. 다른 데이터베이스(PostgreSQL, MySQL 등)에서는 WITH RECURSIVE와 같은 대체 방식을 사용하여 비슷한 결과를 얻을 수 있습니다.

 

📌 계층형 질의 기본 문법

SELECT 컬럼명, LEVEL
FROM 테이블명
START WITH 조건                       -- 1️⃣ 계층 구조의 시작점 정의
CONNECT BY PRIOR 부모컬럼 = 자식컬럼  -- 2️⃣ 부모-자식 관계 설정✨
ORDER SIBLINGS BY 정렬컬럼;           -- 3️⃣ 형제 노드 정렬

• `START WITH`절 : 계층 구조의 루트 노드를 지정하며, 해당 조건에 맞는 행이 시작점이 됩니다.
• `CONNECT BY`절 : `PRIOR` 키워드를 사용하여 부모와 자식 간의 관계를 설정합니다.
• `ORDER SIBLINGS BY`절 : 같은 부모를 가진 형제 노드들을 지정된 컬럼으로 정렬합니다.

 

📌 PRIOR 키워드

• 이 키워드는 현재 행의 부모 행(부모 노드)를 나타냅니다.
• `CONNECT BY`, `SELECT`, `WHERE` 절에서 사용됩니다.

✔️`CONNECT BY PRIOR 자식컬럼 = 부모컬럼` : 부모 → 자식 순방향 전개

-- 예시 : 부서 ID가 1인 인사부의 하위 부서 조회✨
SELECT department_name, LEVEL
FROM departments
START WITH department_id = 1                  -- 부모가 1인 부서 찾기 (인사부)
CONNECT BY PRIOR department_id = parent_id;    -- 부모 → 자식 관계 설정
-- 이 구문은 부모의 department_id와 자식의 parent_id를 비교하여 부모에서 자식으로 탐색합니다.

✔️`CONNECT BY PRIOR 부모컬럼 = 자식컬럼` : 자식 → 부모 역방향 전개

-- 예시 : 부서 ID가 4인 개발부의 상위 부서 조회✨
SELECT department_name, LEVEL
FROM departments
START WITH department_id = 4                   -- 자식이 4인 부서 찾기 (개발부)
CONNECT BY PRIOR parent_id = department_id;    -- 자식 → 부모 관계 설정
-- 이 구문은 현재 행의 parent_id와 이전 단계에서 가져온 부모의 department_id를 비교하여 상위 부서를 찾습니다.