[독학! SQLP] 계층 쿼리

계층형 쿼리?

• 정의: 트리(tree) 구조처럼 상위-하위 관계(부모-자식)를 가지는 데이터를 조회하는 SQL 기법
• 대표 문법 (Oracle)

SELECT ...
FROM table
START WITH 조건     -- 루트(최상위) 지정
CONNECT BY [NOCYCLE] PRIOR 부모 = 자식  -- 상위-하위 관계
ORDER SIBLINGS BY 컬럼  -- 형제 노드 간 정렬

예제 데이터 셋

-- EMP(사번, 이름, 상사사번, 부서)
CREATE TABLE EMP (
  EMPNO NUMBER PRIMARY KEY,
  ENAME VARCHAR2(20),
  MGR   NUMBER NULL,
  DEPT  VARCHAR2(10)
);

INSERT INTO EMP VALUES (100, 'KING' , NULL,   'HQ');     -- 루트
INSERT INTO EMP VALUES (110, 'JONES', 100 ,   'R&D');
INSERT INTO EMP VALUES (120, 'BLAKE', 100 ,   'SALES');
INSERT INTO EMP VALUES (130, 'CLARK', 100 ,   'FIN');
INSERT INTO EMP VALUES (201, 'ALLEN', 120 ,   'SALES');
INSERT INTO EMP VALUES (202, 'WARD' , 120 ,   'SALES');
INSERT INTO EMP VALUES (211, 'SMITH', 110 ,   'R&D');
INSERT INTO EMP VALUES (212, 'FORD' , 110 ,   'R&D');
INSERT INTO EMP VALUES (301, 'MILLER',130 ,   'FIN');
COMMIT;

주요 키워드로 알아보는 계층형 쿼리

START WITH

• 계층형 쿼리의 시작점(루트 노드) 지정.
• 조건을 만족하는 행부터 탐색을 시작함.
• 지정하지 않으면 전체 테이블에서 탐색 시도 → 불필요한 결과나 에러 가능.
  • 모든 노드가 루트처럼 취급되어 다중 트리 발생 → 의도치 않은 중복.
• 예시

-- 최고 관리자(KING)에서 시작
SELECT LEVEL, EMPNO, ENAME, MGR
FROM   EMP
START WITH MGR IS NULL        -- 루트 조건
CONNECT BY PRIOR EMPNO = MGR; -- 부모 → 자식

-- JONES(110)을 루트로 시작
SELECT LEVEL, EMPNO, ENAME, MGR
FROM   EMP
START WITH EMPNO = 110
CONNECT BY PRIOR EMPNO = MGR;

-- JONES(110), BLAKE(120) 둘 다 루트로 시작
SELECT LEVEL, EMPNO, ENAME, MGR
FROM   EMP
START WITH EMPNO IN (110, 120)
CONNECT BY PRIOR EMPNO = MGR;

-- 루트 없이 실행할 경우.
SELECT LEVEL, EMPNO, ENAME, MGR
FROM   EMP
CONNECT BY PRIOR EMPNO = MGR;

CONNECT BY

• 상/하위 관계(재귀적 탐색)를 정의하는 구문.
• PRIOR 키워드와 함꼐 부모 - 자식 관계를 지정해야 함.
• 루트(START WITH)에서 시작해 CONNECT BY조건을 만족하는 행들을 계속 탐색
• 예시

-- 부모(empno) → 자식(mgr)
SELECT LEVEL, EMPNO, ENAME, MGR
FROM   EMP
START WITH MGR IS NULL                -- KING에서 시작
CONNECT BY PRIOR EMPNO = MGR;         -- 부모 → 자식 관계


-- CONNECT BY 안에 추가 조건을 넣어 특정 경로만 탐색
-- R&D 부서만 추적
SELECT LEVEL, EMPNO, ENAME, DEPT
FROM   EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
       AND DEPT = 'R&D';

→ CONNECT BY PRIOR EMPNO = MGR 조건이 “부모(empno) → 자식(mgr)” 관계를 만든 것.

