#22 계층 쿼리

목차

계층 쿼리란

계층 쿼리는 자기 자신을 부모/자식으로 참조하는 트리 구조를 탐색할 때 사용하는 쿼리다.
예를 들어 회사의 조직도(CEO → 팀장 → 사원), 댓글의 답글 구조, 분류체계 등
**"상위-하위 관계를 가진 데이터"**에서 필수적으로 사용됨.

 

employees
id	name	position	manager_id
1	철수	CEO	NULL
2	영희	CTO	1
3	민수	팀장	2
4	지수	대리	3
5	나리	사원	4

 

• manager_id가 NULL이면 최상위 (CEO)
• 각 직원은 manager_id를 통해 상사를 참조함

LEVEL 현재의 DEPTH를 반환한다. 루트 노드는 1이 된다. SYS_CONNECT_BY_PATH (컬럼, 구분자) 루트 노드부터 현재 노드까지의 경로를 출력해주는 함수이다. START WITH 경로가 시작되는 루트 노드를 생성해주는 절이다. CONNECT BY 루트로부터 자식 노드를 생성해주는 절이다. 조건에 만족하는 데이터가 없을 때까지 노드를 생성한다. PRIOR 바로 앞에 있는 부모 노드의 값을 반환한다.

라고는 하지만 이해가 힘들 것이다.. 나도 그랬던 것처럼..

 

LEVEL

지금 내가 보고 있는 행이 **계층 구조에서 몇 번째 단계(깊이)**에 있는지를 숫자로 알려준다.
시작 지점(루트 노드)은 항상 1이다.

 

철수 (LEVEL 1)  
 └ 영희 (LEVEL 2)  
     └ 민수 (LEVEL 3)

SYS_CONNECT_BY_PATH(컬럼, 구분자)

루트 노드부터 지금 이 행까지 어떤 경로를 거쳐 왔는지를 문자열로 보여준다.
구분자는 >나 / 같이 단계 구분에 쓰는 문자.

SYS_CONNECT_BY_PATH(name, ' > ')

결과 → > 철수 > 영희 > 민수

START WITH

이 계층 구조를 어디서부터 시작할지 지정하는 문장이다.
보통 루트 노드 조건 (manager_id IS NULL)을 여기에 쓴다.

CONNECT BY

어떻게 부모-자식 관계를 연결할지 조건을 정하는 부분이다.
이 조건에 따라 계속 자식 노드를 따라 내려간다.

CONNECT BY PRIOR id = manager_id

→ 자식의 manager_id가 부모의 id일 때 연결해.

PRIOR

CONNECT BY 안에서 "부모"가 누구인지를 명확히 말해주는 키워드이다.
누구를 기준으로 위(부모), 아래(자식)를 구분할 건지 지정할 때 쓴다.

CONNECT BY PRIOR id = manager_id

 

• PRIOR id: 부모의 id
• manager_id: 자식이 참조하는 id

 

🎯 한줄 요약

Oracle 계층 쿼리는 START WITH로 시작점 정하고,
CONNECT BY로 부모-자식 연결하고,
LEVEL로 깊이 보고,
SYS_CONNECT_BY_PATH로 경로까지 따라가는 구조다.

---

employees
id	name	position	manager_id
1	철수	CEO	NULL
2	영희	CTO	1
3	민수	팀장	2
4	지수	대리	3
5	나리	사원	4

SELECT LEVEL,
       name,
       position,
       SYS_CONNECT_BY_PATH(name, ' > ') AS 경로
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

 

 

 

LEVEL	name	position	경로
1	철수	CEO	> 철수
2	영희	CTO	> 철수 > 영희
3	민수	팀장	> 철수 > 영희 > 민수
4	지수	대리	> 철수 > 영희 > 민수 > 지수
5	나리	사원	> 철수 > 영희 > 민수 > 지수 > 나리

 

• LEVEL: 계층 구조에서의 깊이 (1 = CEO, 2 = CTO, …)
• SYS_CONNECT_BY_PATH: 루트부터 해당 직원까지의 전체 경로를 문자열로 보여줌
• START WITH: 가장 위에 있는 철수(Ceo)부터 시작
• CONNECT BY PRIOR id = manager_id:
  부모의 id를 자식의 manager_id와 연결해서 아래로 내려가는 트리 구조를 만듦

