[SQL] ORACLE 계층형 질의

hyunsooSong·2022년 5월 22일

SQLD/SQL기본및활용 sql

SQL

목록 보기

5/14

📈 Oracle 계층형 질의

1. 계층형 질의 기본구조

START WITH : 계층 구조 전개의 시작 위치를 지정한다. 즉, ROOT 데이터를 지정한다. (ACCESS)
CONNECT BY : 결과 목록을 필터링 하기 위한 조건절을 작성한다.
PRIOR : 'CONNECT BY, SELECT, WHERE' 절에서 사용되며, 현재 읽은 컬럼을 지정한다.
- 순방향 전개 : 'PRIOR 자식 = 부모' 형태를 말한다. (부모->자식)
- 역방향 전개 : 'PRIOR 부모 = 자식' 형태를 말한다. (자식 -> 부모)
ORDER SIBLINGS : 형제 노드(동일 LEVEL) 사이에서 정렬 수행한다.
- 루트 노드의 LEVEL 값은 1이다.

C2 -> C1 방향으로 진행
SELECT C3
FROM TAB1
START WITH C2 IS NULL
CONNECT BY PRIOR C1 = C2
ORDER SIBLINGS BY C3 DESC;

2. SQL Server, Oracle 계층형 질의문 특징

SQL Server
1) SQL Server에서의 계층형 질의문은 CTE(Common Table Expression)를 재귀 호출함으로써 계층 구조를 전개한다.

2) SQL Server에서의 계층형 질의문은 앵커 멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행한다.
Oracle
1) Oracle의 계층형 질의문에서 WHERE 절은 모든 전개를 진행한 이후 필터 조건으로서 조건을 만족하는 데이터만을 추출하는데 활용된다.

2) Oracle의 계층형 질의문에서 CONNECT BY, SELECT, WHERE 절에서 사용할 수 있으며 'PRIOR 자식 = 부모'형태로 사용하면 순방향 전개로 수행 된다.

3. 예시

💡 해설

CONNECT BY 절에 작성된 조건절은 WHERE 절에 작성된 조건절과 다르게 START WITH 절에서 필터링된 시작데이터는 결과목록에 포함되어지며, 이후 CONNECT BY 절에 의해 된다. 그러므로 매니저 사원번호가 NULL인 데이터는 결과목록에 포함되며, 이후 recursive 조인에 의해 입사일자가 필터링 된다.

[ 사원 ]

사원번호 (PK)	사원명	입사일자	매니저사원번호 (FK)
001	안영이	2021-01-01	NULL
002	장백기	2021-01-01	001
003	한석율	2022-02-01	001
004	송현수	2022-03-01	001
005	장그래	2022-01-11	NULL
006	오상식	2020-01-01	005
007	김준혁	2020-01-02	005

SELECT 사원번호, 사원명, 입사일자, 매니저사원번호
FROM 사원
START WITH 매니저사원번호 IS NULL
CONNECT BY PRIOR 사원번호 = 매니저사원번호
AND 입사일자 BETWEEN' 2022-01-01' AND '2022-12-31'
ORDER SIBLINGS BY 사원번호;