[Oracle] 오라클 단일, 다중 With절 사용방법과 유스케이스

오라클의 WITH절은 중첩된 쿼리를 분리하여 가독성을 향상시키거나, 재귀호출, 성능최적화를

위해 다양하게 사용됩니다. 또한, 임시(TEMP)테이블로 사용되어 쿼리의 이해를 돕는 역할로도 사용됩니다.

 

이번 블로그에서 WITH절의 다양한 사용방법과 유스케이스를 간략히 살펴봅니다.

---

1. WITH 절 기본 사용방법

 WITH CUSTOMER AS (
    SELECT 'C001' AS customer_id, 'Choi' AS customer_name FROM dual
    UNION ALL
    SELECT 'C002' AS customer_id, 'Kim' AS customer_name FROM dual
)
SELECT * FROM CUSTOMER;

CUSTOMER_ID|CUSTOMER_NAME|
-----------+-------------+
C001       |Choi         |
C002       |Kim          |

• WITH절에 CUSTOMER라는 임시테이블을 생성했습니다.

2. WITH절 여러개의 테이블 사용방법

위에서 생성한 CUSTOMER테이블에 1:N 관계테이블인 ORDER_INFO테이블을 추가로 생성해보겠습니다.

WITH
    CUSTOMER AS (
        SELECT 'C001' AS customer_id, 'Choi' AS customer_name FROM dual
        UNION ALL
        SELECT 'C002' AS customer_id, 'Kim' AS customer_name FROM dual
    ),
    ORDER_INFO AS (
        SELECT 'O001' AS order_id, 'C001' AS customer_id, '2023-07-01' AS order_date FROM dual
        UNION ALL
        SELECT 'O002' AS order_id, 'C001' AS customer_id, '2023-07-05' AS order_date FROM dual
        UNION ALL
        SELECT 'O003' AS order_id, 'C002' AS customer_id, '2023-07-02' AS order_date FROM dual
    )
SELECT c.customer_id,
    c.customer_name,
    o.order_id,
    o.order_date
FROM CUSTOMER c
JOIN ORDER_INFO o ON c.customer_id = o.customer_id;

• 위와 같이 WITH절 내에 콤마로 연결하여 임시테이블을 추가할 수 있습니다.

3. WITH 활용방법

데이터 변환 및 가공

복잡한 데이터의 변환과 가공을 WITH절로 나누어서 가독성을 높임.

WITH
    sales AS (
        SELECT product_id, SUM(quantity) AS total_sales
        FROM order_details
        GROUP BY product_id
    ),
    sales_percentage AS (
        SELECT product_id, total_sales, total_sales / (SELECT SUM(total_sales) FROM sales) AS percentage
        FROM sales
    )
SELECT p.product_name, s.total_sales, sp.percentage
FROM products p
JOIN sales s ON p.product_id = s.product_id
JOIN sales_percentage sp ON s.product_id = sp.product_id;

계층 데이터 재귀조회

WITH RECURSIVE category_hierarchy AS (
    SELECT category_id, category_name, parent_category_id, 1 AS level
    FROM categories
    WHERE parent_category_id IS NULL
    UNION ALL
    SELECT c.category_id, c.category_name, c.parent_category_id, ch.level + 1
    FROM categories c
    INNER JOIN category_hierarchy ch ON c.parent_category_id = ch.category_id
)
SELECT category_id, LPAD(' ', (level-1)*4, ' ') || category_name AS category_name
FROM category_hierarchy
ORDER BY category_id;

테이블간 데이터 결합

다중 테이블을 사용하여 데이터 속성을 분리하고 결합합니다.

WITH
    male_students AS (
        SELECT student_id, student_name
        FROM students
        WHERE gender = 'Male'
    ),
    female_students AS (
        SELECT student_id, student_name
        FROM students
        WHERE gender = 'Female'
    ),
    paired_students AS (
        SELECT m.student_id AS male_id, m.student_name AS male_name, f.student_id AS female_id, f.student_name AS female_name
        FROM male_students m
        JOIN female_students f ON m.student_id = f.student_id
    )
SELECT *
FROM paired_students;