계층형 질의와 셀프 조인

한 테이블 안에서 행끼리 상하 관계를 갖는 데이터를 계층형 데이터라고 한다. 이런 데이터는 Oracle의 계층형 질의(START WITH, CONNECT BY), SQL Server의 재귀 CTE, 또는 셀프 조인으로 조회한다.

계층형 데이터와 예제

사원 테이블에서 MGR 컬럼이 같은 테이블의 EMPNO를 참조하면 순환 관계(재귀 관계)가 된다. EMPNO를 자식 쪽 키, MGR을 부모 쪽 키로 보면 된다.

EMP 테이블

조직도로 그리면 다음과 같다. MGR이 NULL인 한대표가 루트(LEVEL 1)다.

flowchart TD
  A["한대표 (100)<br/>LEVEL 1"] --> B["김부장 (110)<br/>LEVEL 2"]
  A --> C["이부장 (120)<br/>LEVEL 2"]
  B --> D["박과장 (111)<br/>LEVEL 3"]
  B --> E["최과장 (112)<br/>LEVEL 3"]
  C --> F["정대리 (121)<br/>LEVEL 3"]
  classDef hl fill:#fef3c7,stroke:#f59e0b,color:#451a03
  class D,E,F hl

강조된 박과장·최과장·정대리는 자식이 없는 리프(Leaf) 행이다.

한대표
├─ 김부장
│   ├─ 박과장
│   └─ 최과장
└─ 이부장
    └─ 정대리

Oracle 계층형 질의 문법

SELECT ...
FROM   테이블
[WHERE 조건]
START  WITH 루트 조건
CONNECT BY [NOCYCLE] PRIOR 자식컬럼 = 부모컬럼
[ORDER SIBLINGS BY 컬럼];

가상 컬럼과 함수

순방향 전개 (부모에서 자식으로)

SELECT LEVEL,
       LPAD(' ', 2 * (LEVEL - 1)) || ENAME     AS ENAME,
       CONNECT_BY_ISLEAF                       AS ISLEAF,
       CONNECT_BY_ROOT ENAME                   AS ROOT,
       SYS_CONNECT_BY_PATH(ENAME, '/')         AS PATH
FROM   EMP
START  WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER  SIBLINGS BY ENAME;

• PRIOR EMPNO = MGR은 "직전에 전개된 행의 EMPNO와 MGR이 같은 행을 다음으로 찾는다"는 뜻이다. 즉 직전 행의 부하 직원을 찾으므로 위에서 아래로 내려간다.
• PRIOR 자식컬럼 = 부모컬럼 형태가 순방향이다.
• 결과는 깊이 우선 순서로 출력된다. 루트 하나를 내려간 뒤 그 하위를 모두 출력하고 다음 형제로 넘어간다.

시험 포인트: 계층형 질의에서 일반 ORDER BY를 쓰면 계층 순서가 깨진다. 형제 사이에서만 정렬하려면 ORDER SIBLINGS BY를 쓴다.

역방향 전개 (자식에서 부모로)

SELECT LEVEL, ENAME, MGR
FROM   EMP
START  WITH ENAME = '박과장'
CONNECT BY PRIOR MGR = EMPNO;

PRIOR MGR = EMPNO는 "직전 행의 MGR 값을 EMPNO로 가진 행", 즉 상사를 찾는다. PRIOR 부모컬럼 = 자식컬럼 형태는 역방향이다. 역방향에서는 시작한 행이 LEVEL 1이 되고 위로 올라갈수록 LEVEL이 커진다.

시험 포인트: 방향은 등호 좌우가 아니라 PRIOR가 어느 컬럼에 붙었는지로 판단한다. PRIOR가 자식 쪽 키(EMPNO)에 붙으면 순방향, 부모 쪽 키(MGR)에 붙으면 역방향이다.

WHERE 조건과 CONNECT BY 조건의 차이

WHERE는 맨 마지막에 적용된다. 그래서 같은 조건이라도 어디에 두느냐에 따라 결과가 달라진다.