막상 계층 쿼리를 공부하면서 궁굼한 것이 생겼다? 저 문법 LEVEL, SYS_CONNECT_BY_PATH, START WITH, CONNECT BY, PRIOR가 전부 쿼리에 들어가야하는 것인가?

정답은 "아니"다

 

✅ 반드시 필요한 것

🔹 START WITH

• 어디서부터 계층을 시작할지 지정해야 하므로 필수
• 예: START WITH manager_id IS NULL

🔹 CONNECT BY

• 부모-자식 관계가 뭔지 지정하는 조건이므로 필수
• 예: CONNECT BY PRIOR id = manager_id

👉 즉, 계층 구조를 만들려면 START WITH + CONNECT BY는 무조건 필요하다!

 

✅ 선택적으로 사용하는 것

🔹 PRIOR

• CONNECT BY 안에서 누가 부모고 누가 자식인지 방향을 지정할 때 필요
• CONNECT BY id = PRIOR manager_id (상향식 탐색도 가능)
• 거의 항상 같이 쓰이긴 하지만, 반드시 있어야 하는 건 아니고
  명확하게 부모-자식이 구분되는 경우 생략될 수도 있어.

🔹 LEVEL

• 각 행의 깊이(1, 2, 3...)를 표시하고 싶을 때만 사용
• 안 써도 계층 구조는 만들어짐

🔹 SYS_CONNECT_BY_PATH

• 경로 문자열이 필요할 때만 사용
• 예: 메뉴 구조, 조직도 출력 등

 

🎯 한줄 정리

계층 쿼리의 **기본 뼈대는 START WITH와 CONNECT BY**이고,
나머지 (PRIOR, LEVEL, SYS_CONNECT_BY_PATH)는 출력 목적에 따라 선택적으로 추가하는 것이다.

---

 

상황을 가정하고 예제를 만들어 보았다.

 

예제 1: 가장 기본 계층 출력 (필수 요소만)

 

목적

• 계층 관계만 출력하고 싶다
• 계층 표현은 필요 없다

SELECT name, position
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

--
name  | position
------+----------
철수   | CEO
영희   | CTO
민수   | 팀장
지수   | 대리
나리   | 사원

최소 구성: 계층 트리 구조를 따라 탐색하되, 단계나 경로는 보이지 않음

 

---

예제 2: 단계(깊이)를 보고 싶을 때 LEVEL

 

 목적

• 조직도처럼 몇 단계 깊이인지 보고 싶다

SELECT LEVEL, name, position
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

--
LEVEL | name  | position
------+-------+----------
1     | 철수   | CEO
2     | 영희   | CTO
3     | 민수   | 팀장
4     | 지수   | 대리
5     | 나리   | 사원

LEVEL 키워드가 추가되어 각 직원이 조직에서 몇 단계 아래인지 확인 가능

 

---

예제 3: 경로까지 시각적으로 표현하고 싶을 때 SYS_CONNECT_BY_PATH

 

목적

• 트리 구조를 문자열 경로로 표현하고 싶다

SELECT name, SYS_CONNECT_BY_PATH(name, ' > ') AS 경로
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR id = manager_id;

--
name  | 경로
------+-------------------------------
철수   |  > 철수
영희   |  > 철수 > 영희
민수   |  > 철수 > 영희 > 민수
지수   |  > 철수 > 영희 > 민수 > 지수
나리   |  > 철수 > 영희 > 민수 > 지수 > 나리

 

---

예제 4: PRIOR 없는 CONNECT BY는 어떻게 될까?

SELECT name
FROM employees
START WITH manager_id IS NULL
CONNECT BY id = manager_id;

--
실행은 되지만 의도한 부모-자식 연결이 안 됨

오류는 나지 않지만, 부모와 자식이 뒤바뀐 잘못된 구조를 만들 수 있음
항상 방향을 명확하게 하려면 PRIOR는 써주는 게 안전하다

 

✅ 결론 요약

상황	필요한 키워드
기본 계층 구조 탐색	START WITH, CONNECT BY
계층 단계(깊이) 보기	+ LEVEL
경로 추적 (메뉴/조직도 표현)	+ SYS_CONNECT_BY_PATH
부모-자식 방향 명확히 할 때	+ PRIOR