• 학습일자 : 2024.02.24

WITH문이란?

• WITH문은 임시 테이블 또는 가상 테이블라 생각하면 됨
• 반복되는 서브쿼리 블록을 하나의 WITH 절 블록으로 만들어서 사용하거나, 긴 문장의 서브 쿼리를 WITH 절로 만들어서 SELECT 문의 가독성을 높일 수 있음
• WITH AS를 여러 개 사용하기 위해서는 쉼표를 붙여서 여러 개 선언하면 됨
• 데이터 처리와 분석을 단계별로 나누어 처리할 수 있게 해주어, 큰 데이터셋을 다룰 때 효율성을 높여줌

예제

예제 테이블 구조 및 데이터

employees와 departments 두 테이블을 사용하는 샘플 시나리오를 고려하여 Oracle의 WITH 문 사용법을 다양한 복잡도로 설명합니다.

테이블: employees

employee_id	first_name	last_name	department_id	salary
1	John	Doe	1	50000
2	Jane	Smith	2	60000
3	Bob	Johnson	1	70000
4	Sarah	Miller	3	55000

테이블: department

department_id	department_name
1	영업
2	엔지니어링
3	인사

WITH 문을 사용한 예제 쿼리

예제 1

직원 세부 정보를 가져오기

WITH EmployeeDetails AS (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
)
SELECT *
FROM EmployeeDetails
WHERE salary > 55000;

예제 2: 다른 테이블과 조인

WITH DepartmentEmployees AS (
    SELECT e.employee_id, e.first_name, e.last_name, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
)
SELECT *
FROM DepartmentEmployees
WHERE department_name = '영업';

예제 3: 데이터 집계

부서별 평균 급여 계산

WITH DepartmentSalary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.department_name, ds.avg_salary
FROM DepartmentSalary ds
JOIN departments d ON ds.department_id = d.department_id;

예제 4: 재귀

계층적 데이터에 유용한 재귀입니다.

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT employee_id, first_name, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.first_name, eh.depth + 1
    FROM employees e
    JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT *
FROM EmployeeHierarchy;

참고: 이 쿼리는 manager_id 열이 존재한다고 가정하며 관리 구조에 기반한 계층을 구성합니다.

예제 5: 단일 쿼리에서 여러 CTE 사용

복잡한 데이터 분석을 위해 여러 CTE를 결합합니다.

WITH SalesDepartment AS (
    SELECT employee_id
    FROM employees
    WHERE department_id = 1
), HighEarningSales AS (
    SELECT employee_id
    FROM SalesDepartment
    WHERE salary > 65000
)
SELECT e.first_name, e.last_name
FROM employees e
JOIN HighEarningSales hs ON e.employee_id = hs.employee_id;

느낀점

실무에서 WITH문을 상당히 많이 사용한다.

특히, WITH문 없이 JOIN문을 사용하려고 할 때, JOIN에 얽힌 테이블의 관계수가 많을 경우 WITH문으로 짝을 2개 정도씩 묶은 이후 WITH문 밖에서 메인 쿼리를 진행하게 하여 가독성이 높아지게 한다.

이러한 기능이 있어 매우 편리한 것 같다.