WHERE 절: 전개 후 해당 행만 제거

SELECT LEVEL, ENAME
FROM   EMP
WHERE  ENAME <> '김부장'
START  WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER  SIBLINGS BY ENAME;

김부장 행만 빠지고 그 하위인 박과장, 최과장은 그대로 남는다. LEVEL도 3 그대로다.

CONNECT BY 절: 해당 행과 하위 전체가 제외

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

김부장으로 전개되지 않으므로 그 아래 가지 전체가 사라진다.

시험 포인트: WHERE 조건은 전개가 끝난 뒤 행 단위로 거르고, CONNECT BY 조건은 전개 자체를 막아 하위 가지까지 제외한다.

SQL Server 재귀 CTE

SQL Server는 CONNECT BY가 없으므로 WITH 절의 재귀 공통 테이블 식(CTE)으로 계층을 전개한다.

WITH EMP_CTE (EMPNO, ENAME, MGR, LVL) AS (
    -- 앵커 멤버: 루트
    SELECT EMPNO, ENAME, MGR, 1
    FROM   EMP
    WHERE  MGR IS NULL
    UNION ALL
    -- 재귀 멤버: 직전 결과의 부하 직원
    SELECT E.EMPNO, E.ENAME, E.MGR, C.LVL + 1
    FROM   EMP E
           INNER JOIN EMP_CTE C ON E.MGR = C.EMPNO
)
SELECT EMPNO, ENAME, MGR, LVL
FROM   EMP_CTE;

결과 순서가 깊이 우선으로 보장되지 않으므로 경로 컬럼을 만들어 ORDER BY 하는 방식을 쓴다.

셀프 조인으로 상위·하위 조회

셀프 조인(Self Join)은 같은 테이블을 별칭을 달리해 두 번 이상 조인하는 방식이다. 한 단계 위·아래처럼 깊이가 정해진 조회에 적합하다.

-- 사원과 직속 상사
SELECT E.ENAME AS 사원, M.ENAME AS 상사
FROM   EMP E
       LEFT OUTER JOIN EMP M ON E.MGR = M.EMPNO;

INNER JOIN으로 쓰면 상사가 없는 한대표가 빠져 5행이 된다.

-- 사원, 상사, 상사의 상사
SELECT E.ENAME AS 사원, M.ENAME AS 상사, T.ENAME AS 차상위
FROM   EMP E
       JOIN EMP M ON E.MGR = M.EMPNO
       JOIN EMP T ON M.MGR = T.EMPNO;
-- 박과장-김부장-한대표, 최과장-김부장-한대표, 정대리-이부장-한대표

• 조인 조건 E.MGR = M.EMPNO에서 M은 상위(관리자) 역할이다.
• 깊이가 정해지지 않은 전체 계층은 셀프 조인보다 계층형 질의가 적합하다. 조인 일반은 조인, 표준 조인을 참고한다.

핵심 요약

• START WITH는 루트, CONNECT BY는 전개 조건, PRIOR는 직전 행을 가리킨다.
• PRIOR 자식 = 부모(PRIOR EMPNO = MGR)는 순방향, PRIOR 부모 = 자식(PRIOR MGR = EMPNO)은 역방향이다.
• LEVEL은 루트가 1이며, 리프 여부는 CONNECT_BY_ISLEAF, 루트 값은 CONNECT_BY_ROOT로 확인한다.
• SYS_CONNECT_BY_PATH는 루트부터 현재까지 경로를 문자열로 만든다.
• 계층 순서를 유지하며 정렬하려면 ORDER SIBLINGS BY를 쓴다.
• WHERE 조건은 전개 후 해당 행만, CONNECT BY 조건은 하위 가지까지 제외한다.
• SQL Server는 앵커와 재귀 멤버를 UNION ALL로 연결한 재귀 CTE를 사용한다.
• 셀프 조인은 같은 테이블에 별칭을 달리해 상위·하위를 조회한다.