SQL 계층형 질의
IT 위키
더 많은 작업
- 상위 문서: SQL
- Hierarchical Query
- 한 테이블 안에서 부모-자식 관계로 이어진 행들을 트리 구조로 펼쳐 조회하는 질의
조직도(사원-관리자), 메뉴, 부품 구성표처럼 한 테이블의 칼럼이 같은 테이블의 다른 행을 가리키는 데이터를 순환 관계(재귀 관계) 데이터라고 한다. 계층형 질의는 이런 데이터를 루트부터 차례로 펼쳐 계층 순서대로 보여 준다. Oracle은 전용 문법인 START WITH … CONNECT BY를 제공하고, SQL Server와 SQL 표준은 재귀 공통 테이블 식(재귀 CTE, WITH … UNION ALL)을 쓴다. Oracle도 11gR2부터 재귀 WITH를 지원한다. 한 단계 위의 관리자만 필요하면 셀프 조인으로도 충분하지만, 단계 수가 정해져 있지 않은 전체 계층은 계층형 질의로 조회한다.
사원
| 사원번호 | 이름 | 관리자번호 |
|---|---|---|
| 1 | 김사장 | NULL |
| 2 | 이부장 | 1 |
| 3 | 박부장 | 1 |
| 4 | 최과장 | 2 |
| 5 | 정과장 | 2 |
| 6 | 강대리 | 4 |
| 7 | 조대리 | 5 |
| 8 | 윤과장 | 3 |
관리자번호는 같은 테이블의 사원번호를 참조한다. 김사장(1) 아래에 이부장·박부장, 이부장 아래에 최과장·정과장, 박부장 아래에 윤과장이 있고, 최과장 아래 강대리, 정과장 아래 조대리가 있는 4단계 구조다.
SELECT ...
FROM 테이블
[WHERE 조건] -- 계층 전개가 끝난 뒤 행을 거른다
START WITH 루트 조건 -- 시작(루트) 행
CONNECT BY [NOCYCLE] PRIOR 자식칼럼 = 부모칼럼 -- 부모-자식 연결 조건
[ORDER SIBLINGS BY 칼럼]; -- 같은 부모를 둔 형제끼리만 정렬
| 요소 | 설명 |
|---|---|
| START WITH | 계층의 시작점이 되는 루트 행을 지정한다. 생략하면 모든 행이 각각 루트가 된다. |
| CONNECT BY | 다음 단계 행을 찾는 조건이다. |
| PRIOR | 바로 앞 단계(이미 찾은 행)의 칼럼 값을 가리킨다. |
| NOCYCLE | 순환이 있어도 오류를 내지 않고 순환이 시작되는 지점에서 전개를 멈춘다. |
| ORDER SIBLINGS BY | 계층 구조를 유지한 채 형제 노드 사이의 순서만 정한다. |
| 의사 칼럼·함수 | 의미 |
|---|---|
| LEVEL | 루트가 1, 그 자식이 2, … 인 깊이 |
| CONNECT_BY_ISLEAF | 자식이 없는 리프 행이면 1, 아니면 0 |
| CONNECT_BY_ISCYCLE | NOCYCLE과 함께 쓰며, 자식 중에 조상 행이 있어 순환이 생기는 행이면 1 |
| CONNECT_BY_ROOT 칼럼 | 현재 행이 속한 계층의 루트 행의 칼럼 값 |
| SYS_CONNECT_BY_PATH(칼럼, 구분자) | 루트부터 현재 행까지의 칼럼 값을 구분자로 이은 경로 |
PRIOR 사원번호 = 관리자번호는 "앞 단계 행의 사원번호를 관리자번호로 가진 행"을 찾으라는 뜻이다. 즉 PRIOR를 자식 칼럼(사원번호) 쪽에 붙여 PRIOR 자식 = 부모로 쓰면 부모에서 자식 방향(top-down)으로 내려간다.
SELECT LEVEL, LPAD(' ', 2 * (LEVEL - 1)) || 이름 AS 조직도,
사원번호, 관리자번호, CONNECT_BY_ISLEAF AS 리프
FROM 사원
START WITH 관리자번호 IS NULL
CONNECT BY PRIOR 사원번호 = 관리자번호;
| LEVEL | 조직도 | 사원번호 | 관리자번호 | 리프 |
|---|---|---|---|---|
| 1 | 김사장 | 1 | NULL | 0 |
| 2 | 이부장 | 2 | 1 | 0 |
| 3 | 최과장 | 4 | 2 | 0 |
| 4 | 강대리 | 6 | 4 | 1 |
| 3 | 정과장 | 5 | 2 | 0 |
| 4 | 조대리 | 7 | 5 | 1 |
| 2 | 박부장 | 3 | 1 | 0 |
| 3 | 윤과장 | 8 | 3 | 1 |
- 결과는 깊이 우선 순서로 나온다. 한 부모의 자식을 모두 펼친 뒤 다음 형제로 넘어간다.
LPAD(' ', 2 * (LEVEL - 1))는 LEVEL이 1 늘 때마다 공백 2칸을 앞에 붙여 들여쓰기한다. LEVEL 1이면 길이 0이 되어 NULL이고, NULL과 이름을||로 이으면 이름만 남는다.- 리프(CONNECT_BY_ISLEAF = 1)는 강대리, 조대리, 윤과장 3명이다.
- START WITH를 빼면 8명이 모두 각자 루트가 되어 하위 계층을 펼치므로 결과가 22행으로 늘어난다(각 사원의 LEVEL 합계와 같다).
SELECT LEVEL, 이름,
CONNECT_BY_ROOT 이름 AS 루트,
SYS_CONNECT_BY_PATH(이름, '/') AS 경로
FROM 사원
START WITH 관리자번호 IS NULL
CONNECT BY PRIOR 사원번호 = 관리자번호;
| LEVEL | 이름 | 루트 | 경로 |
|---|---|---|---|
| 1 | 김사장 | 김사장 | /김사장 |
| 2 | 이부장 | 김사장 | /김사장/이부장 |
| 3 | 최과장 | 김사장 | /김사장/이부장/최과장 |
| 4 | 강대리 | 김사장 | /김사장/이부장/최과장/강대리 |
| 3 | 정과장 | 김사장 | /김사장/이부장/정과장 |
| 4 | 조대리 | 김사장 | /김사장/이부장/정과장/조대리 |
| 2 | 박부장 | 김사장 | /김사장/박부장 |
| 3 | 윤과장 | 김사장 | /김사장/박부장/윤과장 |
PRIOR를 부모 칼럼 쪽에 붙여 PRIOR 관리자번호 = 사원번호(같은 뜻으로 사원번호 = PRIOR 관리자번호)로 쓰면 "앞 단계 행의 관리자번호를 사원번호로 가진 행", 즉 상사를 찾아 위로 올라간다.
SELECT LEVEL, 이름, 사원번호, 관리자번호
FROM 사원
START WITH 이름 = '강대리'
CONNECT BY 사원번호 = PRIOR 관리자번호;
| LEVEL | 이름 | 사원번호 | 관리자번호 |
|---|---|---|---|
| 1 | 강대리 | 6 | 4 |
| 2 | 최과장 | 4 | 2 |
| 3 | 이부장 | 2 | 1 |
| 4 | 김사장 | 1 | NULL |
- 역방향에서도 LEVEL은 시작 행이 1이다. 조직상 가장 아래인 강대리가 LEVEL 1이 된다.
- PRIOR의 위치만 보면 된다.
PRIOR 자식 = 부모는 순방향,PRIOR 부모 = 자식은 역방향이다. 등호 좌우를 바꿔 써도 PRIOR가 붙은 칼럼이 같으면 같은 방향이다.
같은 조건을 어디에 쓰느냐에 따라 결과가 크게 다르다.
-- (1) WHERE: 계층을 모두 전개한 뒤 이부장 한 행만 뺀다
SELECT LEVEL, 이름 FROM 사원
WHERE 이름 <> '이부장'
START WITH 관리자번호 IS NULL
CONNECT BY PRIOR 사원번호 = 관리자번호;
-- (2) CONNECT BY: 이부장으로 내려가는 연결 자체를 막는다
SELECT LEVEL, 이름 FROM 사원
START WITH 관리자번호 IS NULL
CONNECT BY PRIOR 사원번호 = 관리자번호 AND 이름 <> '이부장';
| (1) LEVEL | (1) 이름 | (2) LEVEL | (2) 이름 |
|---|---|---|---|
| 1 | 김사장 | 1 | 김사장 |
| 3 | 최과장 | 2 | 박부장 |
| 4 | 강대리 | 3 | 윤과장 |
| 3 | 정과장 | ||
| 4 | 조대리 | ||
| 2 | 박부장 | ||
| 3 | 윤과장 |
- (1)은 7행이다. 이부장 행만 빠지고 그 부하(최과장, 강대리, 정과장, 조대리)는 원래 LEVEL 그대로 남는다.
- (2)는 3행이다. 이부장이 연결되지 않으므로 그 아래 가지 전체가 함께 사라진다.
- 처리 순서는 START WITH로 루트를 정하고 CONNECT BY로 전개한 다음, 마지막에 WHERE로 거르는 순서다. 단, 조인이 있으면 조인 조건은 CONNECT BY보다 먼저 처리된다.
LEVEL <= 2처럼 깊이 제한을 CONNECT BY에 넣으면 그 아래로는 아예 전개하지 않는다. 이 예제에서는 WHERE에 넣어도 같은 3행(김사장, 이부장, 박부장)이 나오지만, 큰 테이블에서는 CONNECT BY 쪽이 불필요한 전개를 하지 않는다.
SELECT LEVEL, LPAD(' ', 2 * (LEVEL - 1)) || 이름 AS 조직도
FROM 사원
START WITH 관리자번호 IS NULL
CONNECT BY PRIOR 사원번호 = 관리자번호
ORDER SIBLINGS BY 이름;
LEVEL 조직도 1 김사장 2 박부장 3 윤과장 2 이부장 3 정과장 4 조대리 3 최과장 4 강대리
- 같은 부모 아래의 형제끼리만 이름순으로 바뀌었다(박부장이 이부장보다, 정과장이 최과장보다 앞). 부모-자식 순서는 유지된다.
- 일반
ORDER BY 이름을 쓰면 계층 순서가 무시되고 전체 행이 이름순으로 섞인다. - ORDER SIBLINGS BY가 없으면 형제 사이의 순서는 보장되지 않는다.
김사장의 관리자번호를 6(강대리)으로 바꾸면 김사장 → 이부장 → 최과장 → 강대리 → 김사장으로 고리가 생긴다. 이 상태로 START WITH 사원번호 = 1부터 전개하면 ORA-01436(CONNECT BY loop in user data) 오류가 난다. NOCYCLE을 쓰면 오류 없이 전개하고, CONNECT_BY_ISCYCLE로 순환 지점을 확인할 수 있다.
SELECT LEVEL, 이름, 관리자번호, CONNECT_BY_ISCYCLE AS 순환
FROM 사원
START WITH 사원번호 = 1
CONNECT BY NOCYCLE PRIOR 사원번호 = 관리자번호;
| LEVEL | 이름 | 관리자번호 | 순환 |
|---|---|---|---|
| 1 | 김사장 | 6 | 0 |
| 2 | 이부장 | 1 | 0 |
| 3 | 최과장 | 2 | 0 |
| 4 | 강대리 | 4 | 1 |
| 3 | 정과장 | 2 | 0 |
| 4 | 조대리 | 5 | 0 |
| 2 | 박부장 | 1 | 0 |
| 3 | 윤과장 | 3 | 0 |
- 강대리의 자식이 될 김사장이 이미 조상이므로 강대리에서 순환 표시가 1이 되고, 김사장은 다시 펼쳐지지 않는다.
- CONNECT_BY_ISCYCLE은 NOCYCLE 없이 쓰면 ORA-30930 오류가 난다.
SQL 표준과 SQL Server는 CONNECT BY가 없고 재귀 CTE를 쓴다. 앵커(루트) 쿼리와 재귀 쿼리를 UNION ALL로 잇고, 재귀 쿼리는 CTE 자신을 조인해 다음 단계를 찾는다. Oracle에서도 그대로 실행된다.
WITH 조직 (사원번호, 이름, 관리자번호, 레벨) AS (
SELECT 사원번호, 이름, 관리자번호, 1 -- 앵커: 루트
FROM 사원
WHERE 관리자번호 IS NULL
UNION ALL
SELECT e.사원번호, e.이름, e.관리자번호, o.레벨 + 1 -- 재귀: 앞 단계의 부하
FROM 사원 e
JOIN 조직 o ON e.관리자번호 = o.사원번호
)
SELECT 사원번호, 이름, 관리자번호, 레벨
FROM 조직
ORDER BY 레벨, 사원번호;
| 사원번호 | 이름 | 관리자번호 | 레벨 |
|---|---|---|---|
| 1 | 김사장 | NULL | 1 |
| 2 | 이부장 | 1 | 2 |
| 3 | 박부장 | 1 | 2 |
| 4 | 최과장 | 2 | 3 |
| 5 | 정과장 | 2 | 3 |
| 8 | 윤과장 | 3 | 3 |
| 6 | 강대리 | 4 | 4 |
| 7 | 조대리 | 5 | 4 |
- LEVEL 같은 의사 칼럼이 없으므로 레벨 칼럼을 직접 만들어 1씩 더한다.
- 재귀 CTE는 단계별로 행을 만들기 때문에 CONNECT BY처럼 깊이 우선 순서로 보려면 정렬 기준을 따로 만들어야 한다. Oracle은
SEARCH DEPTH FIRST BY 사원번호 SET 순서절로 CONNECT BY와 같은 순서 번호를 만들 수 있다. - Oracle은 재귀 WITH에 칼럼 목록(
조직 (사원번호, …))을 반드시 적어야 한다. PostgreSQL과 MySQL 8.0은WITH RECURSIVE라고 써야 하고, SQL Server는 RECURSIVE 없이 WITH만 쓴다.
| 구분 | Oracle CONNECT BY | 재귀 CTE (SQL Server·표준, Oracle 11gR2+) |
|---|---|---|
| 루트 지정 | START WITH | 앵커 쿼리의 WHERE |
| 다음 단계 연결 | CONNECT BY PRIOR | 재귀 쿼리에서 CTE와 조인 |
| 깊이 | LEVEL | 칼럼을 직접 계산 |
| 경로 | SYS_CONNECT_BY_PATH | 문자열 칼럼을 직접 누적 |
| 순환 처리 | NOCYCLE, CONNECT_BY_ISCYCLE | Oracle·표준은 CYCLE 절, SQL Server는 MAXRECURSION 옵션(기본 100단계) |
- PRIOR 위치로 전개 방향을 판단한다.
PRIOR 사원번호 = 관리자번호는 위에서 아래(순방향),PRIOR 관리자번호 = 사원번호는 아래에서 위(역방향)다. - WHERE 조건은 전개가 끝난 뒤 해당 행만 거르고, CONNECT BY 조건은 그 행 아래 가지까지 통째로 잘라 낸다. 결과 행 수를 묻는 문제가 자주 나온다.
- LEVEL은 시작 행이 1이며, 역방향에서도 시작 행이 1이다. CONNECT_BY_ISLEAF는 리프면 1이다.
- ORDER SIBLINGS BY는 계층을 유지한 채 형제끼리만 정렬하고, 일반 ORDER BY는 계층 순서를 깨뜨린다.
- SQL Server에는 CONNECT BY가 없고 재귀 CTE(WITH … UNION ALL)를 쓴다. 단순히 상위 관리자 이름만 붙이는 경우는 셀프 조인으로 처리한다.