WITH 절

WITH 절

1. WITH 절이란?

뜻 (영문)

The WITH clause lets you define a temporary result set (also called a CTE: Common Table Expression) that you can refer to within your main query.

뜻 (한글)

WITH 절은 **임시 테이블(=서브쿼리 결과)**을 먼저 정의하고, 그걸 메인 쿼리에서 재사용할 수 있게 해주는 문법이에요.

⸻

2. 왜 사용할까? • 복잡한 서브쿼리를 미리 이름 붙여서 정리할 수 있음 • 가독성 좋아짐 • 쿼리 재사용 가능 (같은 서브쿼리를 여러 번 쓰지 않아도 됨) • 중첩된 서브쿼리를 피할 수 있음

⸻

3. 기본 문법

WITH 임시이름 AS (

   서브쿼리

) SELECT ... FROM 임시이름;

⸻

4. 예제

예제 테이블: sales

id product amount year 1 Laptop 2000 2023 2 Phone 1500 2023 3 Laptop 2200 2024 4 Phone 1800 2024 5 Laptop 2500 2024

⸻

예제 1: 2024년 판매 데이터만 따로 떼어서 분석

WITH sales_2024 AS (

   SELECT *
   FROM sales
   WHERE year = 2024

) SELECT product, SUM(amount) AS total FROM sales_2024 GROUP BY product;

결과

product total Laptop 4700 Phone 1800

설명: WITH sales_2024라는 임시 테이블을 만들어 2024년 데이터만 분리하고, 그걸 기준으로 제품별 총 판매액을 계산했어요.

⸻

예제 2: WITH 절을 두 개 이상 사용하는 경우

WITH sales_2023 AS (

   SELECT * FROM sales WHERE year = 2023

), sales_2024 AS (

   SELECT * FROM sales WHERE year = 2024

) SELECT '2023' AS year, product, SUM(amount) AS total FROM sales_2023 GROUP BY product UNION ALL SELECT '2024' AS year, product, SUM(amount) AS total FROM sales_2024 GROUP BY product;

결과

year product total 2023 Laptop 2000 2023 Phone 1500 2024 Laptop 4700 2024 Phone 1800

설명: 연도별로 따로 나눈 데이터를 UNION ALL로 합쳐서 보여줍니다.

⸻

5. WITH 절 vs 서브쿼리

항목 WITH 절 서브쿼리 가독성 매우 높음 복잡하면 읽기 어려움 재사용 가능 (한 번 정의해서 여러 번 사용) 동일 쿼리를 반복 작성해야 함 성능 Oracle이 내부적으로 최적화 가능 중복 계산 발생 시 성능 저하 가능

⸻

6. 주의사항 • Oracle 11g 이상부터 지원되며, Oracle 19c에서도 완벽히 사용 가능 • WITH 절은 SELECT, INSERT, UPDATE, DELETE에도 사용할 수 있음 • WITH 절 내 쿼리에는 ORDER BY를 직접 넣을 수 없음 (단, ROW_NUMBER()와 함께 정렬 가능)

⸻

요약 정리

개념 설명 WITH 절 서브쿼리를 임시 테이블처럼 이름 붙여 사용 장점 쿼리 재사용, 가독성 향상, 유지보수 용이 사용 예 복잡한 쿼리 정리, 여러 조건 조합 분석 등

좋습니다! 이번엔 Oracle 19c에서 사용할 수 있는 **고급 WITH 절 기능인 재귀 CTE (WITH RECURSIVE)**와 실전 활용 예제를 소개해드릴게요. Oracle에서는 RECURSIVE 키워드는 사용하지 않지만, 재귀적 WITH 사용은 가능합니다.

⸻

1. WITH 재귀 쿼리란? • **재귀 CTE(Common Table Expression)**는 WITH 절 안에서 자기 자신을 호출해서 계층적으로 데이터를 조회할 수 있는 기능입니다. • 주로 트리 구조, 부서-직원, 폴더-파일, 부모-자식 관계 등 계층형 데이터를 처리할 때 사용합니다.

⸻

2. 기본 구조

WITH cte_name (컬럼들) AS (

   -- anchor (기준이 되는 첫 번째 쿼리)
   SELECT ...
   FROM ...
   WHERE ...

   UNION ALL

   -- recursive (재귀적으로 자기 자신 호출)
   SELECT ...
   FROM 원본테이블 t
   JOIN cte_name c ON t.parent_id = c.id

) SELECT * FROM cte_name;

⸻

3. 실전 예제: 조직도 계층 구조 조회

예제 테이블: employees

emp_id name manager_id 1 CEO NULL 2 Alice 1 3 Bob 1 4 Carol 2 5 David 2 6 Eve 3

⸻

목표

CEO → 부하 직원 → 그 밑 직원 순으로 전체 조직 계층도를 출력하고 싶어요.

⸻

쿼리

WITH org_chart (emp_id, name, manager_id, level) AS (

   -- Anchor: 최상위 관리자 (CEO)
   SELECT emp_id, name, manager_id, 1 AS level
   FROM employees
   WHERE manager_id IS NULL

   UNION ALL

   -- Recursive: 상위 직원의 부하직원들
   SELECT e.emp_id, e.name, e.manager_id, oc.level + 1
   FROM employees e
   JOIN org_chart oc ON e.manager_id = oc.emp_id

) SELECT LPAD(' ', (level - 1) * 4) || name AS indented_name, level FROM org_chart ORDER BY level, emp_id;

⸻

결과

indented_name level CEO 1 Alice 2 Bob 2 Carol 3 David 3 Eve 3

설명: LPAD를 이용해서 직원 이름 앞에 공백을 넣어 들여쓰기 효과를 주었고, level을 이용해 계층을 구분했어요.

⸻

4. 실전 활용: 메뉴 구조, 카테고리 분류, 댓글 트리 등 • 게시판 댓글 (대댓글) • 온라인 쇼핑몰 카테고리 (대분류 > 중분류 > 소분류) • 파일 시스템 (폴더 > 하위 폴더 > 파일) • 사내 조직도

⸻

5. 주의사항 (Oracle 19c 기준)

항목 설명 RECURSIVE 키워드 Oracle에서는 쓰지 않습니다. PostgreSQL 같은 DBMS에서만 필요합니다. UNION ALL 필수 재귀 CTE는 **반드시 UNION ALL**로 이어야 합니다. LEVEL, CONNECT BY 기존의 Oracle 계층 쿼리 방식 (CONNECT BY)와도 유사한 역할을 합니다.

⸻

보너스: CONNECT BY vs WITH 재귀

특징 CONNECT BY WITH 재귀 문법 Oracle 전용 문법 표준 SQL 방식 (Oracle에서도 지원됨) 읽기 쉬움 간단하지만 복잡해지면 어려움 가독성 높음, 유연함 순서 제어 ORDER SIBLINGS BY 필요 그냥 ORDER BY 사용 가능 컬럼 추가 유연성 낮음 WITH에 원하는 컬럼 자유롭게 추가 가능

⸻

더 원하시는 실전 시나리오나 특정 데이터에 맞는 계층 쿼리가 있으신가요? 직접 구조를 만들어드릴 수도 있어요.