계층형 질의와 셀프 조인

셀프 조인(self join)은 같은 테이블을 두 번 써서 한 테이블 안의 행끼리 잇습니다. 계층형 질의(hierarchical query)는 그 이음을 끝까지 따라가며 행을 트리 순서로 꺼냅니다.

EMP의 MGR은 관리자의 사번이라 같은 EMP의 EMPNO를 가리킵니다. "사원 옆에 관리자 이름"은 EMP를 두 번 써서 한 번 이으면 되지만, "김민준 아래의 모든 사람을 조직도 순서로"는 몇 단계 아래까지 있는지 미리 알 수 없습니다. 조인은 한 번에 한 단계씩만 이어서, 이을 행이 없을 때까지 따라 내려가는 일은 계층형 질의가 맡습니다.

…FROM EMP E  LEFT OUTER JOIN EMP M  ON E.MGR = M.EMPNO…

SELECT LEVEL,…START WITH MGR IS NULLCONNECT BY PRIOR EMPNO = MGRORDER SIBLINGS BY EMPNO;

SELECT LEVEL,…START WITH EMPNO = 1005CONNECT BY PRIOR MGR = EMPNO;

…FROM EMP E  JOIN EMP M  ON E.MGR = M.EMPNO…

1셀프 조인

EMP에 E(사원)와 M(관리자) 두 별칭을 붙여, 사원의 MGR과 관리자의 EMPNO가 같으면 잇습니다. 별칭 없이 SELECT ENAME FROM EMP, EMP⁠처럼 쓰면 어느 EMP의 ENAME인지 가릴 수 없어 ORA-00918 오류입니다. 김민준은 MGR이 NULL이라 짝이 없어서 내부 조인이면 5행만 남고, 김민준까지 보려면 LEFT OUTER JOIN을 씁니다.

2START WITH·CONNECT BY와 LEVEL

START WITH는 트리의 맨 위 행인 뿌리(root)를 고릅니다. 생략하면 모든 행이 저마다 뿌리가 되어 EMP에서는 14행이 나옵니다. CONNECT BY는 앞 단계 행과 다음 단계 행을 잇는 조건이고, PRIOR는 바로 뒤의 열을 앞 단계에서 찾은 행의 값으로 읽습니다. LEVEL은 뿌리에서 몇 번째 단계인지 알려 주는 가상 열이고, 뿌리가 1입니다. 결과는 LEVEL별로 모이지 않습니다. 김민준 → 최유나 → 정하늘로 한 줄기를 다 훑고 강도윤 쪽으로 넘어가는 깊이 우선(depth-first) 순서입니다. 형제끼리만 정렬하려면 ORDER BY 대신 ORDER SIBLINGS BY를 씁니다. SQL Server는 START WITH·CONNECT BY 대신, 자기 자신을 부르는 재귀 공통 테이블 식(recursive CTE)으로 계층을 따라갑니다.

3방향 바꾸기와 거르기

한 행에서 EMPNO는 그 사원 자신(자식 쪽), MGR은 상사(부모 쪽)입니다. PRIOR가 자식 쪽 열에 붙은 PRIOR EMPNO = MGR은 아래로 내려가는 순방향, 부모 쪽 열에 붙은 PRIOR MGR = EMPNO는 위로 올라가는 역방향입니다. 등호 양쪽을 바꿔 써도 방향은 그대로이고, 역방향에서는 시작한 행의 LEVEL이 1입니다.

Oracle은 조인, CONNECT BY, 남은 WHERE 조건의 차례로 처리해서, 같은 조건 EMPNO <> 1006도 놓는 자리에 따라 빠지는 행이 다릅니다.

MGR을 따라가다 자기에게 돌아오는 순환이 있으면 ORA-01436 오류이고, CONNECT BY NOCYCLE은 순환 직전에서 멈춥니다. 이때 가상 열 CONNECT_BY_ISCYCLE이 순환 직전 행에서 1이 됩니다.

예제

계층형 질의 전용 가상 열과 함수입니다. CONNECT_BY_ISLEAF는 아래로 이을 행이 없는 잎(leaf)이면 1, CONNECT_BY_ROOT는 뿌리 행의 값, SYS_CONNECT_BY_PATH는 뿌리부터의 경로입니다.

SELECT LEVEL,
  ENAME,
  CONNECT_BY_ISLEAF AS LEAF,
  CONNECT_BY_ROOT ENAME
    AS ROOT_NAME,
  SYS_CONNECT_BY_PATH(
    ENAME, '/') AS PATH
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY EMPNO;

시험 포인트

• 셀프 조인에는 별칭이 둘 있어야 하고, 맨 위 사원까지 남기려면 LEFT OUTER JOIN입니다.
• START WITH를 생략하면 모든 행이 뿌리가 됩니다. LEVEL은 고른 뿌리가 1입니다.
• 방향은 PRIOR가 붙은 열이 정합니다. PRIOR EMPNO = MGR은 순방향, PRIOR MGR = EMPNO는 역방향입니다.
• 결과는 깊이 우선 순서이고, 형제끼리만 정렬할 때는 ORDER SIBLINGS BY를 씁니다.
• 같은 조건도 WHERE에 두면 한 행만, CONNECT BY에 두면 가지째 빠집니다.
• CONNECT_BY_ISLEAF·CONNECT_BY_ISCYCLE(가상 열)과 CONNECT_BY_ROOT·SYS_CONNECT_BY_PATH는 계층형 질의 전용입니다.

확인 문제

1. 다음 쿼리의 결과를 적어 보세요.

SELECT LEVEL,
  ENAME,
  SYS_CONNECT_BY_PATH(
    ENAME, '/') AS PATH
FROM EMP
START WITH EMPNO = 1003
CONNECT BY PRIOR MGR = EMPNO;

2. 강도윤과 그 아래 사람만 보려고 합니다. 다음 쿼리의 결과는 몇 행이고, 강도윤의 LEVEL은 얼마일까요?

SELECT LEVEL,
  EMPNO,
  ENAME
FROM EMP
START WITH EMPNO = 1006
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY EMPNO;

3. 다음 쿼리는 몇 행을 낼까요? 같은 조건 EMPNO <> 1004를 WHERE에 두면 몇 행이고, 정하늘은 어느 쪽에 남을까요?

SELECT LEVEL,
  ENAME
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
  AND EMPNO <> 1004
ORDER SIBLINGS BY EMPNO;