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

1. 계층형 질의

계층형 데이터란 ? 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터

 

가. ORACLE 계층형 질의

SELECT 컬럼명

FROM 테이블

WHERE 조건 AND 조건

START WITH 조건

CONNECT BY [NOCYCLE] 조건 AND 조건

[ORDER SIBLING BY 칼럼명 … ] ;

• STRART WITH : 전개의 시작위치 지정, 루트 데이트 지정
• CONNECT BY : 다음에 전개되는 자식 데이터를 지정하는 구문
• PRIOR 자식 = 부모 : 자식데이터에서 부모방향으로 전개하는 순방향 전개
• PRIOR 부모 = 자식 : 부모데이터에서 자식방향으로 전개하는 역방향 전개
• ORACLE에서는 PRIOR은 SELECT, WHERE절에서도 사용 가능
• NOCYCLE : 데이터를 전개하다 이미 나타난 동일한 데이터가 나타나면 오류가 발생되는데 그것을 방지하기 위해 설정
• ORDER SIBLINGS BY : 형제 노느 사이에서 정렬을 수행
• WHERE : 모든 전개를 수행한 후 지정된 조건을 만족하는 데이터만 추출
• 가상칼럼 제공

가상칼럼	설명
LEVEL	루트 데이터면 1, 그 하위 데이터면 2, 리프 데이터까지 1씩 증가
CONNECT_BY_ISLEAF	리프 데이터면 1, 아니면 0
CONNECT_BY_ISCYCLE	자식을 갖는데 해당 데이터가 조상(자신으로부터 루트까지 경로가 존재하는)으로서 존재하면 1, 아니면 0

예제) SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || 사원 사원, 관리자, CONNECT_BY_ISLEAF ISLEAF

FROM 사원

START WITH 관리자 IS NULL

CONNECT BY PRIOR 사원 = 관리자;

 

[실행 결과] LEVEL 사원 관리자 ISLEAF

------- -------- ------- -------

1        A                         0

2           B          A          1

2           C          A          0

3              D       C          1

3              E        C          1

• LEFT 데이터는 B, D, E 임 / C는 또 다른 LEFT들이 있기에 0

 

예제) SELECT LEVEL, LPAD(' ', 4 * (LEVEL-1)) || 사원 사원, 관리자, CONNECT_BY_ISLEAF ISLEAF

FROM 사원

START WITH 사원 = 'D'

CONNECT BY PRIOR 관리자 = 사원;

[실행 결과] LEVEL 사원 관리자 ISLEAF

-------- ----- -------- --------

1         D           C          0

2            C         A         0

3               A                 1

• LEFT 데이터는 A, 역순으로 할때 가장 끝에 있기 때문에

 

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

함수	설명
SYS_CONNECT_BY_PATH	루트데이터부터 현재까지 전개한 경로 표시
CONNECT_BY_ROOT	현재 전개할 데이터의 루트 데이터 표시, 단항 연산자

예제) SELECT CONNECT_BY_ROOT 사원 루트사원, SYS_CONNECT_BY_PATH(사원, '/') 경로, 사원, 관리자

FROM 사원

START WITH 관리자 IS NULL

CONNECT BY PRIOR 사원 = 관리자;

 

[실행 결과] 루트사원  경로   사원  관리자

 ----------  -----   ------  ------

A             /A         A

A             /A/B      B      A

A             /A/C      C     A

A             /A/C/D  D     C

A             /A/C/E   E     C

• 모두 시작은 A이기에 루트사원은 A

 

나. SQL SERVER 계층질의

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

• 상위 쿼리를 앵커 맴버, 아래있는 쿼리는 재귀 멤버, OUTER 집합이 되어 INNER 집합인 재귀맴버와 조인을 반복
1. CTE 식을 앵커 맴버와 재귀 맴버로 분할
2. 앵커 맴버를 실행하여 첫 호출 또는 기본 결과 집합 만듬
3. Ti는 입력하고 Ti+1은 출력하여 재귀맴버 실행
4. 빈집합이 될때까지 반복
5. 결과집합 반환
• SORT 구분 컬럼 추가와 조직도와 같은 모습으로 출력하려면 ORDER BY절에서 정렬 필요

 

2. 셀프조인

동일 테이블 사이의 조인, FROM 절에서 동일 테이블이 두번이상 나타남

테이블 별칭을 반드시 사용해야 함, 칼럼에도 어느 테이블 칼럼인지 식별 필요

 

SELECT ALIAS명1.칼럼명, ALIAS명2.칼럼명, ...

FROM 테이블1 ALIAS명1, 테이블2 ALIAS명2

WHERE ALIAS명1.칼럼명2 = ALIAS명2.칼럼명1;

차상위 데이터를 추출할 때 누락되지 않도록 OUTER JOIN 사용 가능