[SQLD 2-2-6] SQL 활용 - 계층형 질의와 셀프 조인

계층형 질의 (Hierarchical Query)

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

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

 

예를 들어, 사원 테이블에서는 사원들 사이에 상위 사원(관리자)과 하위 사원 관계가 존재하고

조직 테이블에서는 조직들 사이에 상위 조직과 하위 조직 관계가 존재한다.

 

엔티티를 순환관계 데이터 모델로 설계할 경우 계층형 데이터가 발생한다.
순환관계 데이터 모델의 예로는 조직, 사원, 메뉴 등이 있다.

---

Oracle 계층형 질의

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

 

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

 

- PRIOR : CONNECT BY 절에 사용되며, 현재 읽은 컬럼을 지정한다.

PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 자식데이터에서 부모데이터 방향으로 전개하는 순방향 전개를 한다.

PRIOR 부모 = 자식 형태를 사용하면 반대로 부모데이터에서 자식데이터 방향으로 전개하는 역방향 전개를 한다.

 

- NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개 중 다시 나타나면 이것을 사이클이 형성되었다고 말한다.

사이클이 발생한 데이터는 런타임 오류가 발생. 그렇지만 NOCYCLE 을 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.

 

- ORDER SIBLINGS BY : 형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다.

 

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

 

# 순방향 전개 예제

 

A는 루트 데이터이기 때문에 레벨이 1이다.

A의 하위 데이터인 B, C 는 레벨이 2이다.

C의 하위 데이터인 D, E는 레벨이 3이다.

리프 데이터는 B, D, E 이다.

관리자 -> 사원 방향의 전개이기 때문에 순방향 전개이다.

 

# 역방향 전개 예제

 

D는 루트 데이터이기 때문에 레벨이 1이다.

D의 상위 데이터인 C는 레벨이 2이다.

C의 상위 데이터인 A는 레벨이 3이다.

리프 데이터는 A이다.

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

 

 

Oracle 은 계층형 질의를 사용할 때 사용자 편의성을 제공하기 위해 다음과 같은 함수를 제공한다.

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

START WITH를 통해 추출된 루트 데이터가 1건 이기 때문에 루트사원은 모두 A이다.
경로는 루트로부터 현재 데이터까지의 경로를 표시한다.

---

SQL Server 계층형 질의

 

WITH 절의 CTE 쿼리를 보면, UNION ALL 연산자로 쿼리 두 개를 결합했다. 

둘 중 위 에 있는 쿼리를 앵커 멤버(Anchor Member)라고 하고, 아래에 있는 쿼리를 재귀멤버(Recursive Member)라고 한다.

 

아래는 재귀적 쿼리의 처리 과정이다.앵커 멤버가 시작점이자 Outer 집합이 되어 Inner 집합인 재귀 멤버와 조인을 시작한다.

이어서, 앞서 조인한 결과가 다시 Outer 집합이 되어 재귀 멤버와 조인을 반복하다가 조인 결과가 비어 있으면 

즉, 더 조인할 수 없으면 지금까지 만 들어진 결과 집합을 모두 합하여 리턴한다.

---

셀프 조인

- 동일 테이블 사이의 조건을 말한다. 따라서 FROM 절에 동일한 테이블이 두 번 이상 나타난다.

- 테이블과 컬럼 이름이 모두 동일하기 때문에 식별을 위해 반드시 테이블 별칭을 사용해야 한다.

 

# 기본 구조

# 예제

 

셀프 조인은 동일한 테이블(사원)이지만 

[그림 II-2-11]과 같이 개념적으로는 두 개의 서로 다른 테이블(사원, 관리자)을 사용하는 것과 동일하다. 

동일 테이블을 다른 테이블인 것처럼 처리하기 위해 테이블 별칭을 사용한다. 

여기서는 E1(사원), E2(관리자) 테이블 별칭을 사용하였다. 

차상위 관리자를 구하기 위해서 E1.관리자 = E2.사원 조인 조건을 사용한다.