계층형 질의와 셀프 조인

1. 계층형 질의 (Hierarchical Query)

• 계층적 데이터를 조회하기 위해 사용

• DBMS 벤더와 버전에 따라 모두 다른 방법으로 지원 (표준 방법이 없음)

오라클

형식

SELECT …

FROM TABLE

WEHRE CONDITION AND CONDTION…

START WITH condition

CONNECT BY [NOCYCLE] condition AND condition…

[ ORDER SIBLINGS BY column, column …]

설명

• START WITH : 전개의 시작 위치를 지정하는 구문

• CONNECT BY : 자식을 지정하는 구문

• PRIOR 자식 = 부모 : 순방향 전개, 반대로는 역방향 전개

• NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 다시 나타난다면
  이것을 사이클이라하며 NOCYCLE를 추가하면 발생한 이우 더 이상
  전개하지 않음

• ORDER SIBLINGS BY : 형제 노드 사이의 정렬 수행

• WHERE : 모든 전개 이후 지정 조건을 만족하는 데이터만 추출

가상 컬럼

• LEVEL : 깊이 수준

• CONNECT_BY_ISLEAF : 나뭇잎, 마지막

• CONNECT_BY_ISCYCLE : 자식의 조상 중 한명이라면 1 아니면 0 (사이클유무)

사용 함수

• SYS_CONNECT_BY_PATH (칼럼, 경로 분리자)
  탐색된 경로를 표시
  
  

• CONNECT_BY_ROOT
  루트 데이터 표시

SQL SERVER

CTE를 통해 재귀 호출함으로써 도출할 수 있음 (복잡)

재귀 접근 쿼리 예시

WITH EMPLOYEES_ANCHORS AS (

SELECT EMPLOYEEDID, 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. REPOPTSTO

)

SELECT LEVE, EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO

FROM EMPLOYEES_ACHOR

GO

구조적으로 보고 싶을 때

// 경로를 표시함으로써 표현가능

WITH EMPLOYEES_ANCHORS AS (

SELECT EMPLOYEEDID, LASTNAME, FIRSTNAME, REPORTSTO, 0 AS

LEVEL, CONVERT(VARCHAR(1000), EMPLOYEEID) AS SORT

FROM EMPLOYEES

WHERE REPORTSTO IS NULL

UNION ALL
/* 이 아래부터 재귀 형식으로 접근되며, 아래 쿼리가 공집합일 때 멈춘다. */

SELECT R.EMPLOYEEID, R.LASTNAME, R.FIRSTNAME, R.REPORTSTO,

   A.LEVEL +1, CONVERT(VARCHAR(1000), A.SORT + ‘/’ +

R.EMPLOYEEDID) AS SORT

FROM EMPLOYEES_ANCHOR A, EMPLOYEES R

WHERE A.EMPLOYEEID = R. REPOPTSTO

)

SELECT LEVE, EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO

FROM EMPLOYEES_ACHOR

ORDER BY SORT

GO

2. 셀프 조인

동일 테이블 사이의 조인을 의미

예시: 1단계 셀프조인)

SELECT WORKER.EMPNO, WORKER.ENAME, MANAGER.ENAME

FROM EMP WORKER, EMP MANAGER

WHERE WORKER.MGR = MANAGER.EMPNO;