2-2-6. 셀프 조인과 계층형 질의

1. 셀프 조인(Self-Join)

1️⃣ 셀프 조인 기초 개념

1) 셀프 조인

• 동일한 테이블을 두 번 이상 참조하여 서로 다른 별칭(alias)을 사용해 수행됨

• 테이블 내 행 간의 관계 조사 가능

• 예
• 직원 테이블에서 각 직원과 그 직원의 관리자 조회

2️⃣ 셀프 조인의 예시

1) 자신의 MGR(매니저) 찾기

SELECT e1.EMPNO AS EmployeeID, e1.ENAME AS Employee_Name,
			 e2.EMPNO AS ManagerID, e2.ENAME AS Manager_Name
FROM EMP e1 LEFT OUTER JOIN EMP e2
ON e1.MGT = e2.EMPNO;

2. 계층형 질의(Hierarchical Query) ⭐

1️⃣ 계층형 질의 기초 개념

1) 계층형 질의

• 데이터베이스에서 트리 구조 또는 계층 구조 데이터를 검색하는 방법

2) 계층 구조 데이터

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

• 예
• 사원 테이블 : 사원들 사이에 상위 사원(관리자)과 하위 사원 관계 존재
• 조직 테이블 : 조직들 사이에 상위 조직과 하위 조직의 관계 존재

2️⃣ 오라클에서의 계층형 질의

1) 오라클에서의 계층형 질의 형식

SELECT 칼럼#1, 칼럼#2, ...
FROM 테이블명

START WITH 조건#1
CONNECT BY [PRIOR] 조건#2
ORDER SIBLINGS BY 칼럼명 [ ASC | DESC ];

• START WITH 조건#1
• 테이블에서 계층 구조가 시작하는 조건 명시
• 조건#1을 만족하면 그 행부터 계층 구조 시작
• 이 시작점을 루트 노드(ROOT NODE)라고 부름

• CONNECT BY [PRIOR] 조건#2
• 조건#2를 만족하는 계층 구조로 조회

• ORDER SIBLINGS BY 칼럼명 [ASC|DESC]
• 계층 구조의 형제 노드를 정렬하는 기준 명시

2) 계층형 질의 예시

• 기본적인 계층형 질의 예시

SELECT EMPNO, ENAME, MGR, LEVEL
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER BY LEVEL;

EMPNO	ENAME	MGR	LEVEL
7839	KING	NULL	1
7566	JONES	7839	2
7698	BLAKE	7839	2
7782	CLARK	7839	2
7902	FORD	7566	3
7521	WARD	7698	3
7900	JAMES	7698	3
7934	MILLER	7782	3
7499	ALLEN	7698	3
7788	SCOTT	7566	3
7654	MARTIN	7698	3
7844	TURNER	7698	3
7876	ADAMS	7788	4
7369	SMITH	7902	4

• SYS_CONNECT_BY_PATH 계층형 질의 예시

SELECT EMPNO, ENAME, MGR, LEVEL, SYS_CONNECT_BY_PATH(ENAME, '/') AS PATH
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER BY LEVEL;

EMPNO	ENAME	MGR	LEVEL	PATH
7839	KING	NULL	1	/KING
7566	JONES	7839	2	/KING/JONES
7698	BLAKE	7839	2	/KING/BLAKE
7782	CLARK	7839	2	/KING/CLARK
7902	FORD	7566	3	/KING/JONES/FORD
7521	WARD	7698	3	/KING/BLAKE/WARD
7900	JAMES	7698	3	/KING/BLAKE/JAMES
7934	MILLER	7782	3	/KING/CLARK/MILLER
7499	ALLEN	7698	3	/KING/BLAKE/ALLEN
7788	SCOTT	7566	3	/KING/JONES/SCOTT
7654	MARTIN	7698	3	/KING/BLAKE/MARTIN
7844	TURNER	7698	3	/KING/BLAKE/TURNER
7876	ADAMS	7788	4	/KING/JONES/SCOTT/ADAMS
7369	SMITH	7902	4	/KING/JONES/FORD/SMITH

• CONNECT_BY_ROOT 계층형 질의 예시

