계층형 질의와 셀프 조인

개요

계층형 데이터가 테이블에 존재하는 경우 데이터를 조회하기 위해 계층형 질의를 사용한다.
계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다.

셀프 조인

셀프 조인이란 동일 테이블 사이의 조인을 말한다.
칼럼 명들이 모두 동일하므로 식별을 위해 꼭 Alias을 사용해야 한다.

셀프 조인에 대한 사용법

SELECT A.칼럼명1, B.칼럼명1, A.칼럼명2, B.칼럼명2, ...
FROM 테이블1 A, 테이블 2 B
WHERE A.칼럼명1 = B.칼럼명1;

JONES의 자식 노드를 조회하는 쿼리.

SELECT B.EMPNO, B.ENAME, B.MGR
FROM EMP A, EMP B
WHERE A.ENAME = 'JONES'
  AND B.MGR = A.EMPNO;

JONES의 자식의 자식 노드를 조회하는 쿼리.

SELECT C.EMPNO, C.ENAME, C.MGR
FROM EMP A, EMP B, EMP C
WHERE A.ENAME = 'JONES'
  AND B.MGR = A.EMPNO
  AND C.MGR = B.EMPNO;

SMITH의 부모 노드를 조회하는 쿼리.

SELECT B.EMPNO, B.ENAME, B.MGR
FROM EMP A, EMP B
WHERE A.ENAME = 'SMITH'
  AND B.EMPNO = A.MGR;

계층형 질의

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

START WITH 절
계층 구조 전재의 시작 위치를 지정하는 구문.
즉 루트 데이터를 지정한다(액세스).

CONNECT BY 절
다음에 전개될 자식 데이터를 지정하는 구문.
자식 데이터는 CONNECT BY 절에 주어진 조건을 만족해야 한다.(조인)

PRIOR
CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정한다.
(FK) = PRIOR (PK) 형태면 부모에서 자식으로 순방향이며
(PK) = PRIOR (FK) 형태면 자식에서 부모으로 역방향이다.

NOCYCLE
데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면, 사이클이 발생했다고 한다.
사이클이 발생한 데이터는 런타임 오류가 발생한다. NOCYCLE을 사이클 이후의 데이터를 전개하지 않아 오류를 발생시키지 않는다.

ORDER SIBLINGS BY
현재 노드(동일 LEVEL) 사이에서 정렬을 수행한다.

WHERE
모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다(필터링).

Oracle은 계층형 질의를 사용할 때 다음과 같은 가상 칼럼을 제공한다.

LEVEL : 루트 데이터이면 1, 그 하위 데이터라면 2다.
리프(Leaf) 데이터까지 1씩 증가
CONNECT_BY_ISLEAF : 전개 과정에서 해당 데이터가 리프 데이터이면 1, 아니면 0이다.
CONNECT_BY_ISCYCLE : 전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로서 존재하면 1, 그렇지 않으면 0이다.

SELECT LEVEL AS LV
	 , LPAD(' ', (LEVEL - 1) * 2) || EMPNO AS EMPNO
     , MGR
	 , CONNECT_BY_ISLEAF AS ISLEAF
FROM EMP
START WITH MGR IS NULL
CONNECT BY MGR = PRIOR EMPNO;

관리자 -> 사원 방향으로 전개하는 순방향 전개이다.

SELECT LEVEL AS LV
	 , LPAD(' ', (LEVEL - 1) * 2) || EMPNO AS EMPNO
     , MGR
	 , CONNECT_BY_ISLEAF AS ISLEAF
FROM EMP
START WITH MGR = '7839'
CONNECT BY EMPNO = PRIOR MGR;

역방향 전개이기에 하위 데이터에서 상위 데이터로 전개 된다.

계층형 질의에서 사용되는 함수

SYS_CONNECT_BY_PATH : 루트 데이터부터 현재 전개할 데이터까지의 경로를 표시한다.
사용법 : SYS_CONNECT_BY_PATH(칼럼, 경로분리자)
CONNECT_BY_ROOT : 현재 전개할 데이터의 루트 데이터를 표시한다. 단향 연산자이다.
사용법 : CONNECT_BY_ROOT 칼럼

SELECT CONNECT_BY_ROOT(EMPNO) AS ROOT_EMPNO
	 , SYS_CONNECT_BY_PATH(EMPNO, ',') AS PATH
     , EMPNO
     , MGR
FROM EMP
START WITH MGR IS NULL
CONNECT BY MGR = PRIOR EMPNO;