CTE(Common Table Expression)

CTE (Common Table Expression)는 SQL에서 복잡한 쿼리를 더 가독성 있게 작성하고, 재사용할 수 있도록 도와주는 임시 결과 집합입니다.

 

임시 결과 집합(Temporary Result Set)은 SQL에서 실행 중 특정 연산을 수행하기 위해 생성되는, 일시적인 데이터 집합을 의미합니다. 즉, 쿼리가 실행되는 동안 메모리에 저장되며, 쿼리 실행이 완료되면 사라지는 데이터입니다.

 

CTE는 WITH 키워드를 사용하여 정의되며, 이를 통해 다음과 같은 장점을 얻을 수 있습니다.

 

1. 가독성 향상

쿼리의 중첩을 줄여 가독성을 높일 수 있음.

서브쿼리를 여러 번 사용해야 하는 경우 CTE로 대체하면 SQL이 더 명확해짐.

 

2. 재사용성 증가

동일한 서브쿼리를 여러 번 사용할 때 CTE를 정의하면 중복을 줄이고 유지보수를 쉽게 할 수 있음.

 

3. 재귀 쿼리 지원

CTE는 재귀적으로 사용할 수 있어 트리 구조(예: 계층적 데이터, 조직도, 카테고리 구조) 조회에 유용함.

 

4. 임시 뷰 역할

쿼리 내에서만 사용하는 임시 뷰처럼 동작하여 복잡한 데이터 변환 작업을 간결하게 수행할 수 있음.

 

CTE 문법 및 사용 예시

CTE 기본 문법은 다음과 같습니다. 

WITH cte_name (column1, column2, ...) AS (
    SELECT column1, column2, ...
    FROM some_table
    WHERE conditions
)
SELECT *
FROM cte_name;

 

문법만 봐서는 어떻게 사용하는지, 왜 사용하는지 감이 잘 안오니 예제를 통해 이해해보겠습니다.

 

CTE를 사용해 상품의 카테고리별 총 매출액을 조회하는 쿼리입니다.

WITH sales_data AS (
    SELECT p.category, o.quantity * p.price AS total_sales
    FROM orders o
    JOIN products p ON o.product_id = p.id
)
SELECT category, SUM(total_sales) AS total_revenue
FROM sales_data
GROUP BY category;

 

 

 

더 복잡한 쿼리를 예시로 들어보겠습니다.

평균 주문 금액 이상으로 구매한 고객과, 이하로 구매한 고객을 조회하고싶을 때,

다음과 같은 쿼리로 조회할 수 있습니다.

하지만 평균 구매 금액 계산을 두 번 반복해야해서 비효율적일 수 있습니다.

-- 평균 구매 금액 계산을 두 번 반복 (비효율적)
SELECT customer_id, total_spent
FROM orders
WHERE total_spent >= (SELECT AVG(total_spent) FROM orders);  -- 평균 이상 구매 고객 조회

UNION ALL

SELECT customer_id, total_spent
FROM orders
WHERE total_spent < (SELECT AVG(total_spent) FROM orders);   -- 평균 이하 구매 고객 조회

 

CTE를 사용할 경우 평균 구매 금액을 한 번만 계산하고, 이를 여러 쿼리에서 재사용할 수 있습니다.

WITH avg_spent AS (
    SELECT AVG(total_spent) AS avg_purchase FROM orders
)
SELECT customer_id, total_spent
FROM orders, avg_spent
WHERE total_spent >= avg_spent.avg_purchase  -- 평균 이상 구매 고객 조회

UNION ALL

SELECT customer_id, total_spent
FROM orders, avg_spent
WHERE total_spent < avg_spent.avg_purchase;  -- 평균 이하 구매 고객 조회

 

재귀적(Recursive) CTE 

CTE의 큰 장점 중 하나는 재귀적으로 사용할 수 있다는 것입니다.

 

재귀 CTE의 기본 문법은 다음과 같습니다.

WITH RECURSIVE cte_name AS (
    -- 1. 기본(Anchor) 쿼리: 재귀의 시작점
    SELECT column1, column2, ...
    FROM table_name
    WHERE 조건

    UNION ALL

    -- 2. 재귀(Recursive) 쿼리: 자기 자신을 참조하여 반복 실행
    SELECT column1, column2, ...
    FROM table_name
    JOIN cte_name ON table_name.foreign_key = cte_name.primary_key
    WHERE 조건
)
SELECT * FROM cte_name;

 

이러한 재귀 쿼리는 자기 참조 테이블을 조회할 때 매우 유용합니다.

 

 

예를 들어, 상품의 카테고리 테이블이 자기 참조 구조로 되어 있다고 가정해 보겠습니다.

자기 참조 테이블이란, 자기 자신을 외래 키로 참조하는 테이블을 의미합니다.

 

이러한 구조에서 하위 카테고리가 계속해서 추가된다면, 쿼리 조회가 매우 복잡해질 수 있습니다.

 

이때, CTE를 활용하면 효율적이고 간단하게 계층 구조를 조회할 수 있습니다.

예를 들어, ‘clothes’ 카테고리의 모든 하위 카테고리를 조회하려면 다음과 같은 CTE 쿼리를 사용할 수 있습니다.

WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id
    FROM category
    WHERE name = 'clothes'  -- 조회하려는 최상위 카테고리
    
    UNION ALL

    -- 재귀적으로 하위 카테고리를 조회
    SELECT c.id, c.name, c.parent_id
    FROM category c
    INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;