PRIOR

• CONNECT BY에서 부모와 자식중 어느쪽을 기준으로 연결할지 지정
• PRIOR가 붙은 쪽이 부모!
• 왼쪽 PRIOR = 부모 → 자식
• 오른쪽 PRIOR = 자식 → 부모
• PRIOR를 양쪽에 둘 다 쓰는 건 불가. 반드시 한쪽에만!
• 예시

•  

-- 부모 → 자식 (하향식 탐색)
SELECT LEVEL, EMPNO, ENAME, MGR
FROM   EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;   -- 부모(empno) → 자식(mgr)

-- 자식 → 부모 (상향식 탐색)
SELECT LEVEL, EMPNO, ENAME, MGR
FROM   EMP
START WITH EMPNO = 201          -- ALLEN(부하)에서 시작
CONNECT BY EMPNO = PRIOR MGR;   -- 자식(empno) → 부모(mgr

LEVEL 

• 계층 깊이 반환 (루트는 1, 자식은 2, 손자는 3 ...)
• 루트(START WITH 지정)는 항상 1부터 시작.
• 예시

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

-- 들여쓰기
SELECT LPAD(' ', (LEVEL-1)*2) || ENAME AS TREE, EMPNO, MGR
FROM   EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ENAME;

-- 특정 깊이만 조회
-- 2단계(부하 직원)까지만 조회
SELECT LEVEL, ENAME
FROM   EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
AND LEVEL <= 2;

-- 조건식에 확용
-- 말단 직원만 출력 (LEVEL 이용)
SELECT LEVEL, ENAME
FROM   EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
HAVING LEVEL = MAX(LEVEL);  -- 집계랑 같이 쓰는 유형도 출제

ORDER SIBLINGS BY

• 계층형 쿼리에서 같은 부모(=형제 노드) 아래에 있는 행들만 정렬.
• 일반 ORDER BY를 쓰면 계층 구조가 깨짐 → 시험 함정 포인트.
• 따라서 트리 구조 유지 + 형제 간 정렬이 필요할 때 반드시 ORDER SIBLINGS BY 사용.
• 예시

SELECT LPAD(' ', (LEVEL-1)*2) || ENAME AS TREE,
       EMPNO, MGR
FROM   EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ENAME;

-- 일반 order by 와 ORDER SIBLINGS BY 비교

-- 일반 order by
-- 결과: 전체 데이터가 이름 알파벳순으로 섞여서 계층 구조 붕괴.
SELECT LEVEL, ENAME, MGR
FROM   EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER BY ENAME;    -- 일반 ORDER BY

-- ORDER SIBLINGS BY
-- 결과: 계층 유지 + 형제 간 정렬만 적용.
SELECT LEVEL, ENAME, MGR
FROM   EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ENAME;

NOCYCLE & CONNECT_BY_ISCYCLE

• 순환(Cycle) : 계층 구조에서 부모-자식 참조가 자기 자신에게 다시 돌아오는 경우. → 무한 루프 발생 위험.
• NOCYCLE : 순환 발견 시 해당 경로를 끊고 탐색 중단.
• CONNECT_BY_ISCYCLE : 현재 행이 순환에 포함되었는지 여부 반환 (1=순환, 0=아님).
• 예시

-- 순환 포함 예시 데이터

-- WARDCYCLE(333)이 BLAKE(120)의 상사,
-- BLAKE(120)가 다시 WARDCYCLE(333)의 상사 → 순환 발생
UPDATE EMP SET MGR = 333 WHERE EMPNO = 120;  -- BLAKE → WARDCYCLE
-- 이미 EMP(333, 'WARDCYCLE', 120, 'SALES') 있음 (WARDCYCLE → BLAKE)
COMMIT;

-- 순환 방지 옵션(NOCYCLE) 없이 실행
-- 결과 : ORA-01436 오류 발생 (“CONNECT BY loop in user data” → 순환 구조 탐지됨)
SELECT ENAME, LEVEL
FROM   EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

-- NOCYCLE 옵션 사용
-- 결과: 순환되는 경로(BLAKE ↔ WARDCYCLE)에서 경로가 끊기고 탐색 중단, 나머지 정상 트리는 계속 조회됨.
SELECT ENAME, LEVEL, SYS_CONNECT_BY_PATH(ENAME, '->') AS PATH
FROM   EMP
START WITH MGR IS NULL
CONNECT BY NOCYCLE PRIOR EMPNO = MGR;

-- CONNECT_BY_ISCYCLE 옵션 사용 (순환에 걸린 행 식별용)
-- WARDCYCLE 행에서 IS_CYCLE=1로 표시
SELECT ENAME,
       CONNECT_BY_ISCYCLE AS IS_CYCLE,
       SYS_CONNECT_BY_PATH(ENAME, '->') AS PATH
FROM   EMP
START WITH MGR IS NULL
CONNECT BY NOCYCLE PRIOR EMPNO = MGR;

CONNECT_BY_ISLEAF

• 계층형 쿼리에서 현재 행이 자식(하위 노드)을 가지고 있는지 여부를 반환.
• 1 = 리프(leaf, 말단 노드) → 더 이상 하위 없음
• 0 = 내부 노드 → 하위 노드 존재
• 예시

SELECT ENAME,
       CONNECT_BY_ISLEAF AS IS_LEAF,
       LEVEL
FROM   EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ENAME;

-- 말단 직원만 표시
SELECT ENAME, LEVEL
FROM   EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
WHERE CONNECT_BY_ISLEAF = 1;

-- 트리경로 + 말단 표시
SELECT ENAME,
       SYS_CONNECT_BY_PATH(ENAME, '->') AS PATH,
       CONNECT_BY_ISLEAF AS IS_LEAF
FROM   EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

SYS_CONNECT_BY_PATH

• 계층형 쿼리에서 루트 → 현재 노드까지의 경로를 문자열로 반환.
• 구분자를 지정할 수 있음.
• 보통 ->, /, : 같은 기호를 많이 씀.
• 예시

SELECT ENAME,
       SYS_CONNECT_BY_PATH(ENAME, '->') AS PATH,
       LEVEL
FROM   EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY ENAME;

-- 리프노드 최종 경로만 출력
SELECT SYS_CONNECT_BY_PATH(ENAME, '->') AS FULL_PATH
FROM   EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
WHERE CONNECT_BY_ISLEAF = 1;

-- 경로에서 루트 제거
SELECT ENAME,
       SUBSTR(SYS_CONNECT_BY_PATH(ENAME, '/'), 2) AS PATH
FROM   EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;

-- 조건 필터와 조합
-- 특정 사원까지의 경로 조회
SELECT SYS_CONNECT_BY_PATH(ENAME, '->') AS PATH
FROM   EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
AND ENAME = 'SMITH';

계층형 쿼리(Oracle) vs 재귀 CTE(ANSI)

• START WITH ↔ 앵커 SELECT
• CONNECT BY PRIOR ↔ 재귀 JOIN
• LEVEL ↔ 누적 +1 컬럼
• 예시

-- ANSI (예: PostgreSQL/MariaDB 10.2+)
WITH RECURSIVE EMP_HIER AS (
  -- 앵커: 루트
  SELECT EMPNO, ENAME, MGR, 1 AS LVL, CAST(ENAME AS VARCHAR(4000)) AS PATH
  FROM   EMP
  WHERE  MGR IS NULL
  UNION ALL
  -- 재귀: 자식 확장
  SELECT E.EMPNO, E.ENAME, E.MGR, H.LVL + 1,
         CONCAT(H.PATH, '->', E.ENAME)
  FROM   EMP E
  JOIN   EMP_HIER H ON E.MGR = H.EMPNO
  -- (순환 방지는 보통 방문체크용 키 누적 후 NOT LIKE/NOT IN 등으로 구현)
)
SELECT * FROM EMP_HIER
ORDER BY PATH;