SQL) 계층형 질의와 셀프 조인

[ 계층형 질의 ]

회사의 조직도, 국가의 행정구역, 대학교의 학부 등

현실 세계에는 계층형으로 이루어진 다양한 구조가 있다.

 

이러한 계층형 구조는 데이터 모델 설계를 통해

물리적인 테이블로 구축이 가능하다.

 

계층형 구조의 테이블 예시

 A사원은 관리자가 없지만,

B사원은 A가 관리자며

C사원은 B가 관리자인 상황이다.

사원	관리자
A	NULL
B	A (B담당 관리자)
C	B (C담당 관리자)

 

위에서 아래로 내려가는 하향식 방식을 기준으로

맨 상위에 있는 'A' 사원을 루트 데이터,

맨 아래에 있는 'C' 사원을 리프 데이터 라고 한다.

 

이러한 계층형 구조의 데이터를

SQL문으로 표현하는 것을

계층형 질의 Hierarchical Query

혹은 계층형 SQL 이라고 한다.

 

또한 동일한 테이블끼리 조인 연산을

수행하는 조인 방식인 셀프 조인으로도

계층형 구조로 이루어진 데이터를 조회할 수 있다.

 

※ 계층형 데이터란?

동일 테이블에 계층적으로 상위와

하위 데이터가 포함된 데이터를 말한다.

---

[ 오라클 계층형 SQL ]

오라클 DBMS는 계층형 SQL문 기능을 제공한다.

기존의 SELECT문을 바탕으로 결합한 구조이다.

- START WITH

- CONNECT BY

- ORDER SIBLINGS BY문

 

또한 계층형 SQL문에서는

오라클 내부에서 제공하는

Pseudo 칼럼(가상칼럼)이 있다.

 

오라클 계층형 SQL문 구조

구분	설명
SELECT 칼럼	조회하고자 하는 칼럼 지정
FROM 테이블	대상 테이블을 지정
WHERE 조건	모든 전개를 수행한 후 지정된 조건을 만족하는 데이터만 추출 계층 전개 후 출력되는 집합에 대한 필터 처리
START WITH 조건	전개의 시작 위치를 지정하는 구문 즉, 루트 데이터를 지정한다.
CONNECT BY [NOCYCLE] [PRIOR]  A AND B	다음에 전개될 자식 데이터를 지정하는 구문 PRIOR PK(자식) = FK(부모)형태를 사용하면 부모 데이터에서 자식 데이터 방향으로 전개하는 순방향 전개를 함(부모→자식) PRIOR FK(부모) = PK(자식)형태를 사용하면 반대로 자식 데이터에서 부모 데이터 방향으로 전개하는 역방향 전개를 함 (자식→ 부모) NOCYCLE을 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.
ORDER SIBLINGS BY 칼럼	형제 노드(동일 LEVEL) 사이에서 정렬을 수행

 

오라클 계층형 SQL 가상 칼럼(Pseudo)

구분	설명
LEVEL	1부터 시작하며, 루트 데이터면 1 그 하위 데이터면 2 하위 데이터가 있을 때마다 1씩 증가
CONNECT_BY_ISLEAF	전개과정에서 해당 데이터가 리프 데이터면 1 그렇지 않으면 0
CONNECT_BYISCYCLE	전개과정에서 자식을 갖는데, 해당 데이터가 조상으로서 존재하면 1 그렇지 않으면 0

---

[ 예시 데이터 ]

-- 예시 테이블(가정)
-- DEPT(dept_id PK, dept_name, parent_dept_id FK)

SELECT
    LEVEL AS lvl,                               -- 계층 깊이
    LPAD(' ', (LEVEL-1) * 2) || dept_name AS dept_tree, -- 보기 좋게 들여쓰기
    dept_id,
    parent_dept_id
FROM DEPT
WHERE dept_name LIKE '%팀%'                     -- (계층 전개 후) 출력 집합 필터
START WITH parent_dept_id IS NULL               -- 루트(최상위 부서)부터 시작
CONNECT BY NOCYCLE PRIOR dept_id = parent_dept_id  -- 순방향(부모->자식) 전개
ORDER SIBLINGS BY dept_name;                    -- 같은 LEVEL(형제)끼리 이름순 정렬

 

결과 (예시 데이터 기준)

(lvl, dept_tree, dept_id, parent_dept_id)

• 2 ' 개발1팀' 20 10
• 3 ' 백엔드팀' 21 20
• 2 ' 디자인팀' 30 10