[SQL] CTE(Common Table Expressions)란?

CTE(Common Table Expressions)란?

CTE는 SQL에서 재사용 가능한 임시 결과 집합을 정의하기 위해 사용되는 WITH 절입니다. 주로 복잡한 쿼리를 단순화하거나, 가독성을 높이고 유지보수를 쉽게 하기 위해 사용됩니다.

---

CTE의 기본 구조

WITH CTE명칭 (열1, 열2, ...) AS (
    CTE를 정의하는 쿼리
)
SELECT ...
FROM CTE명칭;

주요 특징:

1. WITH 키워드로 시작합니다.
2. CTE는 쿼리 내에서 임시 테이블처럼 사용됩니다.
3. CTE는 원본 데이터베이스에 저장되지 않으며, 쿼리가 실행될 때마다 생성됩니다.
4. 복잡한 쿼리를 여러 단계로 나누어 가독성을 향상시킬 수 있습니다.

---

CTE 사용 예제

예제 테이블: 판매

판매ID	직원ID	매출액	날짜
1	101	100	2024-11-20
2	102	200	2024-11-21
3	101	300	2024-11-22
4	103	400	2024-11-23

1. 기본 CTE 사용

특정 직원별 총 매출액을 계산:

WITH 직원별매출 AS (
    SELECT 직원ID, SUM(매출액) AS 총매출
    FROM 판매
    GROUP BY 직원ID
)
SELECT *
FROM 직원별매출;

직원ID	총매출
101	400
102	200
103	400

---

CTE의 주요 활용 사례

1. 복잡한 쿼리 단순화

중첩된 서브쿼리를 CTE로 분리하여 간결하게 표현:

-- 기존 서브쿼리 사용 방식
SELECT 직원ID, 총매출
FROM (
    SELECT 직원ID, SUM(매출액) AS 총매출
    FROM 판매
    GROUP BY 직원ID
) AS 서브쿼리
WHERE 총매출 > 300;

-- CTE 사용 방식
WITH 직원별매출 AS (
    SELECT 직원ID, SUM(매출액) AS 총매출
    FROM 판매
    GROUP BY 직원ID
)
SELECT 직원ID, 총매출
FROM 직원별매출
WHERE 총매출 > 300;

---

2. 여러 CTE 결합

여러 CTE를 정의하고, 이를 조합하여 결과를 도출:

WITH 직원별매출 AS (
    SELECT 직원ID, SUM(매출액) AS 총매출
    FROM 판매
    GROUP BY 직원ID
),
최고매출직원 AS (
    SELECT 직원ID, 총매출
    FROM 직원별매출
    WHERE 총매출 = (SELECT MAX(총매출) FROM 직원별매출)
)
SELECT *
FROM 최고매출직원;

직원ID	총매출
101	400
103	400

---

3. 재귀 CTE

CTE는 재귀적으로 사용할 수도 있습니다. 재귀 CTE는 계층적 데이터를 처리하거나, 반복적으로 계산이 필요한 작업을 수행할 때 사용됩니다.

예제 테이블: 직원

직원ID	상사ID
1	NULL
2	1
3	1
4	2
5	2

계층 구조 탐색 (부하 직원 찾기):

WITH 직원계층 AS (
    SELECT 직원ID, 상사ID, 1 AS 레벨
    FROM 직원
    WHERE 상사ID IS NULL
    UNION ALL
    SELECT e.직원ID, e.상사ID, c.레벨 + 1
    FROM 직원 e
    INNER JOIN 직원계층 c
    ON e.상사ID = c.직원ID
)
SELECT *
FROM 직원계층
ORDER BY 레벨, 직원ID;

직원ID	상사ID	레벨
1	NULL	1
2	1	2
3	1	2
4	2	3
5	2	3

---

CTE vs 서브쿼리 vs 임시 테이블

특징	CTE	서브쿼리	임시 테이블
가독성	높음	복잡한 쿼리일 경우 낮음	중간 수준
재사용성	쿼리 내에서 여러 번 재사용 가능	한 번 사용 후 재사용 불가능	필요 시 재사용 가능
영향 범위	쿼리 범위 내에서만 유효	쿼리 내에서만 유효	세션 범위 내에서 유효
계층적 데이터 처리	재귀 CTE로 가능	어려움	불가능
성능	SQL 엔진 최적화 적용 가능	SQL 엔진 최적화 적용 가능	디스크에 저장되므로 상대적으로 느림

---

CTE의 장점

1. 가독성: 복잡한 쿼리를 읽기 쉽고 논리적으로 분리.
2. 재사용성: 동일한 데이터를 여러 번 사용할 때 효율적.
3. 재귀 처리: 계층 구조나 반복 계산을 간단히 처리 가능.
4. 임시 데이터 처리: 서브쿼리보다 간결하고 유지보수 쉬움.

CTE는 쿼리의 간소화와 효율적인 데이터 분석에 매우 유용하므로, 복잡한 데이터 처리 작업에서 적극적으로 활용할 수 있습니다! 🚀