CTE(Common Table Expression)란?

 

📌 1. CTE(Common Table Expression)란?

SQL에서 임시 결과 집합을 이름 붙여 재사용할 수 있는 쿼리

✔️ 장점

• 쿼리를 읽기 쉽게 구조화 가능
  
• 중복 쿼리 제거 → 유지보수 용이
  
• 재귀 쿼리 구현 가능 (계층형/트리 구조)

---

📌2. CTE 문법

WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition

)

SELECT *
FROM cte_name;

 

✔️ 설명

• WITH 뒤에 CTE 이름(cte_name) 지정
  
• 괄호 안에 SELECT 문
  
• 마지막에는 CTE를 사용하는 쿼리 필요

---

📌 3. 여러 CTE 연결

WITH
cte1 AS (
    SELECT 1 AS val
),
cte2 AS (
    SELECT val+1 AS val2
    FROM cte1
)

SELECT * FROM cte2;

 

 

✔️  콤마(,)로 여러 CTE를 나열 가능
⚠️  DB별 주의 사항

• DB2, SQL Server, PostgreSQL, MySQL8.0 이상: 지원
• MySQL < 8.0: CTE 지원 X → 서브쿼리로 대체

---

📌 4. 재귀 CTE

✔️ 목적

• 트리 구조, 계층형 데이터 처리
  
• 부모-자식 관계 등 반복 조회
  
  

📖 문법 예시 (DB2 / SQL Server / PostgreSQL)

WITH RECURSIVE menu_tree AS (
    -- 루트 메뉴
    SELECT menu_id, menu_name, '' AS parent_id, 1 AS lvl
    FROM menu
    WHERE parent_id IS NULL   
    UNION ALL    
    -- 하위 메뉴
    SELECT m.menu_id, m.menu_name, p.menu_id, p.lvl + 1
    FROM menu m
    JOIN menu_tree p ON m.parent_id = p.menu_id
)

SELECT * FROM menu_tree;

✔️ 루트: 계층의 시작점

✔️ UNION ALL: 하위 레벨과 합치기
✔️ 재귀 종료 조건: JOIN이 안 되는 경우 자동 종료

✔️ LVL로 깊이 관리 가능

---

📌 5. CTE 활용 예시

✅ 5-1. 중간 계산 결과 저장

WITH total_sales AS (
    SELECT employee_id, SUM(sale_amount) AS total
    FROM sales
    GROUP BY employee_id
)

SELECT *
FROM total_sales
WHERE total > 10000;

• 복잡한 집계 쿼리를 분리해서 가독성 향상
  
  

✅ 5-2. 중복 제거 + 재사용

WITH recent_orders AS (
    SELECT order_id, customer_id
    FROM orders
    WHERE order_date > '2025-01-01'
)

SELECT c.customer_name, r.order_id
FROM customers c
JOIN recent_orders r ON c.customer_id = r.customer_id;

• 서브쿼리 반복 제거
  
  

✅ 5-3. 계층형 조직도 조회

WITH RECURSIVE org_tree AS (
    SELECT emp_id, emp_name, mgr_id, 1 AS lvl
    FROM employee
    WHERE mgr_id IS NULL
    UNION ALL
    SELECT e.emp_id, e.emp_name, e.mgr_id, o.lvl + 1
    FROM employee e
    JOIN org_tree o ON e.mgr_id = o.emp_id
)

SELECT * FROM org_tree;

---

📌 6. 주의사항

✔️ 컬럼 개수 일치: UNION 또는 재귀 CTE에서는 루트와 하위 SELECT 컬럼 개수 + 타입 일치 필요

✔️ ORDER BY 사용 제한: CTE 내부에서는 ORDER BY 사용 X, 외부 SELECT에서 정렬

✔️ 재귀 CTE 종료 조건: 한 루프 주의 → JOIN 조건, 레벨 제한 필요

✔️ DB별 차이

• DB2: RECURSIVE 생략 가능
• MySQL/PostgreSQL: 반드시 WITH RECURSIVE
• SQL Server: WITH만으로 재귀 가능

---

7️⃣ 요약

구분	특징	예시
기본 CTE	임시 테이블, 가독성 향상	WITH cte AS (SELECT ...) SELECT * FROM cte
여러 CTE	콤마로 연결 가능	WITH cte1 AS(...), cte2 AS(...) SELECT * FROM cte2
재귀 CTE	계층형 데이터 처리	WITH RECURSIVE cte AS (...) UNION ALL ...
주의	컬럼 개수, 타입, ORDER BY, DB별 문법	항상 확인 필요

 

💡 TIP

• 재귀 CTE는 항상 루트 + 하위 SELECT가 컬럼 개수 동일해야 함
• 복잡한 URL/파라미터 조합 같은 건 CTE 밖에서 처리하거나, 불필요하면 아예 제거