[SQL] SQL 활용 계층 형 정의와 SELF 조인

이 게시글은 패스트캠퍼스의 '데이터베이스와 SQLD 합격패스 Online' 강의를 요약한 내용입니다.

---

계층형 질의

1. 테이블에 계층형 데이터가 존재하는 경우 데이터를 조회하기 위해서 계층형 질의(Hierarchical Query)를 사용.

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

오라클 계층 형 SQL

구분	설명
SELECT	조회하고자 하는 컬럼을 지정한다.
FROM TABLE	대상 테이블을 지정한다.
WHERE	모든 전개를 수행한 후에 지정된 조건을 만족하는 데이터만 추출한다. (필터링)
START WITH	계층 구조 전개의 시작 위치를 지정. 즉 루트 데이터를 지정한다.
CONNECT BY [NOCYCLE][PRIOR] A AND B	- CONNECT BY절은 다음에 전개될 자식 데이터를 지정하는 구문. - PRIOR 자식 = 부모 : 자식 -> 부모 방향으로 순방향 전개 - PRIOR 부모 = 자식 부모 -> 자식 방향으로 역방향 전개 - NOCYCLE을 추가하면 사이클이 발생한 이후의 데이터는 전개하지 않는다.
ORDER SIBLING BY 컬럼	형제 노드(동일 LEVEL) 사이에서 정렬을 수행한다.

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

구분	설명
LEVEL	- 루트 데이터면 1 - 그 하위 데이터면 2 - 하위데이터가 있을때마다 1씩 증가
CONNECT_BY_ISLEAF	전개과정에서 해당 데이터가 리프 데이터이면 1 그렇지 않으면 0.
CONNECT_BY_ISCYCLE	전개과정에서 자식을 갖는데 해당 데이터가 조상으로서 존재하면 1 그렇지 않으면 0.

---

계층형 SQL

• 관리자 사원번호가 널인 값을 첫 시작 값으로 지정 (관리자가 없는 사람이 첫 시작 값)
• PRIOR 자식 = 부모 순으로 순방향 전개
• CONNECT_BY_ISLEAF를 이용하여 LEAF 노드인 경우 1을 출력

SELECT LEVEL LVL
     , LPAD(' ', 4*(LEVEL-1))|| EMP_NO || '(' || EMP_NM || ')' AS "조직인원"
     , A.DEPT_CD
     , B.DEPT_NM
     , CONNECT_BY_ISLEAF
FROM TB_EMP A, TB_DEPT B
WHERE A.DEPT_CD = B.DEPT_CD
START WITH A.DIRECT_MANAGER_EMP_NO IS NULL 
CONNECT BY 
PRIOR A.EMP_NO = A.DIRECT_MANAGER_EMP_NO
;

• 사원명이 '이경오'인 경우부터 시작
• PRIOR 자식 = 부모 순으로 순방향 전개
• CONNECT_BY_ISLEAF를 이용하여 LEAF 노드인 경우 1을 출력

SELECT LEVEL LVL
     , LPAD(' ', 4*(LEVEL-1))|| EMP_NO || '(' || EMP_NM || ')' AS "조직인원"
     , A.DEPT_CD
     , B.DEPT_NM
     , CONNECT_BY_ISLEAF
FROM TB_EMP A, TB_DEPT B
WHERE A.DEPT_CD = B.DEPT_CD
START WITH A.EMP_NM = '이경오'
CONNECT BY PRIOR A.EMP_NO = A.DIRECT_MANAGER_EMP_NO
;

CONNECT_BY_ROOT

• 관리자 사원번호가 널인 값을 첫 시작 값으로 지정 (관리자가 없는 사람이 첫 시작 값) == 최고 관리자를 START WITH으로 시작함
• PRIOR 자식 = 부모 순으로 순방향 전개
• CONNECT_BY_ISLEAF를 이용하여 LEAF 노드인 경우 1을 출력
• CONNECT_BY_ROOT를 이용하여 최상위 관리자를 출력함

