SQL 계층 쿼리 (START WITH, CONNECT BY PRIOR, ORDER SIBLINGS BY)

데이터 테이블이 계층이 있는 데이터를 포함하고 있다면, 계층 쿼리를 사용하여 테이블 내 열을 정렬할 수 있다.

계층 데이터 (Hierarchical data)는 부모-자식 관계로 서로가 연결되어 있는 것을 의미함 (ex. employee id와 manager id의 관계)

EMP table은 아래와 같은 계층 구조를 가지고 있음

 

문법

 

CONNECT BY PRIOR & START WITH 

부모 - 자식 사이의 행을 지정한다. 

PRIOIR은 '자식' 열을 지정하며, 이를 통해 쿼리의 방향을 알 수 있다. 

START WITH 절은 어떤 뿌리(부모)에서 시작할지 명시해준다. 해당 절을 사용하지 않으면, 모든 루트가 반환된다.

SELECT * FROM emp
CONNECT BY PRIOR empno = mgr ;

위의 빨간 묶음은 JONES를 부모(뿌리)로 가진다. 

SELECT * FROM emp
START WITH empno = 7566 --7566 JONES로 시작하여 JONES의 child 컬럼들을 함께 출력
CONNECT BY PRIOR empno = mgr ;

SELECT * FROM emp
START WITH empno = 7566 --7566 JONES로 시작하여 JONES 보다 윗 계층에 있는 컬럼들을 함께 출력
CONNECT BY empno = PRIOR mgr ;

mgr 컬럼이 자식 컬럼이 되고, emp 컬럼이 부모가 된다. 

이 경우 계층의 반대 방향으로 올라가게 됨 (계층 아래에서 계층 위로)

 

LEVEL

계층의 레벨을 보여주는 컬럼, 높은 층위부터 1 부여 

SELECT emp.* , LEVEL FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr ;

 

CONNECT BY ROOT

root row의 값을 돌려준다.

select 절에서 사용된다. 

SELECT emp.* , CONNECT_BY_ROOT ename "root ename" FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr ;

 

START WITH 절을 없앤 경우에는, 각 컬럼의 직속 부모(?)가 명시된다. 

SELECT emp.* , CONNECT_BY_ROOT ename "root ename" FROM emp
CONNECT BY PRIOR empno = mgr ;

 

SYS CONNECT BY PATH

각 데이터가 어느 계층으로 연결/종속 되어 있는지 보여주는 함수

SELECT 절에서 사용된다. 

SELECT emp.* , LEVEL, 
SYS_CONNECT_BY_PATH (ename, '-') as "path" FROM emp
START WITH empno = 7566
CONNECT BY PRIOR empno = mgr ;

 

ORDER BY SIBLINGS

기존 계층을 유지하되, 계층 안에서 ORDER BY 기능을 사용하고 싶은 경우

SELECT emp.* , LEVEL FROM emp
START WITH empno = 7698  --BLAKE가 ROOT
CONNECT BY  PRIOR empno = mgr ;

LEVEL 2 계층에서 알파벳 순서대로 정렬을 하고 싶을 때 ORDER SIBLINGS BY 함수를 사용할 수 있다. 

SELECT emp.* , LEVEL FROM emp
START WITH empno = 7698  --BLAKE가 ROOT
CONNECT BY  PRIOR empno = mgr 
ORDER SIBLINGS BY ename ;

 

LPAD

특정 문자를 왼쪽(L)에 끼워넣어 주는 함수

이를 바탕으로 계층 순위를 보다 쉽게 파악할 수 있다.

아래 쿼리에서는 ename의 길이에 level을 곱한 만큼 왼쪽에 > 기호를 끼워넣는다. 

SELECT emp.* , LEVEL, LPAD(ename, length(ename)*level,'>')  as ename_pad FROM emp
START WITH empno = 7839
CONNECT BY  PRIOR empno = mgr 
ORDER SIBLINGS BY ename ;

 

결과 필터링하기

empnp = 7698 과 연관된 모든 연결을 빼고 출력하

SELECT emp.* , LEVEL FROM emp
START WITH empno = 7839
CONNECT BY  PRIOR empno = mgr AND empno <> 7698 ;