SELECT CONNECT_BY_ROOT ENAME AS root_manager,
			 SYS_CONNECT_BY_PATH(ENAME, '/') AS PATH,
			 EMPNO, ENAME, MGR, LEVEL
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

root_manager	PATH	EMPNO	ENAME	MGR	LEVEL
KING	/KING	7839	KING	ㅤ	1
KING	/KING/JONES	7566	JONES	7839	2
KING	/KING/JONES/SCOTT	7788	SCOTT	7566	3
KING	/KING/JONES/SCOTT/ADAMS	7876	ADAMS	7788	4
KING	/KING/JONES/FORD	7902	FORD	7566	3
KING	/KING/JONES/FORD/SMITH	7369	SMITH	7902	4
KING	/KING/BLAKE	7698	BLAKE	7839	2
KING	/KING/BLAKE/ALLEN	7499	ALLEN	7698	3
KING	/KING/BLAKE/WARD	7521	WARD	7698	3
KING	/KING/BLAKE/MARTIN	7654	MARTIN	7698	3
KING	/KING/BLAKE/TURNER	7844	TURNER	7698	3
KING	/KING/BLAKE/JAMES	7900	JAMES	7698	3
KING	/KING/CLARK	7782	CLARK	7839	2
KING	/KING/CLARK/MILLER	7934	MILLER	7782	3

• ORDER SIBLINGS BY 계층형 질의 예시

SELECT EMPNO, ENAME, MGR, LEVEL
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ENAME;

EMPNO	ENAME	MGR	LEVEL
7839	KING	NULL	1
7698	BLAKE	7839	2
7499	ALLEN	7698	3
7900	JAMES	7698	3
7654	MARTIN	7698	3
7844	TURNER	7698	3
7521	WARD	7698	3
7782	CLARK	7839	2
7934	MILLER	7782	3
7566	JONES	7839	2
7902	FORD	7566	3
7369	SMITH	7902	4
7788	SCOTT	7566	3
7876	ADAMS	7788	4

3) 순방향 전개와 역방향 전개 ⭐

• CONNECT_BY_ISLEAF 순방향 전개 계층형 질의 예시

SELECT LEVEL, EMPNO, ENAME, MGR, CONNECT_BY_ISLEAF AS ISLEAF
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

• CONNECT_BY_ISLEAF AS ISLEAF
• 전개 과정에서 해당 데이터가 리프 노드(LEAF NODE)이면 1, 그렇지 않으면 0 반환
• 리프노드 : 자식이 없는 노드

LEVEL	EMPNO	ENAME	MGR	ISLEAF
1	7839	KING	NULL	0
2	7566	JONES	7839	0
3	7788	SCOTT	7566	0
4	7876	ADAMS	7788	1
3	7902	FORD	7566	0
4	7369	SMITH	7902	1
2	7698	BLAKE	7839	0
3	7499	ALLEN	7698	1
3	7521	WARD	7698	1
3	7654	MARTIN	7698	1
3	7844	TURNER	7698	1
3	7900	JAMES	7698	1
2	7782	CLARK	7839	0
3	7934	MILLER	7782	1

• 순방향 전개 : 부모 노드에서 자식 노드로 순방향으로 데이터 출력

• 순방향인지 구분하는 쿼리

CONNECT BY PRIOR 자식 칼럼 = 부모 칼럼

• PRIOR : 현재 행의 부모 행을 참조 하라는 의미
• PRIOR의 위치로 순방향/역방향 구분
• CONNECT BY PRIOR 자식 칼럼 = 부모 칼럼 : 순방향
• CONNECT BY 자식 칼럼 = PRIOR 부모 칼럼 : 역방향

• CONNECT_BY_ISLEAF 역방향 전개 계층형 질의 예시

SELECT LEVEL, EMPNO, ENAME, MGR, CONNECT_BY_ISLEAF AS ISLEAF
FROM EMP
START WITH MGR = 7876
CONNECT BY EMPNO = PRIOR MGR;

LEVEL	EMPNO	ENAME	MGR	ISLEAF
1	7876	ADAMS	7788	0
2	7788	SCOTT	7566	0
3	7566	JONES	7839	0
4	7839	KING	NULL	1