[SQL] 계층형 쿼리 ( CONNECT BY )

계층형 쿼리란?

테이블에 계층형 구조 즉, 수직적 구조가 존재할 때 이를 조회하기 위한 쿼리이다.

 

위의 테이블을 보면 EMPNO가 직원번호이고 MGR이 해당 직원의 관리자의 직원번호이다.

1번 로우의 EMPNO가 '7839' 인 KINGdms 회장이라 관리자란이 NULL값이다. 

2번 로우의 EMPNO가 '7698'인 BLAKE는 상사직번이 7829(KING)으로 회장님이 직접 관리하는 직원이다. 

 

이런식으로 한 테이블안에 수직적 구조가 존재할 때 이를 계층형 구조라고 한다.

 

START WITH

계층의 루트로 사용될 행을 지정한다.

서브 쿼리를 사용할 수 있다.

어떤 레코드를 최상위 레코드로 정할지 결정한다. 

 

관리자가 없는(최상위 관리자)부터 시작할 것이기 때문에 MGR이 NULL인 ROW를 최초 시작점으로 한다.

SELECT * 
 FROM TB_EMP
START WITH MGR IS NULL;

 

 

CONNECT BY

연결고리를 만든다.

PRIOR 연산자로 계층구조를 표현할 수 있다.

서브쿼리를 사용할 수 없다.

최초행 이 후 다음행을 어떤 조건으로 가져올 것인지 정한다.

 

앞서 START WITH를 통해 최상위 행을 가져왔다. 이제 최상위 행을 관리자로 갖는 다음 계층의 데이터를 가져와야한다.

최상위 행인 'KING' 데이터를 기준으로 생각하자. MGR값이 'KING'  데이터의 EMPNO와 일치하는 행을 가져오면 된다. 

CONNECT BY PRIOR EMPNO = MGR

 

만약 PRIOR을 반대로 선언하면 

CONNECT BY PRIOR MGR = EMPNO

 

위와 같이 선언하면 'KING' 데이터의 MGR NULL 값이고,  NULL값을 EMPNO로 갖는 데이터는 없기 때문에 출력 결과가 나오지 않게 된다.

 

***CONNECT BY NOCYCLE PRIOR: NOCYCLE파라미터를 이용하여 무한 루프 방지

 

LEVEL

CONNECT BY 절을 쓰면 오라클이 제공해주는 컬럼 

수행 결과의 DEPTH를 표현해주는 의사 컬럼

LEVEL 1이 제일 상위값이다. 

 

SELECT LPAD(' ', (LEVEL -1) * 5 ) || ENAME 
 FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR

 

레벨을 따라 들여쓰기한 쿼리로 

1레벨이면 좌측에 0개의 공백

2레벨이면 좌측에 5개의 공백

3레벨이면 좌측에 10갸의 공백이 붙어 ENAME을 출력해준다.

 

 

위와 같은 트리의 형태로 

최하단까지 찾아가면 상위로 올라가 하위단을 찾아간다.

ADAMS를 찾은 후 SCOTT으로 이동하여 하위계층이 있는지 찾아본다. SCOTT에게 더 이상 하위계층이 없으면, 한 단계 상위 단계인 JONES로 이동하여 하위단이 있는지 찾아본다. FORD가 발견되었고, 하위단 SMITH까지 찾아간다. 위의 방식으로 모든 대상을 찾을때까지 계속한다.

 

SIBLINGS

계층별로 정렬을 하기 위해서는 SIBLINGS 라는 명령어를 ORDER BY와 같이 사용해야 한다.

 

SELECT LPAD(' ', (LEVEL -1) * 5 ) || ENAME 
 FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ENAME;