2.2.3 계층형 질의와 셀프 조인

계층형 질의

• 계층형 데이터란 동일 테이블에 계층적으로 상위와 하위 데이터가 포함된 데이터를 말한다.

오라클 계층형 질의

• START WITH: 루트데이터 지정
• CONNECT BY: 자식 데이터 지정
• PRIOR: CONNECT BY 절에 사용됨
  • PRIOR 자식 = 부모 일 경우 순방향 전개
  • PRIOR 부모 = 자식 일 경우 역방향 전개
• NOCYCLE: NOCYCLE 추가 시 사이클 발생 이후의 데이터는 전개하지 않는다.
• ORDER SIBILINGS BY: 형제 노드 사이에서 정렬을 수행
• WHERE: 모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출
• 오라클은 가상 칼럼을 제공함.

순방향 전개

SELECT LEVEL, LPAD(' ' , 4 * (LEVEL -1)) || EMPNO 사원,
    MGR 관리자, CONNECT_BY_ISLEAF ISLEAF
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

• PRIOR 사원번호 = 관리자 (마지막 줄) => 순방향 전개
• LEVEL (데이터의 레벨, 루트가 1)
• CONNECT_BY_ISLEAF(말단 노드인지 검사)
• CONNECT_BY_ISCYCLE(조상이 존재하면 1, 그렇지 않으면 0, CYCLE 옵션 사용 시 사용가능)

역방향 전개

SELECT LEVEL, LPAD(' ' , 4 * (LEVEL -1)) || EMPNO 사원, 
    MGR 관리자, CONNECT_BY_ISLEAF ISLEAF
FROM EMP
START WITH EMPNO = '7876'
CONNECT BY PRIOR MGR = EMPNO;

• PRIOR 관리자 = 사원번호 (마지막 줄) => 역방향 전개

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

SQL Server 계층형 질의

• CTE(COMMON TABLE EXPRESSION) 재귀호출로 데이터의 최상위부터 하위 방향으로 계층 구조를 전개하도록 작성한 쿼리

WITH EMPLOYEES_ANCHOR AS (
    SELECT EMPLOYEEID, LASTNAME, FIRSTNAME, REPORTSTO, 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

• UNION ALL 연산자로 두 쿼리 결합함
• 둘 중 위에 있는 쿼리: 앵커 멤버
• 아래에 있는 쿼리: 재귀 멤버

재귀적 쿼리의 처리과정

1. CTE 식을 앵커멤버와 재귀멤버로 분할
2. 앵커 멤버를 실행하여 첫 번째 호출 또는 기본 결과 집합(TO)을 만듦
3. Ti는 입력으로 사용하고 T(i+1)는 출력으로 사용하여 재귀 멤버를 실행
4. 빈 집합이 반환될 때까지 3단계를 반복
5. 결과 집합을 반환, T0에서 Tn까지의 UNION ALL

셀프 조인

• 동일 테이블 사이의 조인
• 별칭 쓰는 것이 필수적 (테이블, 칼럼이름 모두 동일하기에)
• 한 테이블 내에 두 칼럼이 연관관계 있을 경우 수행

SELECT WORKER.EMPNO 사원번호, WORKER.ENAME 사원명, MANAGER.ENAME 관리자명
FROM EMP WORKER, EMP MANAGER
WHERE WORKER.MGR = MANAGER.EMPNO;