[MySQL] WITH 구문, CTE(Common Table Expression)

 

MySQL에서 CTE (Common Table Expression)는 WITH 구문을 사용하여 임시 테이블을 생성하는 방법이다.
CTE는 복잡한 쿼리를 보다 가독성 높게 작성할 수 있도록 도와주며, 쿼리의 재사용성과 유지보수성을 높이는 데 유용하다.

---

✅ WITH 구문의 기본 구조

WITH temporary_table AS (
    SELECT column1, column2
    FROM some_table
    WHERE condition
)
SELECT column1, column2
FROM temporary_table;

1. WITH 절을 사용하여 temporary_table이라는 임시 테이블을 생성한다.
2. temporary_table을 일반 테이블처럼 SELECT 문에서 사용하여 데이터를 조회할 수 있다.

 

---

✅ WITH 구문을 사용하는 이유

1. 반복되는 서브쿼리를 줄일 수 있음

• 동일한 서브쿼리를 여러 번 사용할 경우 WITH 구문을 활용하면 성능이 향상된다.
• 쿼리의 실행 속도를 높이고, 불필요한 데이터 처리를 줄일 수 있다.

2. 쿼리의 가독성이 좋아짐

• 복잡한 SQL 문을 단순한 단계로 나누어 작성할 수 있다.
• 여러 개의 WITH 절을 사용하여 여러 개의 서브쿼리를 논리적으로 정리할 수 있다.

3. 재귀 쿼리에 사용 가능

• MySQL 8.0부터 WITH RECURSIVE를 지원하여 계층적인 데이터(예: 조직도, 트리 구조) 처리가 가능하다.

 

---

✅ WITH RECURSIVE 구문 (재귀 CTE)

계층적 데이터(트리 구조 등)를 처리할 때 유용한 기능이다.
예를 들어, 직원과 상사의 계층 구조를 조회하는 경우 다음과 같은 방식으로 사용할 수 있다.

WITH RECURSIVE employee_hierarchy AS (
    -- Anchor member (기본 데이터)
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL  -- 최상위 관리자 찾기

    UNION ALL

    -- Recursive member (재귀적으로 직원-관리자 관계 찾기)
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

🔹 실행 과정:

1. manager_id IS NULL인 최상위 관리자를 찾는다. (level = 1)
2. 해당 관리자를 기준으로 하위 직원들을 재귀적으로 찾는다.
3. 각 직원이 속한 계층을 level로 표시하여 계층 구조를 확인할 수 있다.

 

---

✅ WITH 구문 vs 서브쿼리 비교

🚀 서브쿼리를 사용한 경우

SELECT column1, column2
FROM (
    SELECT column1, column2
    FROM some_table
    WHERE condition
) AS temp_table;

• 서브쿼리를 여러 번 사용할 경우, 성능 저하의 원인이 될 수 있다.
• 코드의 가독성이 떨어진다.

🚀 WITH 구문을 사용한 경우

WITH temp_table AS (
    SELECT column1, column2
    FROM some_table
    WHERE condition
)
SELECT column1, column2
FROM temp_table;

• 가독성이 좋아지고, 유지보수가 용이하다.
• 동일한 WITH 절을 여러 번 재사용할 수 있어 성능 향상 효과가 있다.

 

---

✅ 여러 개의 CTE 사용

WITH 절을 여러 개 사용할 수도 있다.

WITH cte1 AS (
    SELECT column1, column2 FROM table1
),
cte2 AS (
    SELECT column3 FROM table2 WHERE column3 IN (SELECT column1 FROM cte1)
)
SELECT * FROM cte2;

• cte1에서 데이터를 가져오고,
• cte2에서 cte1의 데이터를 활용하여 table2의 특정 데이터를 필터링한다.

 

---

 

✅ 정리

• WITH 구문을 사용하면 쿼리의 가독성을 높이고, 성능을 향상시킬 수 있다.
• 동일한 서브쿼리를 여러 번 사용할 때 반복을 줄여 최적화할 수 있다.
• WITH RECURSIVE를 사용하면 계층적인 데이터를 쉽게 처리할 수 있다.
• 여러 개의 CTE를 활용하여 복잡한 쿼리를 단계적으로 구성할 수 있다.