Common Table Expression (CTE) - MySQL

CTE (공통 테이블 식) - 복잡한 쿼리를 가독성 좋고 재사용 가능하게 만들기

 

- 구조

WITH cte_name AS (
	SELECT ...
    FROM ...
    WHERE ...
)

SELECT *
FROM cte_name

 

 

- 왜 사용하는 가?

• 복잡한 서브쿼리를 WITH절로 분리해서, 쿼리의 의도와 흐름을 명확하게 보여주어 가독성을 향상시킴
• 하나의 CTE를 여러 번 JOIN 하거나 SELECT에 사용할 수 있어 코드의 중복을 줄여줌 - 재사용성 향상
• 재귀 쿼리 작성 가능 -> 계층 구조나 경로 탐색 문제를 처리할 수 있음. 예를 들어 조직도, 친구의 친구 찾기

 

- 예시

sales 테이블에서

• 최근 30일 동안의 판매 데이터를 먼저 필터링하고,
• 그 중에서 제품별 총 판매량이 1000 이상인 제품만 조회해보자

WITH recent_sales AS (
	SELECT *
    FROM sales
    WHERE order_date >= CURDATE() - INTERVAL 30 DAY
),

filtered_sales AS (
	SELECT
    	product_id,
        SUM(quantity) AS total_quantity
    FROM recent_sales
    GROUP BY product_id
)

SELECT *
FROM filtered_sales
WHERE total_quantity >= 1000

 

---

 

재귀 CTE

자기 자신을 참조하는 CTE로, 반복적 / 계층적 구조를 탐색할 때 사용한다.

 

예를 들어

• 조직도 (직원 - 상사)
• 카테고리 트리 (대분류 - 소분류)
• 친구의 친구 탐색
• 숫자 생성 (1부터 100까지)
• 경로 탐색

 

- 구조

WITH RECURSIVE cte_name AS (
	-- anchor member (기본값, 시작점)
    SELECT ...
    
    UNION ALL
    
    -- recursive member (자기 자신 호출)
    SELECT ...
    FROM cte_name
    JOIN ... -- 자기 자신과 연결
)

SELECT * FROM cte_name

 

- 예제

1. 1 ~ 5까지 숫자 출력하기

WITH RECURSIVE number AS (
	SELECT 1 AS n -- 시작값
    
    UNION ALL
    
    SELECT n + 1
    FROM numbers
    WHERE n < 5 -- 종료조건
)

SELECT * FROM numbers

 

🔍 작동 방식

1. 첫 줄에서 n = 1을 생성
2. 그 다음 줄에서 n = 2 → 3 → 4 → 5까지 반복
3. n < 5 조건 때문에 종료됨

 

2. 직원 조직도 - Alice의 모든 후임 직원 찾기

-- 테이블

employee
| emp_id | name     | manager_id |
|--------|----------|------------|
| 1      | Alice    | NULL       |
| 2      | Bob      | 1          |
| 3      | Charlie  | 2          |
| 4      | David    | 2          |
| 5      | Eva      | 3          |

 

WITH RECURSIVE hierarchy AS (
	-- 시작값 : Alice 부터 시작
    SELECT emp_id, name, manager_id, 1 AS level
    FROM employee
    WHERE name = 'Alice'
    
    UNION ALL
    
    -- 부하 찾기
    SELECT e.emp_id, e.name, e.manager_id, h.level + 1
    FROM employee e # 직원 테이블
    JOIN hierarchy h # 상사의 테이블
    ON e.manager_id = h.emp_id # 상사의 직원 번호가 부하의 매니저 번호와 같은 것을 기준으로 
)

SELECT * FROM hierarchy

 

출력 결과 :

emp_id	name	mager_id	level
1	Alice	NULL	1
2	Bob	1	2
3	Charlie	2	3
4	David	2	3
5	Eva	3	4