[SQLD] SQL 활용 - 계층형 질의

계층형 질의 (Hierarchical Query)

계층형 데이터를 조회할 때 사용하는 쿼리

💡 계층형 데이터
동일한 테이블에 계층적으로 상위와 하위 데이터가 포함되어진 데이터

e.g.
사원 테이블에는 사원들의 데이터가 있지만
분명 사원마다 직급이 있거나 관리자(직속상관)이 있기 마련이다.

 

계층형 DB (트리구조, 1:N 구조, 부모자식 구조)

 

 

오라클 계층형 질의

SELECT		...
FROM		테이블
WHERE		condition AND condition...
START WITH	condition
CONNECT BY [NOCYCLE] condition AND condition ...
[ORDER SIBLINGS BY column, column, ...]

• START WITH절은 계층 구조 전개의 시작 위치를 저장하는 구문이다. 루트를 지정한다. (액세스)
• CONNECT BY절은 다음에 절개될 자식 데이터를 지정하는 구문이다. 자식 데이터는 CONNECT BY절에 주어진 조건을 만족해야 한다. (조인)
• PRIOR: CONNECT BY절에 사용되며, 현재 읽은 컬럼을 지정한다. PRIOR 자식 = 부모 형태를 사용하면 반대로 자식 데이터에서 부모 데이터(자식->부모) 방향으로 전개하는 역방향 전개를 한다.
• NOCYCLE: 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 가르켜 사이클(cycle)이 형성되었다라고 말한다. 사이클이 발생한 데이터는 런타임 오류가 발생한다. 그렇지만 NOCYCLE를 추가하면 사이클이 발생한 이후 데이터는 전개하지 않는다.
• ORDER SIBLINGS BY: 형제 노드(동일 Level) 사이에서 정렬을 수행한다.
• WHERE: 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다. (필터링)

오라클은 계층형 질의를 사용할 때 다음과 같은 가상 컬럼(Pseudo Column)을 제공한다.

- LEVEL: 루트 데이터면 1, 그 하위 데이터면 2, 리프(leaf)데이터까지 1씩 증가한다.
- CONNECT_BY_ISLEAF: 전개 과정에서 해당 데이터가 리프데이터면 1, 그렇지 않으면 0이다.
- CONNECT_BY_ISCYCLE: 전개 과정에서 자식을 갖는데, 해당 데이터가 조상을 서 존재하면 1, 그렇지 않으면 0이다. 여기서 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터를 말한다. CYCLE 옵션을 사용했을 때만 사용 가능하다.

 

 

e.g

 

SAMPLE DATA

사원	관리자
A
B	A
C	A
D	C
E	C

 

01.

관리자 → 사원 방향의 전개이기 때문에 순방향 전개의 예제.

A → B, C → D, E

SELECT		LEVEL, CMPNO, MGR, CONNECT_BY_ISLEAF 리프
FROM		EMP
START WITH	MGG IS NULL
CONNECT BY PRIOR EMPNO = MGR;

 

RESULT

 

02. 

사원 D부터 상위 관리자를 찾는 역방향 전개의 예제

D → C → A

SELECT		LEVEL, EMPNO, MGR, CONNECT_BY_ISLEAF 리프
FROM		EMP
START WITH	EMPNO = '7876'
CONNECT BY PRIOR MGR = EMPNO;

 

RESULT

루트 및 레벨은 전개되는 방향에 따라 반대가 된다. (위에서 아래로 흐르면 순방향. 거꾸로 올라가면 역방향)

 

 

📌 참고자료

https://mjn5027.tistory.com/55

[ Database ] SQLD DB 계층형 질의