🌳 11편. WITH절과 재귀 쿼리 (WITH RECURSIVE)

🌳 11편. WITH절과 재귀 쿼리 (WITH RECURSIVE)

Oracle SQL에서 WITH절은 복잡한 서브쿼리를 공통 테이블 표현식(CTE)으로 정의하고 재사용하는 기능입니다.
또한 Oracle 11g부터는 재귀 CTE를 통해 계층형 데이터를 CONNECT BY 없이 조회할 수 있게 되었습니다.
이번 글에서는 WITH의 기본 구조와 재귀 쿼리 구현 방식을 예제 중심으로 설명합니다.

---

📦 WITH절 기본 구조

WITH절은 SQL 내부에서 임시 뷰(View)처럼 서브쿼리를 정의하고, 이를 메인 쿼리에서 재사용합니다.

WITH SALES_SUM AS (
  SELECT MONTH, SUM(AMOUNT) AS TOTAL
  FROM SALES
  GROUP BY MONTH
)
SELECT * FROM SALES_SUM WHERE TOTAL > 1000;

• 서브쿼리를 분리해 가독성 향상
• 복수의 WITH 절 사용 가능 (쉼표로 구분)

---

🌱 WITH RECURSIVE (계층 구조 재귀 쿼리)

Oracle에서는 CONNECT BY를 사용하지 않고도 재귀 CTE를 활용해 계층 데이터를 조회할 수 있습니다.

예제 테이블:

CREATE TABLE ORG (
  ID NUMBER,
  NAME VARCHAR2(50),
  MANAGER_ID NUMBER
);

INSERT INTO ORG VALUES (1, 'CEO', NULL);
INSERT INTO ORG VALUES (2, 'CTO', 1);
INSERT INTO ORG VALUES (3, 'Dev1', 2);
INSERT INTO ORG VALUES (4, 'CFO', 1);
COMMIT;

---

🔄 재귀 WITH 쿼리 구현

WITH ORG_TREE (ID, NAME, MANAGER_ID, LV) AS (
  -- anchor: 루트 노드
  SELECT ID, NAME, MANAGER_ID, 1
  FROM ORG
  WHERE MANAGER_ID IS NULL

  UNION ALL

  -- recursive part: 자식 노드 확장
  SELECT O.ID, O.NAME, O.MANAGER_ID, T.LV + 1
  FROM ORG O
  JOIN ORG_TREE T ON O.MANAGER_ID = T.ID
)
SELECT * FROM ORG_TREE ORDER BY LV, ID;

→ 결과:

ID	NAME	MANAGER_ID	LV
1	CEO	(null)	1
2	CTO	1	2
4	CFO	1	2
3	Dev1	2	3
---

📌 재귀 WITH 사용 시 주의사항

• Oracle은 RECURSIVE 키워드를 명시하지 않지만, ANSI SQL에서는 필수
• UNION ALL 사용이 일반적 (UNION은 성능 저하)
• 루프 방지 로직이 없기 때문에 조인 조건 주의

---

💡 WITH vs CONNECT BY 비교

구분	WITH 재귀	CONNECT BY
표준 SQL	Yes (ANSI 지원)	No (Oracle 전용)
유연성	높음 (JOIN 가능)	제약 많음 (ORDER BY, WHERE 사용 제한)
가독성	높음	낮음
루프 제어	직접 구현	NOCYCLE 지원

---

📝 마무리

이번 글에서는 Oracle의 WITH 절과 재귀 CTE를 통해 서브쿼리 정리 및 계층 데이터 조회 방법을 알아봤습니다.
다음 글에서는 동적 SQL과 EXECUTE IMMEDIATE 사용법을 소개합니다.