[오라클] connect by 개념 및 사용법

1️⃣ CONNECT BY란?

Oracle의 CONNECT BY는 계층적 쿼리(Hierarchical Query) 를 실행할 때 사용되는 구문입니다.
계층 구조를 가진 데이터를 부모-자식 관계를 기준으로 트리 형태로 조회할 수 있도록 합니다.

📌 주로 사용되는 곳:

• 조직도 (상사-부하 관계)
• 카테고리 트리 (부모-자식 관계)
• 제품 분류 구조
• 메뉴 및 UI 네비게이션 구조

 

2️⃣ CONNECT BY의 기본 구문

SELECT 컬럼명
FROM 테이블명
START WITH 루트 조건
CONNECT BY PRIOR 부모_컬럼 = 자식_컬럼;

• START WITH → 최상위 부모 노드(루트 노드)를 설정
• CONNECT BY PRIOR 부모_컬럼 = 자식_컬럼 → 부모-자식 관계 정의

 

3️⃣ 예제: 직원 조직도 계층 구조 조회

📌 (1) 샘플 테이블 생성 및 데이터 삽입

CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,  -- 직원 ID
    emp_name VARCHAR2(50),      -- 직원 이름
    manager_id NUMBER           -- 상사의 ID (최고 경영자는 NULL)
);

INSERT INTO employees VALUES (1, 'CEO', NULL);
INSERT INTO employees VALUES (2, 'Manager A', 1);
INSERT INTO employees VALUES (3, 'Manager B', 1);
INSERT INTO employees VALUES (4, 'Employee A1', 2);
INSERT INTO employees VALUES (5, 'Employee A2', 2);
INSERT INTO employees VALUES (6, 'Employee B1', 3);
INSERT INTO employees VALUES (7, 'Employee B2', 3);

COMMIT;

 

📌 조직도 구조

CEO (1)
 ├── Manager A (2)
 │    ├── Employee A1 (4)
 │    ├── Employee A2 (5)
 ├── Manager B (3)
      ├── Employee B1 (6)
      ├── Employee B2 (7)

 

 

📌 (2) CONNECT BY를 이용한 계층형 쿼리

SELECT LEVEL, emp_id, emp_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

📌 실행 결과

LEVEL | EMP_ID | EMP_NAME     | MANAGER_ID
-----------------------------------------
1     | 1      | CEO         | NULL
2     | 2      | Manager A   | 1
3     | 4      | Employee A1 | 2
3     | 5      | Employee A2 | 2
2     | 3      | Manager B   | 1
3     | 6      | Employee B1 | 3
3     | 7      | Employee B2 | 3

✅ LEVEL 컬럼은 계층 레벨을 나타내며, 최상위 부모(CEO)는 LEVEL=1, 그 다음 레벨부터 차례로 증가합니다.

 

 

📌 (3) LPAD를 사용한 계층 구조 가시화

SELECT LPAD(' ', LEVEL * 2, ' ') || emp_name AS hierarchy, emp_id, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id;

📌 실행 결과

HIERARCHY        | EMP_ID | MANAGER_ID
-----------------------------------
CEO             | 1      | NULL
  Manager A     | 2      | 1
    Employee A1 | 4      | 2
    Employee A2 | 5      | 2
  Manager B     | 3      | 1
    Employee B1 | 6      | 3
    Employee B2 | 7      | 3

✅ LPAD(' ', LEVEL * 2, ' ') || emp_name 를 사용하여 들여쓰기로 계층 구조를 표현합니다.

 

 

4️⃣ CONNECT BY에서 SYS_CONNECT_BY_PATH 활용

계층 구조를 경로(Path) 형태로 출력할 수도 있습니다.

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

📌 실행 결과

EMP_ID | EMP_NAME     | MANAGER_ID | PATH
--------------------------------------------
1      | CEO         | NULL       |  -> CEO
2      | Manager A   | 1          |  -> CEO -> Manager A
4      | Employee A1 | 2          |  -> CEO -> Manager A -> Employee A1
5      | Employee A2 | 2          |  -> CEO -> Manager A -> Employee A2
3      | Manager B   | 1          |  -> CEO -> Manager B
6      | Employee B1 | 3          |  -> CEO -> Manager B -> Employee B1
7      | Employee B2 | 3          |  -> CEO -> Manager B -> Employee B2

✅ SYS_CONNECT_BY_PATH(컬럼명, 구분자)를 이용하면 전체 경로(Path) 를 출력할 수 있습니다.

 

 

5️⃣ CONNECT BY에서 ORDER SIBLINGS BY 활용 (계층 정렬)

ORDER SIBLINGS BY를 사용하면 같은 레벨 내에서 특정 컬럼을 기준으로 정렬할 수 있습니다.

SELECT LEVEL, emp_id, emp_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR emp_id = manager_id
ORDER SIBLINGS BY emp_name;

 

✅ 같은 레벨(부서 내)에서는 emp_name 기준으로 알파벳 순 정렬됩니다.

 

 

6️⃣ CONNECT BY에서 순환 참조(Loop) 방지 방법

계층 구조에서 부모-자식 관계가 순환 구조(Loop)가 되면 무한 루프에 빠질 수 있습니다.
이런 문제를 방지하려면 NOCYCLE 옵션을 사용합니다.

SELECT emp_id, emp_name, manager_id
FROM employees
START WITH manager_id IS NULL
CONNECT BY NOCYCLE PRIOR emp_id = manager_id;

✅ NOCYCLE 옵션을 사용하면 순환 관계(Loop)가 감지될 경우 즉시 중단합니다.

 

 

7️⃣ CONNECT BY 요약 정리

기능	설명	예제
START WITH	루트 노드(최상위 부모)를 지정	START WITH manager_id IS NULL
CONNECT BY PRIOR	부모-자식 관계 정의	CONNECT BY PRIOR emp_id = manager_id
LEVEL	계층 구조의 깊이를 나타냄	SELECT LEVEL FROM employees
SYS_CONNECT_BY_PATH	전체 경로 출력	SYS_CONNECT_BY_PATH(emp_name, ' -> ')
ORDER SIBLINGS BY	계층 내에서 정렬	ORDER SIBLINGS BY emp_name
NOCYCLE	순환 참조 방지	CONNECT BY NOCYCLE PRIOR emp_id = manager_id

 

 

8️⃣ 결론

• CONNECT BY는 계층형 데이터(부모-자식 관계)를 다룰 때 필수적인 기능
• LEVEL, SYS_CONNECT_BY_PATH, ORDER SIBLINGS BY 등을 조합하면 더욱 강력한 계층 쿼리를 작성 가능
• 순환 참조 문제를 방지하려면 NOCYCLE을 사용해야 함

✅ 트리 구조 데이터 조회가 필요할 때 CONNECT BY를 활용하면 SQL 하나로 쉽게 해결 가능! 🚀