2장_3절. 계층형 질의와 셀프 조인

1. 계층형 질의

->테이블에 계층형 데이터가 존재하는 경우, 데이터를 조회하기 위해 계층형 질의를 사용한다(예를들어 A사원의 하위조직이 B,C일때)

가.Orale 계층형 질의

SELECT ...

FROM 테이블

WHERE condition AND condition ...

START WITH condition

CONNECT BY [NOCYCLE] condition AND condition ...

[ORDER SIBLINGS BY column, column, ...]

(1)START WITH절 : 계층 구조 전개의 시작위치를 지정하는 구문(root설정)

(2)CONNECT BY절 : 다음에 전개될 자식 데이터를 지정하는 구문, 자식은 JOIN조건을 만족해야한다

(3)PRIOR : CONNECT BY절에 사용하며, 현재 읽은 칼럼을 지정한다.

PRIOR 자식 = 부모 형태를 사용하면 계층구조에서 (부모->자식)방향으로 전개(순방향 전개)

PRIOR 부모 = 자식 형태를 사용하면 계층구조에서 (자식->부모)방향으로 전개(역방향 전개)

(4)NOCYCLE : 사이클이 발생한 데이터를 전개하지 않는다

(5)ORDER SIBILINGS BY : 형제노드 사이에서 정렬을 수행한다.

(6)WHERE : 모든 전개(1~5)를 수행한 후 WHERE조거절을 수행한다.

#계층형 질의에서 사용되는 가상칼럼

(1)LEVEL : 루트데이터면1, 하위데이터면2, 그 하위면+1...

(2)CONNECT_BY_ISLEAF : 해당 데이터가 리프면1, 아니면0

(3)CONNECT_BY_ISCYCLE : 해당 데이터가 사이클이면1, 아니면0

SQL> SELECT LEVEL,LPAD(' ',4*(LEVEL-1)) || EMPNO 사원,<- ' '빈칸4개 * 레벨만큼 띄어쓰기

  2  MGR 관리자, CONNECT_BY_ISLEAF ISLEAF

  3  FROM EMP

  4  START WITH (MGR IS NULL)

  5  CONNECT BY PRIOR EMPNO = MGR);<-EMPNO가 자식 MGR이 부모. 

또는

SQL> select LEVEL, LPAD(' ',4*(LEVEL-1)) || EMPNO 사원,

  2  MGR 관리자, CONNECT_BY_ISLEAF 리프인가

  3  FROM EMP

  4  START WITH (MGR IS NULL)

  5  CONNECT BY ((NOCYCLE) PRIOR EMPNO = MGR)<-EMPNO가 자식, MGR이 부모.

  6  ORDER SIBLINGS BY MGR;

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

(1)SYS_CONNECT_BY_PATH(칼럼,경로분리자) : ROOT부터 현재 전개할 데이터까지 경로표시

(2)CONNECT_BY_ROOT 칼럼 : 현재 전개할 데이터의 루트를 표시한다. 단항연산자임

SQL> SELECT CONNECT_BY_ROOT EMPNO 루트사원, <-루트 표시SYS_CONNECT_BY_PATH(EMPNO, '->') 경로, <-경로 표시

EMPNO 사원, MGR 관리자

  2  FROM EMP

  3  START WITH (MGR IS NULL)

  4  CONNECT BY (PRIOR EMPNO = MGR);

또는

SQL> select connect_by_root empno, sys_connect_by_path(empno,'->') 경로,

  2  empno 사원, mgr 관리자

  3  from emp

  4  start with mgr is null

  5  connect by prior empno = mgr;

나. SQL Server 계층형 질의(시험에 안나오지 않음??생략)

2. 셀프조인(Self join)

->셀프조인이란 동일 테이블 사이의 조인을 말함.

->from절에 동일테이블을 두 번 이상 사용

->동일테이블에서 셀프조인시에 칼럼명이 동일하므로, 반드시 식별자를 사용(alias)

ex)자신과 상위, 차상위 관리자를 같은줄에 표시하라

SQL> SELECT E1.EMPNO 사원, E1.MGR 관리자, E2.MGR 차상위_관리자

  2  FROM EMP E1, EMP E2

  3  WHERE E1.MGR = E2.EMPNO <-차상위 관리자는 직속관리자를 기준으로 셀프조인

  4  ORDER BY E2.MGR DESC, E1.EMPNO;

최상위 관리자가 누락됨 : inner join을 사용하여, 관리자가 존재하지 않는경우가 누락

SQL> SELECT E1.EMPNO 사원, E1.MGR 관리자, E2.MGR 차상위_관리자

  2  FROM EMP E1 left outer join EMP E2 <-OUTER 조인을 사용하여 최상위 관리자보이기

  3  ON E1.MGR = E2.EMPNO

  4  ORDER BY 차상위_관리자 DESC, 관리자, 사원;