SQLD 4-3 ] 계층형 질의와 셀프 조인
❏ 계층형 질의
- 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터 ?
그러니까, 한 테이블 내에서 사원 A 의 관리자 되는 사람은 지정 안되있고
사원 B, 사원 C의 관리자는 사원 A 이다.
❏ ORACLE 계층형 질의
1. START WITH : 계층 구조 전개의 시작 위치를 지정하는 구문. (루트 데이터를 지정. 액세스)
2. CONNECT BY : 다음에 전개될 자식 데이터를 지정하는 구문. 자식 데이터는 CONNECT BY 절에 있는 조건에 만족해야 한다.(조인)
3. PRIOR : 저기엔 안나와있지만 CONNECT BY 절에 사용되며, 현재 읽은 칼럼을 지정한다.
PRIOR 자식 = 부모 : 부모 -> 자식 방향으로 전개
PRIOR 부모 = 자식 : 자식 -> 부모 방향으로 전개
4. NOCYCLE : 데이터를 전개하면서 이미 나타났던 동일한 데이터가 전개중에 다시 나오는것을 CYCLE 이 형성됬다고 한다. 사이클이 일어나면 런타임 오류가 일어난다. 그렇지만 NOCYCLE 을 쓰면 사이클이 발생한 이후의 데이터는 전개 하지 않는다
5. ORDER SIBLINGS BY : 형제노드(동일 LEVEL) 사이에서 정렬을 수행한다.
6. WHERE : 모든 전개를 다 하고 나서 조건을 만족하는 데이터를 추출한다 (필터링)
계층형 질의를 사용할 때 위와 같은 가상 칼럼을 사용할 수 있다.
EMP 테이블의 내용이다. EMPNO 는 사원의 순수 고유 번호? 이고 MGR 은 매니저 번호. 그러니까.. 1번 행 보면
7369 사원번호 SMITH 의 사수 (MGR) 은 7902 (FORD) 이다.
이렇게 한 테이블 내에 서로 왔다갔다 하는게 있네.
LPAD 는 들여쓰기 하는 함수 이다.
MGR 의 NULL 부터 시작 (start with) 하고, 다음 전개될 값은 prior empno = mgr
부모 ( MGR ) -> 자식 ( EMPNO ) 를 탐색할꺼니까.. 관리자 번호 보고 그거에 해당하는 관리자번호 들을 찾을꺼니까
PRIOR 자식 = 부모 로 써야겠고, PRIOR empno = mgr 이다.
위의 테이블 내용들을 보면..
1. MGR(NULL)-> EMPNO(7839) : KING 의 사수가 null 이다. 자기가 제일 위!!. LEVEL 도 1 이고, leaf 도 아니다. leaf 는 맨 끝 잎사귀.
2. 이 EMPNO(7839) KING 씨는 또 누군가의 사수 이겠지. MGR 에 7839 를 찾자. 4행, 6행, 7행 의 사수는 KING 이다.
이렇게..
A 는 루트 데이터기 때문에 레벨이 1이다. A의 하위 데이터는 B와 C가 있고 레벨이 2다. 라고 책에 나와있지만.
? 이거 설명이 좀 이상하다. A KING 씨에서 뻗어나온건 3건 인데.??
하튼.. 계층형 질의에 대한 논리적인 모습 이고 관리자 -> 사원 순방향 전개이다.
반대로, 역방향 전개도 보자
루트랑 부모랑은 엄연히 다르다!!!
처음에 이것땜에 헷갈려서 시간 엄청걸렸다.
위의 쿼리문을 보면 start with EMPNO = '7876'
루트 다 루트. 시작을 저기서 한다는 것 뿐이고
부모 자식 은 이미 역방향으로 할 생각이 있기 때문에!
PRIOR MGR = EMPNO 로
PRIOR 부모 = 자식 형태로 쓰이기 때문에. 자식 -> 부모 로 거슬러 올라가고. 시작 루트는 EMPNO = '7876' 인것이다.
<< PRIOR 이 제일 중요하다!!! 순방향으로 갈껀지, 역방향으로 갈껀지 정해야 한다. >>
아까 ORACLE 에서 계층형질의 쓸때 사용되는 가상칼럼 3가지 가 있었고
이번에는 ORACLE 에서 계층형질의 에서 사용할 수 있는 함수 2가지다.
| 루트사원 | 경로 | 사원 | 관리자 |
| 7839 | /7839 | 7839 | |
| 7839 | /7839/7566 | 7566 | 7839 |
| 7839 | /7839/7566/7788 | 7788 | 7566 |
| 7839 | /7839/7566/7788/7876 | 7876 | 7788 |
CONNECT BY : 다음에 전개될 자식 데이터를 지정하는 구문. 자식 데이터는 CONNECT BY 절에 있는 조건에 만족해야 한다.(조인)
CONNECT BY 의 실행순서
1. START WITH 절
2. CONNECT BY 절
3. WHERE 절
ORDER SIBLINGS BY : 형제노드(동일 LEVEL) 사이에서 정렬을 수행한다.
마지막에 ORDER SIBILINGS BY EMPNO DESC
이렇게..
❏ SQL SERVER 계층형 질의
WITH EMPLOYEES_ANCHOR AS (
SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, PREPORTSTO, 0 AS LEVEL
FROM EMPLOYEES
WHERE REPORTSTO IS NULL <--- 재귀호출의 시작점
UNION ALL
SELECT R.EMPLOYEEID, R.LASTNAME, R.FIRSTNAME, R.REPORTSTO, A.LEVEL + 1
FROM EMPLOYEES_ANCHOR A, EMPLOYEES R
WHERE A.EMPLOYEEID = R.REPORTSTO )
SELECT LEVEL, EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO
FROM EMPLOYEES_ANCHOR
GO
이게 가장 기본적인 틀이다. with ~ as ( ) 는 SQL Server 에서 쓰는 문법이고,
EMPLOYEES_ANCHOR 라는 이름으로 가상으로 ? 하나 테이블 만들어 논다는 뜻이다. 별칭이다.
내용은 as ( ) 의 내용들로. 근데 그 안에 구성된 내용들을 보면. UNION ALL 으로 두 개의 쿼리결과를 중복도 싹 다 합집합 하였다.
❏ 셀프 조인
- 동일 테이블 사이의 조인 . ? 쌍둥이 테이블 끼리 조인을 한다는건가?? 왜..??;;
FROM 절에 동일 테이블이 두번 이상 써야하기 때문에.. 구분하기 위해서 ALIAS 를 사용한다!
SELECT WORKER.EMPNO 사원번호, WORKER.ENAME 사원명, MANAGER.ENAME 관리자명
FROM EMP WORKER, EMP MANAGER
WHERE WORKER.MGR = MANAGER.EMPNO;
다시 MGR 테이블을 보자.
음. 만약 1번째 행 보면. EMPNO 7369 사원의 사수는 7902 (FORD 씨) 이다.
그의 또 사수 는 7566 번 (JONES 씨) 이다.
이렇게. 차상위 결과가 궁금할 때 쓸 수 있다. 하나의 테이블에서는 그냥 상위만 알 수 있는데
상위의 한칸 더 위인 차상위 를 알수 있다.
select E1.EMPNO 사원, E1.MGR 관리자, E2.MGR 차상위관리자
from EMP E1 LEFT OUTER JOIN EMP E2
on (E1.MGR = E2.EMPNO)
order by E2.MGR DESC, E1.MGR, E1.EMPNO;
핵심이 ON 조건이네.
매니저 = 사원번호
순서가 바뀌면 이상하다.
한번 펜으로 두개의 테이블 그려보면 이해가 빠르다
맨 마지막줄 보면, select 에서 E1 의 EMPNO, E1 의 관리자, E2 의 MGR 을 추출했다.
이렇게 7369 의 형의 형은 7566 인것을 알 수 있다