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

1. 계층형 질의

테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 계층형 질의(Hierarchical Query) 사용

계층형 데이터

동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터, 엔티티를 순환관계 데이터 모델로 설계할 경우 계층형 데이터 발생

• 루트 노드(루트 데이터)
  가장 최상위의 데이터
• 리프 노드(리프 데이터)
  가장 최하위의 데이터

기본적인 계층형 질의 형태

SELECT ...
FROM ...
WHERE 조건
START WITH 조건
CONNECT BY [NOCYCLE] 조건
[ORDER SIBLINGS BY 컬럼명1, 컬럼명2...];

가. Oracle 계층형 질의

구문	설명
START WITH 절	데이터의 전개가 시작될 데이터를 지정
CONNECT BY 절	자식 데이터 지정
PRIOR	- CONNECT BY 절에서 사용 - PRIOR 자식=부모 형태는 부모에서 자식 방향으로 데이터 전개(순방향) - PRIOR 부모=자식 형태는 자식에서 부모 방향으로 데이터 전개(역방향)
NOCYCLE	데이터를 전개하는 도중 동일 데이터가 다시 나타나면 CYCLE이 발생했다고 함 CYCLE이 발생한 데이터 이후 데이터를 전개하면 런타임 오류가 발생 NOCYCLE을 추가하면 CYCLE이 발생한 이후의 데이터는 전개하지 않아 오류 발생하지 않음
WHERE 절	모든 데이터 전개를 수행한 후 지정된 조건을 만족하는 데이터만을 추출

• 계층형 질의에서 사용되는 가상 컬럼

구문	설명
LEVEL	전개과정에서 루트 데이터이면 1, 그 하위 데이터이면 2/루트에서 리프로 내려갈때 1씩 증가
CONNECT_BY_ISLEAF	전개과정에서 해당 데이터가 리프 데이터이면 1, 그렇지 않으면 0
CONNECT_BY_ISCYCLE	전개과정에서 해당 데이터가 조상으로서 존재하면(자식이 존재하면) 1, 그렇지 않으면 0, CYCLE 옵션을 사용할때만 사용 가능

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

함수	설명
SYS_CONNECT_BY_PATH	루트 데이터로부터 현재 위치까지 전개할 데이터의 경로 표시 SYS_CONNECT_BY_PATH(컬럼명, 경로분리기호)
CONNECT_BY_ROOT	현재 전개할 데이터의 루트 데이터 표시 CONNECT_BY_ROOT(컬럼명)

나. SQL Server 계층형 질의

2000 버전 까지는 계층형 질의를 작성할 수 있는 문법을 지원하지 않았다.
조직도처럼 계층적 구조를 가진 데이터는 저장 프로시저를 재귀 호출하거나 While 루프 문에서 임시 테이블을 사용하는 등 순수한 쿼리가 아닌 프로그램 방식으로 전개한다.
하지만 SQL Server 2005 버전부터 하나의 질의로 원하는 결과를 얻을 수 있다.

• 재귀적 쿼리의 처리 과정

1. CTE 식을 앵커 멤버와 재귀 멤버로 분할
2. 앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합 생성
3. Ti는 입력으로 사용하고 Ti+1은 출력으로 사용하여 재귀 멤버 실행
4. 빈 집합이 반환될 때까지 3단계 반봅
5. 결과 집합을 반환. 이것을 T0~Tn까지 UNION ALL