2.2.3 제3절 계층형 질의와 셀프 조인

1. 계층형 질의

• 계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다.

 

가. Oracle 계층형 질의

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절에 주어진 조건을 만족해야 한다.(조인) ● 다음에 나오는 AND 절은 START WITH 절의 수행 한 후에 Filter 적용을 한다.
PRIOR	●  CONNECT BY 절에 사용되며, 현재 읽은 컬럼을 지정한다. ●  "PRIOR 자식 = 부모" 형태를 사용하면 계층구조에서 자식 데이터에서 부모 데이터(자식 -> 부모)방향으로 전개하는 순방향 전개를 한다. ●  "PRIOR 부모 = 자식" 형태를 사용하면 반대로 부모 데이터에서 자식 데이터(부모 -> 자식) 방향으로 전개하는 역방향 전개를 한다.
NOCYCLE	● 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중에 다시 나타난다면 이것을 가리켜 사이클(Cycle)이 형성되었다라고 말한다. ● 사이클이 발생한 데이터는 런타임 오류가 발생한다. ● 그렇지만 NOCYCLE를 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.
ORDER SIBLINGS BY	형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다.
WHERE	모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다.(필터링)

 

 

 

• Oracle에서 계층형 질의 사용시 제공되는 가상 컬럼(Pseudo Column)

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

 

 

• Oracle에서 계층형 질의 사용시 제공되는 함수

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

 

 

WITH TEST AS (
    SELECT '001' A, '홍길동' B, '2012-01-01' C, NULL D FROM DUAL UNION ALL
    SELECT '002' A, '강감찬' B, '2012-01-01' C, '001' D FROM DUAL UNION ALL
    SELECT '003' A, '이순신' B, '2013-01-01' C, '001' D FROM DUAL UNION ALL
    SELECT '004' A, '이민정' B, '2013-01-01' C, '001' D FROM DUAL UNION ALL
    SELECT '005' A, '이병헌' B, '2013-01-01' C, NULL D FROM DUAL UNION ALL
    SELECT '006' A, '안성기' B, '2014-01-01' C, '005' D FROM DUAL UNION ALL
    SELECT '007' A, '이수근' B, '2014-01-01' C, '005' D FROM DUAL UNION ALL
    SELECT '008' A, '김병만' B, '2014-01-01' C, '005' D FROM DUAL
)
SELECT A, B, C, D
FROM TEST
START WITH D IS NULL
CONNECT BY PRIOR A = D 
AND C BETWEEN '2013-01-01' AND '2013-12-31'
ORDER SIBLINGS BY A;


001	홍길동	2012-01-01	NULL
003	이순신	2013-01-01	001
004	이민정	2013-01-01	001
005	이병헌	2013-01-01	NULL

-- 만약에 WHERE C BETWEEN '2013-01-01' AND '2013-12-31' 추가 시 
-- 001	홍길동	2012-01-01	NULL 제외 됨

 

 

나. SQL-Server 계층형 질의

• CTE(Common Table Expression) 즉,
• WITH문으로 재귀적 호출 사용으로 Oracle에서의 Connect By와 동일한 효과를 나타냄

---- 아래와 같이 EMPLOYEES 테이블이 존재 ----
************************************************************************** 
EmployeeID  LastName  FirstName  ReportsTo 
---------   --------   -------  -------- 
         1   Davolio     Nancy       2 
         2   Fulle       Andrew     NULL 
         3   Leverling   Janet       2 
         4   Peacock    Margaret     2 
         5   Buchanan   Steven       2 
         6   Suyama     Michael      5 
         7   King        Robert      5 
         8   Callahan     Laura      2 
         9   Dodsworth   Anne        5

 

 

WITH EMPLOYEES_ANCHOR AS ( 
    SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO, 0 AS LEVEL
    FROM EMPLOYEES 
    WHERE REPORTSTO IS NULL /* 재귀 호출의 시작점 */ 
    UNION ALL 
    SELECT R.EMPLOYEEID, R.LASTNAME, R.FIRSTNAME, 
    R.REPORTSTO, A.LEVEL + 1 
    FROM EMPLOYEES_ANCHOR A, EMPLOYEES R 
    WHERE A.EMPLOYEEID = R.REPORTSTO 
) 
SELECT LEVEL, EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO 
FROM EMPLOYEES_ANCHOR 
GO

• WITH문내의 UNION ALL의 위쪽 최상위 부모를 구하는 쿼리를 앵커 멤버(Anchor Member)라고 하고 아래에 있는 쿼리를 재귀 멤버(Recursive Member)라고 한다.
• 처리과정
  1. CTE 식을 앵커 멤버와 재귀 멤버로 분할한다.
  2. 앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합(T0)을 만든다.
  3. Ti는 입력으로 사용하고 Ti+1은 출력으로 사용하여 재귀 멤버를 실행한다.
  4. 빈 집합이 반환될 때까지 3단계를 반복한다.
  5. 결과 집합을 반환한다,
  6. 결과는 T0에서 Tn까지의 UNION ALL이다.

 

2. 셀프 조인

• 셀프 조인이란 동일 테이블 사이의 조인을 말한다.
• 동일테이블을 두번 조인하기에 Alias를 사용해야 한다.

SELECT E1.사원, E1.관리자, E2.관리자 차상위_관리자 
FROM 사원 E1, 사원 E2 
WHERE E1.관리자 = E2.사원 
ORDER BY E1.사원;