테이블에 계층형 데이터가 존재하는 경우 계층형 질의를 사용할수 있습니다. 계층형 데이터란 동일 테이블에 계층적으로 상위, 하위 데이터가 함께 포함된 구조의 테이블을 말합니다. 예를들어, 사원 테이블의 상위 사원(관리자)와 하위 사원이 함께 존재한다. 엔터티를 순환 관계 데이터 모델로 설계할 경우 계층형 데이터가 발생합니다.
셀프 조인
동일 테이블을 조인하는 것을 말합니다. 따라서 FROM 절에 동일 테이블이 두 번 이상 나타납니다. 동일 테이블 사이의 조인을 수행하면 테이블 칼럼 이름이 모두 동일하므로 식별을 위해 반드시 테이블 별칭을 사용해야합니다. 셀프조인의 형식은 다음과 같습니다.
SELECT ALIAS1.COLUMN, ALIAS2.COLUMN,...
FROM TABLE1.ALIAS1, TABLE1.ALIAS2
WHERE ALIAS2.COLUMN = ALIAS1.COLUMN
셀프조인의 실제 활용예시 입니다.
-- EMPLOYEE_ID 가 100 인 직원의 자식노드 출력
SELECT B.EMPLOYEE_ID , B.MANAGER_ID
FROM HR.EMPLOYEES A, HR.EMPLOYEES B
WHERE A.EMPLOYEE_ID = 100
AND B.MANAGER_ID = A.EMPLOYEE_ID
-- EMPLOYEE_ID 가 100 인 직원의 자식노드의 자식노드 출력
SELECT C.EMPLOYEE_ID , C.MANAGER_ID , B.MANAGER_ID
FROM HR.EMPLOYEES A, HR.EMPLOYEES B, HR.EMPLOYEES C
WHERE A.EMPLOYEE_ID = 100
AND B.MANAGER_ID = A.EMPLOYEE_ID
AND C.MANAGER_ID = B.EMPLOYEE_ID
-- EMPLOYEE_ID 가 172 인 직원의 부모노드를 출력
SELECT B.EMPLOYEE_ID , B.MANAGER_ID
FROM HR.EMPLOYEES A, HR.EMPLOYEES B
WHERE A.EMPLOYEE_ID = 172
AND A.MANAGER_ID = B.EMPLOYEE_ID
-- EMPLOYEE_ID 가 172 인 직원의 부모노드의 부모노드
SELECT C.EMPLOYEE_ID , C.MANAGER_ID
FROM HR.EMPLOYEES A, HR.EMPLOYEES B, HR.EMPLOYEES C
WHERE A.EMPLOYEE_ID = 172
AND A.MANAGER_ID = B.EMPLOYEE_ID
AND B.MANAGER_ID = C.EMPLOYEE_ID계층형 질의
- START WITH : 계층 구조 전개의 시작 위치를 지정하는 구문으로 루트 데이터를 지정합니다.
- CONNECT BY : 다음에 전개될 자식 데이터를 지정하는 구문으로 자식 데이터는 CONNECT BY의 CONDITION 을 만족해야합니다.
- PRIOR : CONNECT BY 절에 사용하며, 현재 칼럼을 지정합니다. 예를들어, (FK) = PRIOR (PK) 형태로 지정하면 부모 데이터에서 자식데이터 방향으로 순방향 전개합니다. 반대로, (PK) = PRIOR (FK) 형태를 사용하면 자식 데이터에서 부모 데이터 방향으로 역방향 전개합니다.
- NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개중에 다시 나타나면 더이상 전개하지 않게 합니다.
- ORDER SIBLINGS BY : 형제 노드 사이에서 정렬을 수행합니다.
계층형 질의에서 사용되는 가상 칼럼
- LEVEL : 루트 데이터 1을 기준으로 리프 데이터 까지 1씩 증가합니다.
- CONNECT_BY_ISLEAF : 전개 과정에서 해당 데이터가 리프 데이터면 1, 그렇지 않으면 0 으로 표시됩니다.
- CONNECT_BY_ISCYCLE : 전개 과정에서 자식을 갖는데, 해당 데이터가 조상으로 존재하면 1, 그렇지 않으면 0으로 표시됩니다. 여기서 조상이란 자신으로부터 루트까지의 경로에 존재하는 데이터를 말합니다. CYCLE 옵션을 사용했을때만 사용할 수 있습니다.
SELECT ...
FROM TABLE
WHERE CONDTION
AND CONDITION ...
START WITH CONDIION AND CONDITION ...
CONNECT BY [NOCYCLE] CONDITION AND CONDITION ...
[OPDER SIBLINGS BY COLUMN, COLUMN, ...]
실제 활용 예시는 다음과 같습니다.
-- EMP Table을 계층형 질의 구문을 이용해 조회합니다. PK인 EMPLOYEE_ID 앞쪽에 PRIOR 연산자를 기술하고 결과 데이터를 들여쓰기하기 위해 LPAD 함수를 사용합니다.
-- 최상위관리자로 부터 말단 직원을 찾는 정방향 전계의 예입니다. PK인 EMPLOYTEE_ID 앞쪽에 PRIOR 연산자를 기술합니다.
SELECT LEVEL AS LV, LPAD(' ', (LEVEL - 1) * 2) || EMPLOYEE_ID, MANAGER_ID
, CONNECT_BY_ISLEAF AS ISLEAF
FROM HR.EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY MANAGER_ID = PRIOR EMPLOYEE_ID
-- 113 번 사원으로부터 자신의 상위관리자를 찾는 역방향 전개의 예이다. FK인 MANAGER_ID 앞쪽에 PRIOR 연산자를 기술합니다.
SELECT LEVEL AS LV, LPAD(' ', (LEVEL - 1) * 2) || EMPLOYEE_ID, MANAGER_ID
, CONNECT_BY_ISLEAF AS ISLEAF
FROM HR.EMPLOYEES
START WITH EMPLOYEE_ID = '113'
CONNECT BY PRIOR MANAGER_ID = EMPLOYEE_ID
ORACLE 계층형 질의를 사용할 때 사용자 편의성을 제공하기 위해 함수를 제공합니다.
- SYS_CONNECT_BY_PATH : 루트 데이터로부터 현재 전개할 데이터까지의 경로를 표시한다.
- CONNECT_BY_ROOT : 현재 전개할 데이터의 루트 데이터를 표시한다.
SELECT CONNECT_BY_ROOT (EMPLOYEE_ID) AS ROOT_EMPID
, SYS_CONNECT_BY_PATH(EMPLOYEE_ID, ',')
, EMPLOYEE_ID , MANAGER_ID
FROM HR.EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY MANAGER_ID = PRIOR EMPLOYEE_ID ;
