계층형 질의 / 셀프 조인

계층형 데이터

• 정의
  - 동일 테이블에 계층적으로 상위, 하위 데이터가 포함된 데이터
  - 순환관계 데이터 모델로 설계할 경우 계층형 데이터 발생 (조직, 사원, 메뉴 등)

계층형 질의

• 정의
  - 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해 계층형 질의를 사용
  
  

Oracle

• LEVEL 컬럼
  - Root 데이터면1, 그 하위 데이터면 2, Leaf 데이터까지 1씩 증가
  
  
• CONNECT_BY_ISLEAF 컬럼
  - Leaf 데이터면1, 아니면 0
  
  
• CONNECT_BY_ISCYCLE 컬럼
  - 자식 데이터와 부모 데이터에서 Cycle이 존재하면1, 아니면 0
  
  
• START WITH 절
  - 계층 구조에서 전개 시작 위치를 지정하는 구문
  - Root 데이터를 지정
  - 결과목록에 항상 포함되어 짐
  
  
• WHERE 절
  - 모든 전개를 진행한 이후, 필터 조건으로서 조건을 만족하는 데이터만을 추출하는데 활용
  
  
• CONNECT BY 절
  - 다음에 전개될 자식 데이터를 지정하는 구문
  - 여기에 사용된 조건절은 WHERE절에 작성된 조건과는 다르다. (START WITH절에서 필터링된 시작 데이터는
  
  
• PRIOR
  - SELECT, CONNECT BY 절에서 사용
  - 상위 행을 참조하기 위함
  - CONNECT BY PRIOR 개발팀 = 개발1팀 : 개발1팀에서 개발팀으로 (개발1팀의 부모를 찾자)
  - CONNECT BY PRIOR 개발1팀 = 개발팀 : 개발팀에서 개발1팀으로 (개발팀의 자식을 찾자)
  
  
• NOCYCLE
  - Cycle이 발생한 데이터는 런타임 오류 발생
  
  
• SYS_CONNECT_BY_PATH(컬럼, 경로분리자)
  - 루트 데이터부터 전개할 데이터까지의 경로를 표시
  
  
• CONNECT_BY_ROOT 컬럼
  - 루트 데이터를 표시
  
  
• ORDER SIBLINGS BY
  - 동일 부모를 가진 자식들끼리 정렬 기준을 주는 것
  
  
• 코드 예시
  
  - SMITH(7369)의 관리자는 MGR컬럼 값 = FORD(7902)이다.
  - 이렇게 상위 계층(관리자)과 하위 계층(사원)의 관계를 조회 할 수 있다.
  
  > 직업이 PRESIDENT인 것을 기준으로 계층 구조 조회
  SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ENAME ENAME, EMPNO, MGR
  FROM SCOTT.EMP
  START WITH JOB = 'PRESIDENT'
  CONNECT BY PRIOR EMPNO = MGR;
  
  - 직업이 PRESIDENT인 것은 KING이고 ROOT이다. (LEVEL의 값이 1)
  - 그 하위 노드는 KING이 관리하는 사원들이다. (LEVEL의 값이 2)
  
  > PRIOR 연산자를 SELECT 절에서 사용
  SELECT LEVEL, LPAD(' ', 4*(LEVEL-1)) || ENAME ENAME,
          PRIOR ENAME MGRNAME,
          EMPNO, MGR, JOB
  FROM SCOTT.EMP
  START WITH JOB = 'PRESIDENT'
  CONNECT BY PRIOR EMPNO = MGR;

- PRIOR은 이전의 노드 즉, 상위 계층을 뜻한다. 따라서, PRIOR ENAME은 관리자의 이름이다.

SQL Server

• 계층형 질의 문법 지원X
• CTE(Common Table Expression)를 재귀 호출함으로써 계층 구조 전개
• 앵커 멤버를 실행하여 기본 결과 집합을 만들고 이후 재귀 멤버를 지속적으로 실행

셀프 조인

• 정의
  - 동일 테이블 사이의 JOIN (FROM절에 같은 테이블이 두 번 이상 나타남)
  - 한 테이블 내에서 두 컬럼이 연관 관계가 있음
  - 반드시 Alias를 사용 해야 함