SELECT LEVEL LVL
     , LPAD(' ', 4*(LEVEL-1))|| EMP_NO || '(' || EMP_NM || ')' AS "조직인원"
     , A.DEPT_CD
     , B.DEPT_NM
     , CONNECT_BY_ISLEAF
     , CONNECT_BY_ROOT A.EMP_NO AS "최상위관리자"
FROM TB_EMP A, TB_DEPT B
WHERE A.DEPT_CD = B.DEPT_CD
START WITH A.DIRECT_MANAGER_EMP_NO IS NULL 
CONNECT BY PRIOR A.EMP_NO = A.DIRECT_MANAGER_EMP_NO;

SYS_CONNECT_BY_PATH

• 사원명이 '이경오'인 경우 부터 시작
• PRIOR 자식 = 부모 순으로 순방향 전개
• CONNECT_BY_ISLEAF를 이용하여 LEAF 노드인 경우 1을 출력
• CONNECT_BY_ROOT를 이용하여 최상위 관리자를 출력함
• SYS_CONNECT_BY_PATH 함수를 이용하여 조직 인원 경로를 출력함

SELECT LEVEL LVL
     , LPAD(' ', 4*(LEVEL-1))|| EMP_NO || '(' || EMP_NM || ')' AS "조직인원"
     , A.DEPT_CD
     , B.DEPT_NM
     , CONNECT_BY_ISLEAF
     , CONNECT_BY_ROOT A.EMP_NO AS "최상위관리자"
     , SYS_CONNECT_BY_PATH(EMP_NO || '(' || EMP_NM || ')', '/') AS "조직인원경로"
FROM TB_EMP A, TB_DEPT B
WHERE A.DEPT_CD = B.DEPT_CD
START WITH A.EMP_NM = '이경오'
CONNECT BY PRIOR A.EMP_NO = A.DIRECT_MANAGER_EMP_NO;

SELF 조인의 활용

• SELF 조인은 동일한 테이블끼리의 조인을 의미한다. ( FROM TB_EMP A INNER JOIN TB_EMP B )
• SELF 조인을 이용해서 계층형의 데이터를 출력 할 수 있다.
• SELF 조인 시 INNER 조인을 하여 관리자가 존재하지 않는 김회장의 레코드는 결과 집합에 포함되지 않음.

SELECT A.EMP_NO "사원번호"
     , A.EMP_NM "사원번호"
     , A.DIRECT_MANAGER_EMP_NO "관리자사원번호"
     , (SELECT L.EMP_NM FROM TB_EMP L WHERE L.EMP_NO = A.DIRECT_MANAGER_EMP_NO) AS "관리자사원명" 
     , B.DIRECT_MANAGER_EMP_NO AS "차상위관리자사원번호"
     , (SELECT L.EMP_NM FROM TB_EMP L WHERE L.EMP_NO = B.DIRECT_MANAGER_EMP_NO) AS "차상위관리자사원명"
FROM TB_EMP A INNER JOIN TB_EMP B 
ON(A.DIRECT_MANAGER_EMP_NO = B.EMP_NO)
JOIN TB_DEPT C 
ON (A.DEPT_CD = C.DEPT_CD)
;

• LEFT OUTER JOIN을 통해서 김회장의 레코드를 결과 집합에 포함되게 출력할 수 있다.

SELECT A.EMP_NO "사원번호"
     , A.EMP_NM "사원번호"
     , A.DIRECT_MANAGER_EMP_NO "관리자사원번호"
     , (SELECT L.EMP_NM FROM TB_EMP L WHERE L.EMP_NO = A.DIRECT_MANAGER_EMP_NO) AS "관리자사원명" 
     , B.DIRECT_MANAGER_EMP_NO AS "차상위관리자사원번호"
     , (SELECT L.EMP_NM FROM TB_EMP L WHERE L.EMP_NO = B.DIRECT_MANAGER_EMP_NO) AS "차상위관리자사원명"
FROM TB_EMP A LEFT OUTER JOIN TB_EMP B 
ON(A.DIRECT_MANAGER_EMP_NO = B.EMP_NO)
JOIN TB_DEPT C 
ON (A.DEPT_CD = C.DEPT_CD)
;