[SQLD] 4-7: 계층형 질의와 셀프 조인

 

이전 포스트:

2025.02.21 - [자격증/SQLD] - [SQLD] 4-6: Top N 쿼리 (Top N Query)

[SQLD] 4-6: Top N 쿼리 (Top N Query)

 

 

[SQLD] 4-7: 계층형 질의와 셀프 조인

 

관계형 데이터베이스에서는 부모-자식 관계를 표현해야 하는 경우가 자주 발생합니다. 데이터가 계층적으로 구성되는 경우, 이 계층 구조를 효과적으로 조회하는 방법이 필요합니다. 일반적으로 계층 구조를 표현하는 방법으로 두 가지가 많이 사용됩니다.

1. 셀프 조인(Self Join): 같은 테이블을 여러 번 조인하여 계층 구조를 구현하는 방식
2. 계층형 질의(Hierarchical Query): CONNECT BY 구문을 이용하여 직관적으로 계층 구조를 표현하는 방식 (주로 Oracle에서 지원)

이 글에서는 계층형 질의와 셀프 조인의 개념과 차이점을 설명하고, SQL 예제와 함께 주요 함수들을 상세히 분석해 보겠습니다.

---

1. 셀프 조인(Self Join)

 

: 셀프 조인은 같은 테이블을 여러 번 조인하여 부모-자식 관계를 구현하는 방식입니다. 일반적으로 INNER JOIN 또는 LEFT JOIN을 사용하여 계층 구조를 표현합니다.

• 모든 관계형 데이터베이스에서 지원됨
• SQL 표준 문법(JOIN)을 활용하여 비교적 쉽게 이해 가능
• 하지만 계층 구조가 깊어질수록 쿼리 작성이 복잡해질 수 있음

 

예제 테이블: employees

employee_id	employee_name	manager_id
1	CEO	NULL
2	Manager A	1
3	Manager B	1
4	Employee X	2
5	Employee Y	2
6	Employee Z	3

 

셀프 조인 SQL 예제

SELECT e1.employee_name AS Employee, e2.employee_name AS Manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

 

실행 결과

Employee	Manager
Manager A	CEO
Manager B	CEO
Employee X	Manager A
Employee Y	Manager A
Employee Z	Manager B

---

2. 계층형 질의(Hierarchical Query) 

계층형 질의는 재귀적으로 부모-자식 관계를 탐색하여 데이터를 조회하는 SQL 방식입니다. Oracle DBMS에서 주로 사용되며, CONNECT BY 구문을 활용합니다.

 

• 계층 구조를 직관적으로 표현 가능
• LEVEL을 이용해 계층의 깊이를 쉽게 확인할 수 있음
• CONNECT BY PRIOR를 사용하여 부모-자식 관계를 자동 탐색
• 재귀적으로 탐색하기 때문에 셀프 조인보다 효율적

 

🔹 기본 문법

SELECT ...
FROM 테이블
START WITH 최상위 조건
CONNECT BY PRIOR 부모컬럼 = 자식컬럼;

• START WITH: 최상위(root) 노드를 정의
• CONNECT BY PRIOR: 부모-자식 관계를 설정

Top-Down	CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID	부모 → 자식
Bottom-Up	CONNECT BY EMPLOYEE_ID = PRIOR MANAGER_ID	자식 → 부모

---

 

3. 계층형 질의 주요 함수와 예제

예제 테이블: employees

employee_id	employee_name	manager_id
1	CEO	NULL
2	Manager A	1
3	Manager B	1
4	Employee X	2
5	Employee Y	2
6	Employee Z	3

 

🔸 LEVEL (계층의 깊이 확인)

SELECT LEVEL, employee_id, employee_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

🔹 실행 결과

LEVEL	employee_id	employee_name	manager_id
1	1	CEO	NULL
2	2	Manager A	1
3	4	Employee X	2

 

🔸 SYS_CONNECT_BY_PATH() (계층 경로 표시)

SELECT employee_name, SYS_CONNECT_BY_PATH(employee_name, ' -> ') AS path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

🔹 실행 결과

