서브쿼리의 진화, CTE(Common Table Expression) 완전 정리!

CTE(Common Table Expression)는 SQL에서 쿼리의 가독성을 높이고 복잡한 쿼리를 구조적으로 작성할 수 있도록 도와주는 기능입니다. 특히 재귀적 쿼리나 여러 단계로 나뉘어진 연산이 필요한 경우 유용하게 사용됩니다.

---

✅ 왜 CTE가 필요한가?

SQL을 사용하다 보면, 서브쿼리(subquery)가 중첩되거나 동일한 쿼리를 반복 작성해야 하는 상황이 자주 발생합니다.
이럴 때 코드의 가독성은 떨어지고 유지보수가 어려워지게 됩니다.
CTE는 이러한 문제를 해결하고자 도입된 기능으로, 쿼리 내에서 임시 결과 테이블을 정의하고, 이후 쿼리에서 그 테이블을 사용하는 방식입니다.

💡 주요 기술적 기반:

• SQL 표준 (특히 WITH 구문 사용)
• 임시 테이블과 유사하나 메모리 상에서만 존재함
• 뷰(view)와 비슷하지만, 범위(scope)는 하나의 쿼리 내로 한정됨

---

🔍 CTE의 기초와 활용

1. CTE 기본 문법

WITH cte_name AS (
  SELECT column1, column2
  FROM table_name
  WHERE condition
)
SELECT *
FROM cte_name
WHERE another_condition;

• WITH 키워드를 사용해 시작
• cte_name은 가상의 테이블명
• CTE는 바로 이어지는 SELECT문에서 사용 가능

---

2. CTE 활용 예제

💡 예제 1: 중복 제거 및 정렬 처리

WITH recent_sales AS (
  SELECT product_id, sale_date, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date DESC) AS rn
  FROM sales
)
SELECT product_id, sale_date
FROM recent_sales
WHERE rn = 1;

각 상품에 대해 가장 최근 판매일만 추출하는 쿼리입니다.

 

---

💡 예제 2: 여러 개의 CTE 활용

WITH monthly_sales AS (
  SELECT EXTRACT(MONTH FROM sale_date) AS month, SUM(amount) AS total
  FROM sales
  GROUP BY EXTRACT(MONTH FROM sale_date)
),
high_sales AS (
  SELECT *
  FROM monthly_sales
  WHERE total > 100000
)
SELECT * FROM high_sales;

CTE 간 중첩 사용도 가능하며, 단계별로 결과를 나누어 처리할 수 있습니다.

---

💡 예제 3: 재귀 CTE (계층 구조)

WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;

조직도, 폴더 구조와 같이 계층적 데이터를 처리할 때 강력한 도구입니다.

---

🧾 CTE가 주는 이점

이점 설명

가독성 향상	복잡한 쿼리를 단계적으로 분해
중복 제거	동일한 서브쿼리 반복 방지
유지보수 용이	디버깅, 수정 시 부담 감소
재귀 처리 가능	계층적 데이터 분석에 유리

---

✅ 마무리

CTE는 단순한 SQL 구문 이상의 가치가 있는 기능으로, 복잡한 비즈니스 로직을 SQL 레벨에서 깔끔하게 처리할 수 있게 해주는 중요한 도구입니다. 특히 데이터 분석, 보고서 작성, 계층 구조 관리 등 다양한 영역에서 유용하게 쓰일 수 있으며, 모던 SQL 개발에서 필수적으로 익혀야 할 개념 중 하나입니다.

---