[DB] CONNECT BY와 WITH RECURSIVE 계층형 데이터 조회 | 민민의 하드디스크 - 티스토리

내용

신청 마스터테이블이 있을 때, 키값은 순번(신청순번 시퀀스)SN이 계속 쌓인다. UP_SN컬럼도 있다. 해당 컬럼은 상위 이력을 보기 위해 존재하는 컬럼이다. 신청순번/상위신청순번이 있다고 가정.

신청되고 반려되어도 재신청이 가능하고, 신청 건에 대한 모든 이력을(반려포함) 보고싶을 때 사용하려고 설계되었다. 

- 처음 신청 시에는 상위 순번이 없음 (UP_SN : NULL)

- 신청 건에서 반려처리되면 재신청이 가능 (재신청 시 새로운 순번으로 신청내용 INSERT 됨)

- 반려된 건에서 재신청 시 SN이 새로운 신청 건의 UP_SN 값으로 들어감.

 

 

 

예제 데이터

디테일테이블에는 신청 내역이 있고,

• 반려되면 새로운 신청(up_sn이 이전 sn을 참조)
• 승인되면 더 이상 재신청이 없음

sn	up_sn	상태	신청자	신청일자
1	NULL	반려	홍길동	2024-03-10
2	1	반려	홍길동	2024-03-12
3	2	승인	홍길동	2024-03-15
4	NULL	승인	이영희	2024-03-16

---

 

 

 1. CONNECT BY (Oracle)

 

SELECT 
    CONNECT_BY_ROOT sn AS 최초신청번호, 
    sn AS 현재신청번호,
    up_sn AS 이전신청번호,
    상태
FROM 디테일테이블
START WITH up_sn IS NULL
CONNECT BY PRIOR sn = up_sn
ORDER BY 최초신청번호 DESC, 현재신청번호 DESC;

실행 결과

최초신청번호	현재신청번호	이전신청번호	상태
1	1	NULL	반려
1	2	1	반려
1	3	2	승인
4	4	NULL	승인

CONNECT BY 설명

1. START WITH up_sn IS NULL
   → 최초 신청(up_sn이 없는 경우)을 찾음
2. CONNECT BY PRIOR sn = up_sn
   → 이전 신청(sn)을 참조하는 데이터(up_sn)를 계속 따라감
3. CONNECT_BY_ROOT sn AS 최초신청번호
   → 현재 신청(sn)이 속한 최초 신청(sn)을 찾음
4. ORDER BY 최초신청번호 DESC, 현재신청번호 DESC
   → 최신 최초 신청을 기준으로 최신 신청부터 정렬

• Oracle에서는 CONNECT BY를 사용하면 매우 간단하고 빠르게 계층 쿼리를 실행할 수 있음
• 재귀 없이도 부모-자식 관계를 쉽게 조회 가능

---

 

 

 

2. WITH RECURSIVE (MySQL, PostgreSQL)

 

WITH RECURSIVE history AS (
    -- 최초 신청 데이터를 가져옴
    SELECT 
        sn AS 최초신청번호, 
        sn AS 현재신청번호,
        up_sn AS 이전신청번호,
        상태
    FROM 디테일테이블
    WHERE up_sn IS NULL

    UNION ALL

    -- 재귀적으로 up_sn을 따라가며 연결
    SELECT 
        h.최초신청번호,
        d.sn AS 현재신청번호,
        d.up_sn AS 이전신청번호,
        d.상태
    FROM 디테일테이블 d
    JOIN history h ON d.up_sn = h.현재신청번호
)
SELECT * FROM history ORDER BY 최초신청번호 DESC, 현재신청번호 DESC;

 

실행 결과 (Oracle과 동일)

최초신청번호	현재신청번호	이전신청번호	상태
1	1	NULL	반려
1	2	1	반려
1	3	2	승인
4	4	NULL	승인

 

WITH RECURSIVE 설명

1. 초기 데이터 (WHERE up_sn IS NULL)
   • 최초 신청(up_sn이 없는 경우)을 찾음
2. 재귀적으로 up_sn을 따라가면서 연결 (JOIN history h ON d.up_sn = h.현재신청번호)
   • up_sn이 현재신청번호와 같으면 연결
3. 최초 신청번호를 유지 (h.최초신청번호)
   • CONNECT_BY_ROOT 대신, 재귀 쿼리에서 최초 신청번호를 계속 유지
4. ORDER BY 최초신청번호 DESC, 현재신청번호 DESC
   • 최신 최초 신청을 기준으로 정렬

• MySQL과 PostgreSQL에서는 WITH RECURSIVE를 사용해야 계층 데이터를 조회할 수 있음
• 재귀적으로 up_sn을 따라가면서 연결하여 같은 결과를 얻을 수 있음

---

 

 

 

 

CONNECT BY vs WITH RECURSIVE 비교

	Oracle (CONNECT BY)	MySQL / PostgreSQL (WITH RECURSIVE)
간결함	매우 간단함 (CONNECT BY 한 줄)	상대적으로 복잡 (WITH RECURSIVE)
속도	빠름 (최적화된 계층 쿼리)	느릴 수 있음 (재귀 호출)
이해하기 쉬움	CONNECT_BY_ROOT로 최초 신청 찾기	최초신청번호를 유지해야 함
지원 DB	Oracle 전용	MySQL / PostgreSQL 지원

결론

• Oracle에서는 CONNECT BY가 더 쉽고 빠름
• MySQL과 PostgreSQL에서는 WITH RECURSIVE를 사용해야 함
• 결과는 동일하지만, Oracle이 계층형 데이터를 더 쉽게 다룰 수 있음