MySQL WITH 절 (CTE) 설명: 구문, 예제, 재귀 쿼리 및 성능 팁

1 1. 소개
2 2. WITH 절의 기본 (Common Table Expressions)
3 3. MySQL WITH 절의 기본 사용법
4 4. 재귀 WITH 절의 실용적인 예시
5 5. WITH 절 사용 시 주의사항 및 고려사항
6 6. 실제 시나리오에서의 실용적인 사용 사례
7 7. 자주 묻는 질문 (FAQ)
8 8. 결론

1. 소개

MySQL은 개발자와 데이터베이스 관리자들이 널리 사용하는 데이터베이스 관리 시스템으로, 강력하고 유연한 SQL 기능을 제공합니다. 그 기능 중 MySQL 8.0에서 도입된 WITH 절(Common Table Expression: CTE)은 SQL 쿼리를 더 읽기 쉽게 만들고 유지 보수를 향상시키는 강력한 도구입니다.

이 문서는 WITH 절을 기본부터 고급 사용까지 설명하며, 초급부터 중급 사용자까지를 대상으로 합니다. 특히 서브쿼리 대체와 재귀 쿼리 구현과 같은 실용적인 주제를 다룹니다.

SQL을 배우고 있거나 일상 업무에서 쿼리 최적화에 어려움을 겪는 분들을 위해, 이 문서는 구체적인 해결책을 제공하고자 합니다. 아래 내용을 따라가며 WITH 절의 기본 개념을 이해하고 실제 상황에 적용해 보세요.

2. WITH 절의 기본 (Common Table Expressions)

WITH 절이란?

WITH 절은 SQL 쿼리 내에서 임시 결과 집합(공통 테이블 표현식, CTE)을 정의하고 이후 쿼리에서 참조할 수 있게 하는 구문입니다. MySQL 8.0부터 지원되며, 복잡한 서브쿼리를 더 명확하고 간결하게 재작성할 수 있게 해줍니다.

예를 들어 서브쿼리를 직접 작성하면 가독성이 떨어지고 전체 쿼리가 길어질 수 있습니다. WITH 절을 사용하면 쿼리를 논리적인 블록으로 나눌 수 있어 이해하기 쉬워집니다.

WITH 절의 기본 구문

아래는 WITH 절의 기본 구문입니다:

WITH table_name AS (
  SELECT column1, column2
  FROM original_table
  WHERE condition
)
SELECT column1, column2
FROM table_name;

이 구문에서는 WITH 뒤에 가상 테이블(공통 테이블 표현식)이 정의되고, 이후 메인 쿼리에서 사용됩니다. 이렇게 하면 반복적으로 사용되는 서브쿼리를 간결하게 표현할 수 있습니다.

서브쿼리 및 뷰와의 차이점

WITH 절은 일시적으로 사용할 수 있는 결과 집합을 생성하며, 서브쿼리와 뷰와는 여러 측면에서 다릅니다.

Feature	WITH Clause	Subquery	View
Scope	Valid only within the query	Usable only where defined	Reusable across the entire database
Persistence	Temporary	Temporary	Permanent
Purpose	Simplifies complex queries	Temporary data extraction	Frequently reused data extraction

WITH 절은 서브쿼리보다 가독성이 높으며, 뷰와 같은 영구 객체를 만들 필요가 없을 때 이상적입니다.

WITH 절 사용의 장점

쿼리 가독성 향상 여러 서브쿼리가 있더라도 WITH 절로 정리하면 구조가 명확해집니다.
재사용성 향상 임시 결과 집합을 정의하면 쿼리 내에서 여러 번 참조할 수 있습니다.
유지 보수성 향상 쿼리를 논리적으로 나눌 수 있어 수정 및 확장이 쉬워집니다.

3. MySQL WITH 절의 기본 사용법

서브쿼리 대체

