[Oracle] 오라클 WITH 절: 서브쿼리 팩토링의 강력한 도구

오라클 데이터베이스에서 WITH 절은 서브쿼리 팩토링(Subquery Factoring)이라고도 불리는 강력한 SQL 기능입니다. 이 기능을 사용하면 복잡한 쿼리를 더 읽기 쉽고 유지보수하기 쉬운 형태로 작성할 수 있습니다. 이 포스트에서는 WITH 절의 개념, 장점, 그리고 다양한 사용 예제를 살펴보겠습니다.

WITH 절이란?

WITH 절은 메인 쿼리 앞에 위치하여 하나 이상의 서브쿼리를 정의할 수 있게 해 줍니다. 이렇게 정의된 서브쿼리는 마치 임시 뷰처럼 메인 쿼리 내에서 참조될 수 있습니다.

WITH 절의 장점

가독성 향상: 복잡한 쿼리를 논리적인 부분으로 나눠 이해하기 쉽게 만듭니다.
성능 개선: 동일한 서브쿼리가 여러 번 사용될 경우, 한 번만 실행되어 결과를 재사용할 수 있습니다.
재사용성: 정의된 서브쿼리를 여러 번 참조할 수 있습니다.
복잡성 감소: 중첩된 서브쿼리를 평면화하여 쿼리의 복잡성을 줄일 수 있습니다.

기본 문법

WITH 
    subquery1_name AS (SELECT ...),
    subquery2_name AS (SELECT ...)
SELECT ...
FROM ...
WHERE ...

예제와 설명

1. 기본 사용 예제

WITH 
    dept_avg_salary AS (
        SELECT department_id, AVG(salary) as avg_salary
        FROM employees
        GROUP BY department_id
    )
SELECT e.employee_id, e.first_name, e.last_name, e.salary, 
       d.department_name, das.avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN dept_avg_salary das ON e.department_id = das.department_id
WHERE e.salary > das.avg_salary;

이 예제에서는 dept_avg_salary라는 서브쿼리를 정의하여 각 부서의 평균 급여를 계산합니다. 그런 다음 메인 쿼리에서 이를 사용하여 자신의 부서 평균보다 높은 급여를 받는 직원들을 조회합니다.

2. 다중 서브쿼리 사용

WITH 
    dept_avg_salary AS (
        SELECT department_id, AVG(salary) as avg_salary
        FROM employees
        GROUP BY department_id
    ),
    high_salary_employees AS (
        SELECT e.employee_id, e.first_name, e.last_name, e.salary, e.department_id
        FROM employees e
        JOIN dept_avg_salary das ON e.department_id = das.department_id
        WHERE e.salary > das.avg_salary
    )
SELECT hse.*, d.department_name
FROM high_salary_employees hse
JOIN departments d ON hse.department_id = d.department_id;

이 예제에서는 두 개의 서브쿼리를 정의합니다. 첫 번째는 부서별 평균 급여를 계산하고, 두 번째는 그 결과를 사용하여 평균 이상의 급여를 받는 직원을 찾습니다. 메인 쿼리에서는 이 결과를 사용하여 최종 출력을 생성합니다.

3. 재귀적 WITH 절 사용

WITH RECURSIVE
    emp_hierarchy(employee_id, first_name, last_name, manager_id, level) AS (
        SELECT employee_id, first_name, last_name, manager_id, 1
        FROM employees
        WHERE manager_id IS NULL
        UNION ALL
        SELECT e.employee_id, e.first_name, e.last_name, e.manager_id, eh.level + 1
        FROM employees e
        JOIN emp_hierarchy eh ON e.manager_id = eh.employee_id
    )
SELECT *
FROM emp_hierarchy
ORDER BY level, employee_id;

이 예제는 재귀적 WITH 절을 사용하여 직원의 계층 구조를 표현합니다. 최상위 관리자부터 시작하여 모든 하위 직원을 레벨별로 표시합니다.

주의사항

WITH 절은 쿼리의 가독성을 높이지만, 과도한 사용은 오히려 복잡성을 증가시킬 수 있습니다.
대량의 데이터를 처리하는 경우, WITH 절의 서브쿼리 결과가 메모리에 저장되므로 메모리 사용량에 주의해야 합니다.
재귀적 WITH 절을 사용할 때는 무한 루프에 빠지지 않도록 종료 조건을 명확히 해야 합니다.
오라클 버전에 따라 WITH 절의 지원 범위가 다를 수 있으므로 사용 전 확인이 필요합니다.

WITH 절은 복잡한 쿼리를 더 관리하기 쉽고 이해하기 쉬운 형태로 작성할 수 있게 해주는 강력한 도구입니다. 적절히 사용하면 SQL 코드의 가독성과 유지보수성을 크게 향상시킬 수 있으며, 때로는 성능 개선에도 도움이 될 수 있습니다. 복잡한 비즈니스 로직을 SQL로 표현해야 할 때 WITH 절의 사용을 고려해 보시기 바랍니다.

개발