[SQL] Oracle 계층형 질의 (START WITH, CONNECT BY, ORDER SIBLINGS BY, SYS_CONNECT_BY_PATH)

🚀 계층형 질의 (START WITH, CONNECT BY)

계층형 질의를 이용해 EMP 테이블에서 사원간의 서열을 출력한다.

START WITH와 CONNECT BY 절을 이용하면 LEVEL을 출력할 수 있다.

• START WITH 절은 루트노드를 지정
• CONNECT BY 절은 부모노드와 자식노드 간의 관계를 지정

우선 EMP 테이블의 전체 데이터를 보면 아래와 같다.

위와 같은 데이터가 있을때 EMPNO와 MGR를 보면 상하관계를 확인할 수 있다. MGR가 상위이다. 상하관계를 간단히 나타내면 아래 그림과 같다.

하나 예를 들자면 KING은 루트이고 BLAKE, CLARK, JONES의 경우 KING의 하위 관계다. JONES를 보면 SCOTT과 FORD가 하위 관계이며 FORD를 보면 SMITH가 하위 관계인것을 확인할 수 있다. (빨간색 줄은 잘못 표현되었다. SMITH의 경우 FORD를 가리켜야한다)

이처럼 상하관계, 계층형을 간단히 표현한 것이 계층형 질의이다.

SELECT rpad(' ', (level-1)*3) || ename as employee
     , level
     , sal
     , job
FROM EMP
START WITH ename='KING'
CONNECT BY PRIOR empno = mgr;

위 쿼리의 결과는 위에서 봤던 계층형 그림이다. 쿼리를 간단히 설명하자면

• SATRT WITH ename = 'KING'  : KING을 루트 노드로 설정한다.
• CONNECT BY PRIOR empno = mgr : empno(부모노드)와 mgr(자식노드) 관계를 기준으로 상하관계를 만든다.
• LEVEL : 상하관계를 기준으로 레벨을 자동으로 생성해준다.
• RPAD :  LEVEL을 기준으로 왼쪽으로부터 공백을 생성하여 계층형을 가시화한다.

 

🚀 정렬 (ORDER SIBLINGS BY, ORDER BY)

ORDER SIBLINGS BY를 이용하면 루트노드와 자식노드 간의 서열을 유지하면서 정렬이 이루어진다.

SELECT rpad(' ', (level-1)*3) || ename as employee
     , level
     , sal
     , job
FROM EMP
START WITH ename='JONES'
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY sal ASC;
 
SELECT rpad(' ', (level-1)*3) || ename as employee
     , level
     , sal
     , job
FROM EMP
START WITH ename='JONES'
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY sal DESC;    

반면, ORDER BY 절을 그대로 사용하게되면 루트노드와 자식노드간의 서열을 무시하고 정렬이 이루어진다.

SELECT rpad(' ', (level-1)*3) || ename as employee
     , level
     , sal
     , job
FROM EMP
START WITH ename='JONES'
CONNECT BY PRIOR empno = mgr
ORDER BY sal ASC;
 
SELECT rpad(' ', (level-1)*3) || ename as employee
     , level
     , sal
     , job
FROM EMP
START WITH ename='JONES'
CONNECT BY PRIOR empno = mgr
ORDER BY sal DESC;

🚀 경로 표시 ( SYS_CONNECT_BY_PATH )

SYS_CONNECT_BY_PATH를 이용하면 CONNECT BY를 통해 가져온 값을 마치 루트노드로 부터 경로를 표시하는 것과 같이 표현한다.

* LTRIM을 이용해 앞에 출력되는 '/'를 제거하고 출력할 수 있다.

SELECT ename
     , LTRIM(SYS_CONNECT_BY_PATH(ename, '/'), '/') as PATH
FROM EMP
START WITH ename='KING'
CONNECT BY PRIOR empno = mgr;

이전 예제와 함께 활용하면 노드간의 계층도를 시각적으로 표현할 수도 있다.

SELECT ename
     , RPAD(' ', (level-1)*3) || LTRIM(SYS_CONNECT_BY_PATH(ename, '/'), '/') as PATH
FROM EMP
START WITH ename='KING'
CONNECT BY PRIOR empno = mgr;

 

🚀 주의!

START WITH 

루트노드를 지정해준다. 같은 쿼리라도 루트노드를 지정해주는 것에 따라 출력결과는 달라진다.

KING이 아닌 JONES를 루트노드로 하면 JONES 하위 노드들만 출력된다.

SELECT rpad(' ', (level-1)*3) || ename as employee
     , level
     , sal
     , job
FROM EMP
START WITH ename='JONES'
CONNECT BY PRIOR empno = mgr;