START WITH ... CONNECT BY PRIOR는 오라클 데이터베이스에서 계층적 쿼리를 작성할 때 사용되는 구문입니다. 이 구문은 트리 구조나 계층적 관계를 가진 데이터를 조회할 때 매우 유용합니다.
1. 기본적인 계층적 쿼리
아래는 직원 테이블(EMP)에서 직원과 그들의 매니저 간의 관계를 표현한 예제입니다.
1.1. 예제 테이블
col ENAME for a30
SELECT EMPNO, ENAME, MGR, C3
FROM EMP;
EMPNO ENAME MGR C3
---------- ------------------------------ ---------- ----------
1 KING 100
2 BLAKE 1 90
3 CLARK 1 80
4 JONES 2 95
5 SCOTT 2 85
// EMPNO: 직원 번호
// ENAME: 직원 이름
// MGR: 매니저의 직원 번호
2. 직원 매니저 관계를 트리 형식으로 조회
최상위 노드(KING)를 기점으로 하위 노드를 모두 순차적으로 조회합니다.
KING
│
├── BLAKE
│ │
│ ├── JONES
│ └── SCOTT
│
└── CLARKSELECT EMPNO, ENAME, MGR, LEVEL
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
EMPNO ENAME MGR LEVEL
---------- ------------------------------ ---------- ----------
1 KING 1
2 BLAKE 1 2
4 JONES 2 3
5 SCOTT 2 3
3 CLARK 1 2
// LEVEL: 계층 구조에서의 깊이를 나타냅니다. 루트 노드는 1부터 시작하며, 자식 노드로 갈수록 증가합니다.
// START WITH: 계층적 쿼리가 시작되는 루트 노드를 지정합니다.
// CONNECT BY PRIOR: 계층적 관계를 정의합니다. PRIOR 키워드를 사용하여 부모와 자식 간의 관계를 나타냅니다.
3. 직원들이 속한 트리 구조를 반대로 탐색
특정 노드(SCOTT)를 기준으로 해서, 그 노드의 부모 노드들만 차례로 조회합니다.
SCOTT
│
└── BLAKE
│
└── KINGSELECT EMPNO, ENAME, MGR, LEVEL
FROM EMP
START WITH EMPNO = 5 -- SCOTT을 시작점으로 설정
CONNECT BY PRIOR MGR = EMPNO;
EMPNO ENAME MGR LEVEL
---------- ------------------------------ ---------- ----------
5 SCOTT 2 1
2 BLAKE 1 2
1 KING 3
4. START WITH을 중간에서 시작하여 탐색
4.1. 순방향
BLAKE
│
├── JONES
└── SCOTTSELECT EMPNO, ENAME, MGR, LEVEL
FROM EMP
START WITH EMPNO = 2 -- BLAKE 노드를 시작점으로 설정
CONNECT BY PRIOR EMPNO = MGR; -- 순방향
EMPNO ENAME MGR LEVEL
---------- ------------------------------ ---------- ----------
2 BLAKE 1 1
4 JONES 2 2
5 SCOTT 2 2
4.2. 역방향
BLAKE
│
└── KINGSELECT EMPNO, ENAME, MGR, LEVEL
FROM EMP
START WITH EMPNO = 2 -- BLAKE 노드를 시작점으로 설정
CONNECT BY PRIOR MGR = EMPNO; -- 역방향
EMPNO ENAME MGR LEVEL
---------- ------------------------------ ---------- ----------
2 BLAKE 1 1
1 KING 2
5. ORDER SIBLINGS BY 구문
오라클에서 계층적 쿼리(START WITH ... CONNECT BY PRIOR)의 결과를 정렬하는 데 사용되는 구문입니다.
이 구문은 동일한 부모를 가진 형제 노드들 간의 순서를 제어할 때 매우 유용합니다.
KING (C3: 100)
│
├── BLAKE (C3: 90)
│ │
│ ├── JONES (C3: 95)
│ └── SCOTT (C3: 85)
│
└── CLARK (C3: 80)SELECT EMPNO, ENAME, MGR, C3, LEVEL
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
EMPNO ENAME MGR C3 LEVEL
---------- ------------------------------ ---------- ---------- ----------
1 KING 100 1
2 BLAKE 1 90 2
4 JONES 2 95 3
5 SCOTT 2 85 3
3 CLARK 1 80 2
// 탐색순서: KING -> BLAKE(JONES, SCOTT) -> CLARKSELECT EMPNO, ENAME, MGR, C3, LEVEL
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR
ORDER SIBLINGS BY C3 ASC; -- 정렬기준변경
EMPNO ENAME MGR C3 LEVEL
---------- ------------------------------ ---------- ---------- ----------
1 KING 100 1
3 CLARK 1 80 2
2 BLAKE 1 90 2
5 SCOTT 2 85 3
4 JONES 2 95 3
// 탐색순서: KING -> CLARK -> BLAKE(JONES, SCOTT)
6. 부모 노드와 자식 노드, 순방향과 역방향
이쯤 되면 계층적 쿼리에서 START WITH은 이해가 됩니다. 하지만 아직 부모 노드와 자식 노드의 관계와 방향의 기준을 어떻게 정하는지 조금 헷갈립니다. 그래서 아래에서 저만의 방법으로 이를 정리하였습니다. 정확하지 않을 수 있으니 참고만 바랍니다.
6.1. 개념적 접근방법 - 그래서 누가 부모고 누가 자식인가?
- 매니저와 직원 관계: 매니저(부모) - 직원(자식)
- 회사와 직원 관계: 회사(부모) - 직원(자식)
- 지부와 지사 관계: 아시아지부(부모) - 한국지사(자식), 한국지사(부모) - 서울지점(자식)
6.2. 개념적 접근방법 - 순방향과 역방향
- 순방향: 부모 노드에서 자식 노드로 이동하는 방향입니다.
- 역방향: 자식 노드에서 부모 노드로 이동하는 방향입니다.
PRIOR 자식 = 부모 (순방향)
PRIOR 부모 = 자식 (역방향)
자식 = PRIOR 부모 (역방향)
부모 = PRIOR 자식 (순방향)
기억법:
- PRIOR 쪽으로 이동한다. (eg. PRIOR 자식 <= 부모)
- 자식은 부모에게 자산을 받는 것이 자연스러우므로 순방향입니다.
- 부모가 자식에게 자산을 받는 것은 비정상적인 경우이므로 역방향입니다.
6.3. 참조 관계를 통한 접근
SELECT EMPNO, ENAME, MGR, LEVEL
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR EMPNO = MGR;
EMPNO ENAME MGR LEVEL
---------- ------------------------------ ---------- ----------
1 KING 1
2 BLAKE 1 2
4 JONES 2 3
5 SCOTT 2 3
3 CLARK 1 21. MGR IS NULL인 행을 기준으로 시작 => KING
2. PRIOR EMPNO = MGR 패턴
KING의 EMPNO = 1 / MGR값이 1이 되는 행을 찾는다. => BLAKE, CLARK
3. BLAKE의 EMPNO = 2 / MGR값이 2가 되는 행을 찾는다. => JONES, SCOTT
4. CLARK의 EMPNO = 3 / MGR값이 3가 되는 행을 찾는다. => 없음
5. JONES의 EMPNO = 4 / MGR값이 4가 되는 행을 찾는다. => 없음
6. SCOTT의 EMPNO = 5 / MGR값이 5가 되는 행을 찾는다. => 없음
// 실제로 조회되는 순서는 다르지만 어떤 방식으로 탐색하는지 원리확인SELECT EMPNO, ENAME, MGR, LEVEL
FROM EMP
START WITH MGR IS NULL
CONNECT BY PRIOR MGR = EMPNO;
EMPNO ENAME MGR LEVEL
---------- ------------------------------ ---------- ----------
1 KING 1
1. MGR IS NULL인 행을 기준으로 시작 => KING
2. PRIOR MGR = EMPNO 패턴
KING의 MGR = NULL / EMPNO값이 NULL이 되는 행을 찾는다. => 없음