employee_name	path
CEO	CEO
Manager A	CEO -> Manager A

 

🔸 CONNECT_BY_ROOT (최상위 부모 노드 가져오기)

SELECT employee_name, CONNECT_BY_ROOT employee_name AS root_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

🔹 실행 결과

employee_name	root_name
CEO	CEO
Manager A	CEO

 

🔸 ORDER SIBLINGS BY (계층 구조에서 형제 정렬)

SELECT employee_name
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY employee_name;

🔹 실행 결과

employee_name

CEO

Manager A

Manager B

 

---

4. 계층 구조 문제 예제

문제

다음 SQL의 실행 결과로 가장 적절한 것은 무엇인가?

SELECT EMPLOYEE_ID, 
       NAME, 
       MANAGER_ID
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID
ORDER SIBLINGS BY EMPLOYEE_ID DESC;

 

[EMPLOYEES 테이블]

EMPLOYEE_ID	NAME	MANAGER_ID
100	Steven	NULL
101	Neena	100
102	Lex	100
108	Nancy	101
109	Daniel	108
110	John	108
111	Ismael	108
112	Jose Manuel	108
113	Luis	108

정답 해설

이 SQL 쿼리는 계층형 질의(Hierarchical Query)를 수행하며, ORDER SIBLINGS BY EMPLOYEE_ID DESC을 사용하여 같은 계층에 속하는 형제 노드들을 EMPLOYEE_ID 기준으로 내림차순 정렬합니다.

📌 실행 흐름

1. START WITH MANAGER_ID IS NULL: 최상위 계층(CEO)인 Steven (100)부터 탐색 시작
2. CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID: 부모(EMPLOYEE_ID) → 자식(MANAGER_ID) 관계를 따라 내려감
3. ORDER SIBLINGS BY EMPLOYEE_ID DESC: 같은 계층 내의 형제 노드들을 EMPLOYEE_ID 내림차순으로 정렬

📌 계층 구조 정리

100 (Steven)
├── 102 (Lex)
├── 101 (Neena)
     ├── 108 (Nancy)
         ├── 113 (Luis)
         ├── 112 (Jose Manuel)
         ├── 111 (Ismael)
         ├── 110 (John)
         ├── 109 (Daniel)

 

📌 최종 결과

EMPLOYEE_ID	NAME	MANAGER_ID
100	Steven	NULL
102	Lex	100
101	Neena	100
108	Nancy	101
113	Luis	108
112	Jose Manuel	108
111	Ismael	108
110	John	108
109	Daniel	108

 

🔹 핵심 포인트

• CONNECT BY PRIOR EMPLOYEE_ID = MANAGER_ID를 사용하여 재귀적으로 계층을 따라 탐색
• ORDER SIBLINGS BY EMPLOYEE_ID DESC로 같은 계층 내 형제 노드를 EMPLOYEE_ID 기준 내림차순 정렬

따라서 Lex(102)가 Neena(101)보다 먼저 정렬되고, Nancy(108)의 자식 노드들도 내림차순 정렬됨.

 

---

5. 결론

• 셀프 조인(Self Join)은 같은 테이블을 여러 번 조인하여 계층 구조를 표현하는 방식으로, 일반적인 SQL 조인을 활용합니다.
• 계층형 질의(Hierarchical Query)는 CONNECT BY 구문을 사용하여 자동으로 계층 구조를 탐색하는 방식으로, 보다 직관적이고 효율적입니다.
• 계층 구조를 표현하는 여러 함수(LEVEL, SYS_CONNECT_BY_PATH(), CONNECT_BY_ROOT, CONNECT_BY_ISLEAF, PRIOR, ORDER SIBLINGS BY)를 활용하면 보다 정교한 계층 데이터를 쉽게 관리할 수 있습니다.

 

 

---

 

💡 도움이 되셨다면 댓글과 공감 부탁드립니다! 😊
📌 더 많은 알고리즘 풀이와 프로그래밍 자료는 블로그에서 확인하세요!
✉️ 문의나 피드백은 댓글이나 이메일로 남겨주세요.