WITH 절은 복잡한 서브쿼리를 단순화하는 강력한 도구입니다. 서브쿼리를 직접 삽입하면 전체 쿼리가 복잡하고 읽기 어려워지지만, WITH 절을 사용하면 가독성이 향상됩니다.

아래는 WITH 절을 사용해 서브쿼리를 대체하는 기본 예시입니다.

서브쿼리 사용:

SELECT AVG(sales.total) AS average_sales
FROM (
  SELECT SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
) AS sales;

WITH 절 사용:

WITH sales AS (
  SELECT SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT AVG(sales.total) AS average_sales
FROM sales;

이 예시에서는 sales라는 임시 결과 집합을 WITH 절로 정의한 뒤 메인 쿼리에서 참조합니다. 이렇게 하면 전체 쿼리를 이해하기 쉽고 구조가 더 잘 정리됩니다.

다중 공통 테이블 표현식(CTE) 정의

WITH 절을 사용하면 여러 개의 CTE를 정의할 수 있습니다. 이를 통해 복잡한 쿼리를 더욱 모듈화할 수 있습니다.

예시:

WITH 
  sales_per_customer AS (
    SELECT customer_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY customer_id
  ),
  high_value_customers AS (
    SELECT customer_id
    FROM sales_per_customer
    WHERE total_sales > 10000
  )
SELECT customer_id
FROM high_value_customers;

이 예제에서는 sales_per_customer가 고객당 총 매출을 계산하고, 그 결과를 기반으로 high_value_customers가 구매 금액이 높은 고객을 추출합니다. 여러 CTE를 순차적으로 사용하면 쿼리를 단계별로 구성할 수 있습니다.

중첩 CTE 사용

중첩 CTE를 사용하면 보다 복잡한 데이터 작업을 수행할 수 있습니다.

예시:

WITH 
  sales_data AS (
    SELECT product_id, SUM(amount) AS total_sales
    FROM orders
    GROUP BY product_id
  ),
  ranked_sales AS (
    SELECT product_id, total_sales,
           RANK() OVER (ORDER BY total_sales DESC) AS rank
    FROM sales_data
  )
SELECT product_id, total_sales
FROM ranked_sales
WHERE rank <= 5;

이 쿼리에서는 sales_data가 제품별 매출을 집계하고, ranked_sales가 매출량을 기준으로 순위를 부여합니다. 마지막으로 상위 다섯 개 제품을 추출합니다.

실용적인 사용을 위한 핵심 포인트

논리적인 단계로 생각하기 CTE를 단계별로 구축하여 가독성을 높이고 디버깅을 쉽게 합니다.
중간 계산 결과 저장 여러 번 사용되는 계산 결과나 필터링 조건을 CTE에 그룹화하여 코드 중복을 줄입니다.
대용량 데이터셋에 주의 CTE는 임시 결과 집합을 생성하므로 대량 데이터를 다룰 때 성능 영향을 고려해야 합니다.

4. 재귀 WITH 절의 실용적인 예시

재귀 WITH 절이란?

재귀 WITH 절(재귀 CTE)은 공통 테이블 식을 사용하여 자체 참조 쿼리를 반복 실행함으로써 계층형 데이터와 반복 계산을 처리할 수 있게 하는 방법입니다. 재귀 CTE는 MySQL 8.0 이상에서 지원되며, 부모‑자식 관계 및 계층 구조를 다룰 때 특히 유용합니다.

재귀 CTE의 기본 구문

재귀 CTE를 정의하려면 WITH RECURSIVE 키워드를 사용합니다. 기본 구문은 다음과 같습니다:

WITH RECURSIVE recursive_table_name AS (
  initial_query -- starting point of the recursion
  UNION ALL
  recursive_query -- query called recursively
)
SELECT * FROM recursive_table_name;

Initial query : 재귀 프로세스의 첫 번째 데이터 집합을 가져옵니다.
Recursive query : 초기 쿼리 또는 이전 반복 결과를 기반으로 새로운 행을 생성합니다.
UNION ALL : 초기 쿼리와 재귀 쿼리의 결과를 결합합니다.

예시: 계층형 데이터 처리

재귀 CTE는 계층 구조(예: 조직 트리 또는 카테고리 트리)를 가진 데이터를 확장할 때 자주 사용됩니다.

예시: 직원 관리 계층 확장

다음 employees 테이블을 고려해 보세요:

employee_id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Charlie	1
4	David	2

이 데이터를 사용하여 특정 직원부터 시작하는 전체 계층을 조회하는 쿼리를 만들 수 있습니다.

WITH RECURSIVE employee_hierarchy AS (
  -- Initial query: get top-level employees
  SELECT employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive query: get direct reports
  SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  INNER JOIN employee_hierarchy eh
  ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy;

결과:

employee_id	name	manager_id	level
1	Alice	NULL	1
2	Bob	1	2
3	Charlie	1	2
4	David	2	3

이 쿼리에서는 manager_id를 기준으로 부하 직원을 재귀적으로 검색하여 전체 계층을 확장합니다.

재귀 CTE의 제한 사항 및 주의점

종료 조건이 필요함 재귀 쿼리가 종료 조건을 충족하지 않으면 무한 루프가 발생할 수 있습니다. 무한 재귀를 방지하기 위해 항상 적절한 조건을 포함하세요.
성능 영향 재귀 CTE는 대용량 데이터셋에 대해 많은 계산을 수행할 수 있어 실행 시간이 늘어날 수 있습니다. 효율성을 높이기 위해 LIMIT 절과 필터링 조건을 사용하세요.
재귀 깊이 제한 MySQL은 재귀 깊이에 제한이 있으므로 매우 깊은 재귀 처리를 실행할 때 주의가 필요합니다. 이 제한은 max_recursive_iterations 매개변수로 설정할 수 있습니다.

재귀 CTE가 유용한 시나리오

폴더 구조 탐색 : 폴더와 하위 폴더를 재귀적으로 검색합니다.
조직도 작성 : 관리자에서 부하 직원까지의 계층 구조를 시각화합니다.
카테고리 트리 표시 : 계층형 제품 카테고리 또는 태그 구조를 가져옵니다.

재귀 CTE는 이러한 시나리오에 대해 간결한 SQL 쿼리를 작성하면서 가독성을 향상시키는 강력한 방법입니다.

5. WITH 절 사용 시 주의사항 및 고려사항

성능 영향 및 최적화

CTE 재계산 일반적으로 WITH 절로 정의된 CTE는 참조될 때마다 재계산됩니다. 따라서 동일한 CTE를 여러 번 사용하면 쿼리 실행 시간이 증가할 수 있습니다. 예시:
```
WITH sales AS (
  SELECT product_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY product_id
)
SELECT * FROM sales WHERE total_sales > 1000;
SELECT COUNT(*) FROM sales;
```

위 사례에서 sales가 두 번 참조되므로 두 번 계산됩니다. 이를 방지하려면 결과를 여러 번 참조해야 할 경우 임시 테이블에 저장하는 것이 효과적일 수 있습니다.

해결책:

CREATE TEMPORARY TABLE temp_sales AS
SELECT product_id, SUM(amount) AS total_sales
FROM orders
GROUP BY product_id;

SELECT * FROM temp_sales WHERE total_sales > 1000;
SELECT COUNT(*) FROM temp_sales;

복잡한 CTE 분할 WITH 절이 너무 깊게 중첩되면 전체 쿼리가 복잡해지고 디버깅이 어려워질 수 있습니다. 단일 CTE 내의 처리가 과도하게 복잡해지지 않도록 논리를 적절히 분할하는 것이 중요합니다.

대용량 데이터셋에서 WITH 절 사용

WITH 절은 실행 중에 임시 데이터셋을 생성합니다. 대량의 데이터를 처리할 때 메모리나 저장소에 부담을 줄 수 있습니다.

대응 방안:

WHERE 절을 사용한 데이터 필터링 CTE 내부에서 불필요한 데이터를 필터링하여 연산을 줄입니다.

WITH filtered_orders AS (
  SELECT *
  FROM orders
  WHERE order_date > '2023-01-01'
)
SELECT customer_id, SUM(amount)
FROM filtered_orders
GROUP BY customer_id;

LIMIT 절 사용 데이터셋이 큰 경우 LIMIT을 사용해 필요한 데이터만 추출합니다.

MySQL 버전 호환성

MySQL의 WITH 절은 MySQL 8.0 이상에서 지원됩니다. 이전 버전은 WITH 절을 지원하지 않으므로 대안을 고려해야 합니다.

대안:

서브쿼리 사용 WITH 절 대신 서브쿼리를 직접 사용합니다.

SELECT AVG(total_sales)
FROM (
  SELECT customer_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY customer_id
) AS sales;

뷰 생성 재사용 가능한 쿼리가 필요할 경우 뷰를 사용하는 것이 효과적일 수 있습니다.

CREATE VIEW sales_view AS
SELECT customer_id, SUM(amount) AS total_sales
FROM orders
GROUP BY customer_id;

SELECT AVG(total_sales) FROM sales_view;

WITH 절을 올바르게 사용하는 방법

가독성 우선 WITH 절의 목적은 쿼리를 정리하고 가독성을 높이는 것입니다. 과도하게 사용하면 쿼리가 복잡해질 수 있으므로 필요할 때만 사용하세요.

성능 검증 실행 계획(EXPLAIN 명령) 을 확인하고 성능 최적화 방안을 고려합니다.

EXPLAIN
WITH sales AS (
  SELECT product_id, SUM(amount) AS total_sales
  FROM orders
  GROUP BY product_id
)
SELECT * FROM sales WHERE total_sales > 1000;

6. 실제 시나리오에서의 실용적인 사용 사례

매출 데이터 집계

월별 매출 데이터를 집계한 뒤 해당 결과를 사용해 월 평균 매출을 계산하는 예시입니다.

예시: 월별 매출 집계 및 평균 계산

WITH monthly_sales AS (
  SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS sales_month,
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY sales_month
)
SELECT 
  sales_month, 
  total_sales,
  AVG(total_sales) OVER () AS average_sales
FROM monthly_sales;

이 쿼리에서 monthly_sales는 월별 매출을 계산하며, 그 결과를 바탕으로 전체 평균 매출이 계산됩니다. 이를 통해 데이터를 명확하게 정리할 수 있으며 분석을 단순화합니다.

특정 조건에 기반한 데이터 필터링

복잡한 필터링 로직을 WITH 절로 분리함으로써 가독성을 향상시킬 수 있습니다.

예시: 고액 지출 고객 목록 생성

WITH customer_totals AS (
  SELECT 
    customer_id, 
    SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT 
  customer_id, 
  total_spent
FROM customer_totals
WHERE total_spent > 100000;

이 쿼리에서 customer_totals는 고객별 총 구매 금액을 계산하며, 지정된 조건을 충족하는 고객이 추출됩니다.

계층 데이터 분석

조직 구조나 카테고리와 같은 계층 데이터를 분석할 때, 재귀 WITH 절이 매우 유용합니다.

예시: 직속 및 간접 부하 목록 검색

WITH RECURSIVE employee_hierarchy AS (
  SELECT 
    employee_id, 
    name, 
    manager_id, 
    1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT 
    e.employee_id, 
    e.name, 
    e.manager_id, 
    eh.level + 1
  FROM employees e
  INNER JOIN employee_hierarchy eh
  ON e.manager_id = eh.employee_id
)
SELECT 
  employee_id, 
  name, 
  manager_id, 
  level
FROM employee_hierarchy
ORDER BY level, manager_id;

이 쿼리는 employee_hierarchy에서 계층 데이터를 구축하고, 레벨별로 그룹화된 직원을 검색합니다. 이를 통해 조직도와 유사한 정보를 동적으로 생성할 수 있습니다.

여러 CTE를 사용한 고급 분석

여러 WITH 절을 활용함으로써 데이터를 단계적으로 처리할 수 있어 복잡한 분석을 단순화할 수 있습니다.

예시: 카테고리별 베스트셀러 제품 추출

WITH category_sales AS (
  SELECT 
    category_id, 
    product_id, 
    SUM(amount) AS total_sales
  FROM orders
  GROUP BY category_id, product_id
),
ranked_sales AS (
  SELECT 
    category_id, 
    product_id, 
    total_sales,
    RANK() OVER (PARTITION BY category_id ORDER BY total_sales DESC) AS rank
  FROM category_sales
)
SELECT 
  category_id, 
  product_id, 
  total_sales
FROM ranked_sales
WHERE rank <= 3;

이 쿼리에서 카테고리별 매출이 계산되며, 각 카테고리 내 상위 세 제품이 추출됩니다. 특정 조건에 기반한 데이터 좁히기에 효과적인 접근법입니다.

실무 적용을 위한 핵심 포인트

논리적 단계로 쿼리 설계 WITH 절을 사용하여 쿼리를 분할하고 가독성을 유지하면서 데이터를 단계적으로 처리하세요.
필요한 데이터만 추출 WHERE와 LIMIT 절을 사용하여 불필요한 데이터 처리를 피하고 효율적인 쿼리를 설계하세요.
유연한 비즈니스 적용 WITH 절은 매출 분석, 고객 세분화, 재고 관리 등에 유연하게 적용할 수 있습니다.

7. 자주 묻는 질문 (FAQ)

Q1: WITH 절은 언제 사용해야 하나요?

A1:
WITH 절은 다음 시나리오에서 특히 효과적입니다:

복잡한 서브쿼리를 단순화하고 싶을 때.
쿼리 내에서 동일한 데이터셋을 여러 번 재사용해야 할 때.
쿼리를 논리적으로 분할하여 가독성을 향상시키고 싶을 때.

예를 들어, 동일한 집계 결과를 여러 번 사용하는 쿼리에서 WITH 절을 사용하면 더 효율적으로 구성할 수 있습니다.

Q2: 재귀 CTE는 언제 유용한가요?

A2:
재귀 CTE는 계층 구조나 반복 계산을 처리할 때 유용합니다. 구체적으로:

계층 데이터 처리 (예: 조직 트리, 카테고리 구조).
폴더 또는 파일 계층 표시 .
숫자나 기간의 순차 계산 (예: 피보나치 수열 계산).

재귀 CTE를 사용하면 자기 참조 데이터를 확장하고 처리하기 쉬워집니다.

Q3: WITH 절을 사용하는 쿼리가 뷰보다 더 효율적인가요?

A3:
사용 사례에 따라 다릅니다.

WITH 절 : 쿼리 내에서만 사용되는 임시 결과 집합을 생성합니다. 자주 재사용할 필요가 없는 데이터에 적합합니다.
뷰 : 데이터베이스에 영구적으로 저장되며 다른 쿼리에서 재사용할 수 있습니다. 반복적으로 사용되는 쿼리에 적합합니다.

시나리오에 따라 적절한 방법을 선택하는 것이 중요합니다.

Q4: WITH 절을 사용할 때 성능 저하를 일으키는 원인은 무엇인가요?

A4:
WITH 절을 사용할 때 성능 저하를 일으키는 주요 원인은 다음과 같습니다:

CTE 재계산 : 결과가 참조될 때마다 다시 계산되어 처리 시간이 증가합니다.
대용량 데이터 처리 : CTE 내에서 큰 데이터 집합을 생성하면 메모리 사용량이 증가하고 성능이 저하됩니다.
적절한 인덱스 부재 : CTE 내부 쿼리가 적절한 인덱스를 사용하지 않으면 성능이 저하될 수 있습니다.