Oracle 계층형 쿼리 정리: CONNECT BY 문법

Oracle에서 계층 구조(트리 구조) 데이터를 SQL로 표현하고 조회할 때 사용하는 문법이 바로 CONNECT BY입니다. 이 문법을 통해 조직도, 카테고리 트리, 폴더 구조 등의 재귀적 구조를 SQL만으로 처리할 수 있습니다.

---

1. 기본 구조

SELECT 컬럼들
FROM 테이블
START WITH 루트 조건
CONNECT BY [NOCYCLE] PRIOR 부모컬럼 = 자식컬럼;

• START WITH: 계층의 시작(루트) 노드를 지정
• CONNECT BY: 계층의 부모-자식 관계 지정
• PRIOR: 부모/자식 방향성 결정
• NOCYCLE: 순환 구조가 있을 경우 무한 루프 방지

---

2. 계층 방향에 따른 JOIN 해석

구문	방향	출력방향	탐색방향
CONNECT BY PRIOR A = B	부모 → 자식	상위 → 하위 (가장 일반적)	하위 → 상위
CONNECT BY A = PRIOR B	자식 → 부모	하위 → 상위 (역방향 탐색)	상위 → 하위

예시

-- 부모(empno) → 자식(mgr)
CONNECT BY PRIOR empno = mgr;

---

3. 실습 예시 (EMP 테이블)

테이블 예시

empno	ename	mgr
100	KING	NULL
101	BLAKE	100
102	CLARK	100
103	JONES	102
104	SCOTT	103

3-1. 기본 계층 구조 출력

SELECT LEVEL, empno, ename, mgr
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

결과

• LEVEL은 루트부터의 깊이를 나타냄
• 루트인 KING(LEVEL 1) → BLAKE, CLARK(LEVEL 2) → JONES(LEVEL 3) → SCOTT(LEVEL 4)

---

4. 가상 컬럼들

4-1. LEVEL: 깊이 정보

• 계층의 깊이를 나타냄 (1부터 시작)
• 출력 시 계층 시각화에도 활용 가능

SELECT LPAD(' ', LEVEL * 2) || ename AS tree_view
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

4-2. SYS_CONNECT_BY_PATH: 계층 경로

• 루트 노드부터 현재 노드까지의 경로를 문자열로 표시

SELECT ename, SYS_CONNECT_BY_PATH(ename, ' → ') AS path
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

결과 예시: KING → CLARK → JONES → SCOTT

4-3. CONNECT_BY_ISLEAF: 말단 노드 여부

• 하위 노드가 없으면 1, 있으면 0

SELECT ename, CONNECT_BY_ISLEAF AS is_leaf
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr;

4-4. CONNECT_BY_ISCYCLE: 순환 탐색 여부

• 순환 참조 발생 시 1, 아니면 0
• NOCYCLE과 함께 사용 가능 (Oracle 10g 이상)

SELECT ename, CONNECT_BY_ISCYCLE
FROM emp
START WITH mgr IS NULL
CONNECT BY NOCYCLE PRIOR empno = mgr;

---

5. 고급 활용

5-1. ORDER SIBLINGS BY: 계층 내 정렬

• 같은 계층의 형제 노드를 정렬할 때 사용

SELECT LEVEL, ename
FROM emp
START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename;

5-2. 역방향 트리 탐색 (하위 → 상위)

SELECT LEVEL, ename
FROM emp
START WITH empno = 104
CONNECT BY empno = PRIOR mgr;

결과: SCOTT → JONES → CLARK → KING

---

6. 주의사항

• CONNECT BY는 Oracle 전용 문법이다. MySQL, PostgreSQL, SQL Server에서는 지원되지 않음.
• 타 DBMS에서는 WITH RECURSIVE 또는 CTE(Common Table Expression)를 사용해야 한다.

---

7. 다른 DB 계층형 쿼리 비교

DBMS	계층형 쿼리 방식
Oracle	CONNECT BY
MySQL 8 이상	WITH RECURSIVE CTE
PostgreSQL	WITH RECURSIVE CTE
SQL Server	WITH CTE + RECURSION

---

마무리

Oracle의 CONNECT BY 구문은 복잡한 계층형 데이터를 매우 효율적으로 처리할 수 있게 해주는 강력한 기능이다. 가상 컬럼인 LEVEL, SYS_CONNECT_BY_PATH, CONNECT_BY_ISLEAF 등을 함께 활용하면 복잡한 트리 구조도 SQL만으로 명확하게 표현 가능하다.