오라클 WITH절 활용법과 사용 예제 정리

SQL을 작성하다 보면 동일한 서브쿼리를 여러 번 반복해서 사용하는 경우가 있습니다.
이때 오라클 WITH절을 활용하면 가독성을 높이고 쿼리 실행 효율성까지 개선할 수 있습니다.

이번 글에서는 오라클 WITH절의 기본 개념과 활용 방법을 정리하겠습니다.

1. 오라클 WITH절 개요

WITH절은 SQL 문장에서 **임시 뷰(서브쿼리 블록)**를 정의하는 구문입니다.
복잡한 쿼리를 단순화하고, 동일한 서브쿼리를 여러 번 호출해야 할 때 유용합니다.
일반적으로 **공통 테이블 표현식(CTE, Common Table Expression)**이라고도 불립니다.

2. WITH절 기본 문법

WITH절은 다음과 같은 형태를 가집니다.

WITH subquery_name AS (
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT *
FROM subquery_name;

예시:

WITH dept_avg AS (
    SELECT deptno, AVG(sal) AS avg_sal
    FROM emp
    GROUP BY deptno
)
SELECT e.ename, e.sal, d.avg_sal
FROM emp e
JOIN dept_avg d ON e.deptno = d.deptno;

👉 dept_avg라는 임시 결과를 정의한 뒤 메인 쿼리에서 재사용합니다.

3. 여러 개의 서브쿼리 정의하기

WITH절은 한 번에 여러 개의 서브쿼리를 정의할 수도 있습니다.

WITH dept_avg AS (
    SELECT deptno, AVG(sal) AS avg_sal
    FROM emp
    GROUP BY deptno
),
high_salary AS (
    SELECT ename, sal, deptno
    FROM emp
    WHERE sal > 3000
)
SELECT h.ename, h.sal, d.avg_sal
FROM high_salary h
JOIN dept_avg d ON h.deptno = d.deptno;

👉 복잡한 조건을 모듈화하여 쿼리를 훨씬 읽기 쉽게 만들 수 있습니다.

4. 재귀적 WITH절 사용법

오라클 11g R2부터는 재귀적 WITH절도 지원합니다.
이는 계층 구조 데이터를 다룰 때 유용합니다.

WITH employee_hierarchy (empno, mgr, level_no) AS (
    SELECT empno, mgr, 1
    FROM emp
    WHERE mgr IS NULL
    UNION ALL
    SELECT e.empno, e.mgr, h.level_no + 1
    FROM emp e
    JOIN employee_hierarchy h ON e.mgr = h.empno
)
SELECT *
FROM employee_hierarchy;

👉 조직도, 메뉴 구조 등 계층적 데이터를 다룰 때 자주 활용됩니다.

5. 실무에서 자주 쓰이는 예제

부서별 매출 집계 후 상세 내역과 조인
동일 조건을 여러 번 쓰는 복잡한 리포트 쿼리 단순화
재귀적 계층 구조 탐색 (조직도, 카테고리 구조 등)

WITH절을 활용하면 SQL을 더 모듈화하고 재사용성을 높일 수 있습니다.

✅ 마무리

오라클 WITH절은 복잡한 SQL을 단순하게 만들고 실행 효율성을 높이는 중요한 기능입니다.

기본적으로 임시 뷰 정의를 통해 쿼리 재사용 가능
여러 개의 서브쿼리를 정의하여 가독성 개선
계층적 데이터 처리를 위한 재귀 WITH절 지원

실무에서 리포트성 쿼리를 작성하거나 복잡한 조건을 관리할 때 매우 유용하므로 반드시 익혀두는 것이 좋습니다.

기록기록