IT

MySQL 공통 테이블 표현식(Common Table Expressions, CTEs) 사용법

lilililililiiii 2025. 5. 5. 11:12

복잡한 비즈니스 로직을 SQL로 구현하다 보면, 쿼리가 길어지고 여러 단계의 데이터 가공이 필요해지면서 가독성이 떨어지고 유지보수가 어려워지는 문제에 직면하게 됩니다. MySQL 8.0부터 도입된 공통 테이블 표현식(Common Table Expressions, CTEs)은 이러한 문제를 해결하는 데 도움을 주는 강력한 기능입니다. WITH 키워드를 사용하여 쿼리 내에서 재사용 가능한 명명된 임시 결과 집합을 정의함으로써, 복잡한 쿼리를 논리적인 단위로 명확하게 분리하고 구조화할 수 있습니다. 이는 특히 다단계 연산이 필요하거나, 동일한 중간 결과 집합을 여러 번 참조해야 하는 경우, 그리고 기존의 SQL로는 표현하기 까다로웠던 조직도나 부품 목록(BOM)과 같은 계층적 데이터를 처리해야 할 때(재귀 CTE 활용) 그 진가를 발휘합니다. 본 글에서는 CTE의 기본 구문과 작동 방식, 서브쿼리(인라인 뷰) 대비 가독성 및 재사용성 측면에서의 장점을 살펴보고, 실제 활용 예제(일반 CTE 및 재귀 CTE)를 통해 그 효과를 확인하며, 마지막으로 CTE 사용 시 성능 관련 고려사항 및 주의점에 대해 논의하고자 합니다.

CTE의 기본 구문

WITH <CTE 이름> [(<컬럼 이름 목록>)] AS (
    <CTE 쿼리 정의> -- SELECT 문
)
[, <다른 CTE 이름> AS (
    <다른 CTE 쿼리 정의> -- 이전 CTE 참조 가능
)]...
SELECT ...
FROM <CTE 이름>
[JOIN <다른 CTE 이름 또는 테이블> ON ...]
...;
  • WITH: CTE 정의를 시작하는 키워드.
  • <CTE 이름>: 정의할 CTE의 이름. 쿼리 내에서 이 이름으로 참조할 수 있습니다.
  • [(<컬럼 이름 목록>)] (선택 사항): CTE 결과 집합의 컬럼 이름을 명시적으로 지정할 수 있습니다. 생략하면 CTE 쿼리 정의의 SELECT 절 컬럼 이름이 사용됩니다.
  • AS (<CTE 쿼리 정의>): CTE를 정의하는 SELECT 문.
  • ,: 여러 개의 CTE를 정의할 때 구분자로 사용됩니다. 뒤따르는 CTE는 앞서 정의된 CTE를 참조할 수 있습니다.
  • 메인 쿼리: 정의된 CTE를 테이블처럼 참조하여 사용하는 주 SELECT (또는 INSERT, UPDATE, DELETE) 문.

CTE의 장점

  1. 가독성 향상: 복잡한 쿼리를 논리적인 단위로 나누어 이름을 부여함으로써 전체 쿼리의 구조를 이해하기 쉽게 만듭니다. 긴 서브쿼리 중첩을 피할 수 있습니다.
  2. 재사용성 증대: 하나의 CTE를 쿼리 내에서 여러 번 참조할 수 있습니다. 동일한 서브쿼리를 반복해서 작성할 필요가 없습니다. (단, MySQL의 현재 구현에서는 CTE가 참조될 때마다 실행될 수 있으므로 성능상 이점은 제한적일 수 있습니다. 옵티마이저가 Materialization을 적용할 수도 있습니다.)
  3. 계층 구조 쿼리 작성 (Recursive CTE): 자기 자신을 참조하는 재귀적 CTE를 정의하여 조직도, 부품 목록(Bill of Materials), 그래프 탐색 등 계층적인 데이터를 쉽게 처리할 수 있습니다.

CTE 활용 예제

예제 1: 부서별 평균 급여보다 높은 급여를 받는 직원 조회 (가독성 향상)

-- 서브쿼리 사용 시
SELECT e.emp_no, e.first_name, s.salary, de.dept_no
FROM employees e
JOIN salaries s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'
JOIN dept_emp de ON e.emp_no = de.emp_no AND de.to_date = '9999-01-01'
WHERE s.salary > (
    SELECT AVG(s2.salary)
    FROM salaries s2
    JOIN dept_emp de2 ON s2.emp_no = de2.emp_no AND de2.to_date = '9999-01-01'
    WHERE de2.dept_no = de.dept_no AND s2.to_date = '9999-01-01'
);

-- CTE 사용 시
WITH DeptAvgSalary AS ( -- 부서별 평균 급여를 계산하는 CTE 정의
    SELECT de.dept_no, AVG(s.salary) AS avg_salary
    FROM salaries s
    JOIN dept_emp de ON s.emp_no = de.emp_no AND de.to_date = '9999-01-01'
    WHERE s.to_date = '9999-01-01'
    GROUP BY de.dept_no
)
SELECT e.emp_no, e.first_name, s.salary, de.dept_no
FROM employees e
JOIN salaries s ON e.emp_no = s.emp_no AND s.to_date = '9999-01-01'
JOIN dept_emp de ON e.emp_no = de.emp_no AND de.to_date = '9999-01-01'
JOIN DeptAvgSalary das ON de.dept_no = das.dept_no -- 정의된 CTE 참조
WHERE s.salary > das.avg_salary; -- CTE의 결과를 조건절에서 사용
  • CTE를 사용하여 부서별 평균 급여 계산 로직을 분리함으로써 메인 쿼리가 더 명확해졌습니다.

예제 2: 재귀 CTE를 이용한 조직도 조회

-- 가정: employees 테이블에 employee_id, name, manager_id 컬럼 존재
WITH RECURSIVE EmployeeHierarchy (employee_id, name, manager_id, level) AS (
    -- 시작점 (Anchor Member): 최상위 관리자 (manager_id가 NULL인 직원)
    SELECT employee_id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 재귀 부분 (Recursive Member): 하위 직원 조회
    SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.employee_id -- 자기 자신(CTE) 참조
)
-- 최종 결과 조회
SELECT employee_id, name, manager_id, level
FROM EmployeeHierarchy
ORDER BY level, manager_id, employee_id;
  • WITH RECURSIVE 키워드를 사용하여 재귀 CTE를 정의합니다.
  • 시작점(Anchor Member)과 재귀 부분(Recursive Member)을 UNION ALL로 연결합니다.
  • 재귀 부분에서는 CTE 자기 자신을 참조하여 계층 구조를 탐색합니다.

CTE와 서브쿼리(인라인 뷰) 비교

특징 CTE (Common Table Expression) 서브쿼리 (Inline View / Derived Table)
정의 위치 쿼리 시작 부분 (WITH 키워드 사용) 주로 FROM 절 내부
재사용성 쿼리 내에서 여러 번 참조 가능 일반적으로 참조될 때마다 다시 정의
가독성 복잡한 쿼리를 논리적 단위로 분리하여 향상 중첩될 경우 가독성 저하 가능성
재귀 가능 가능 (WITH RECURSIVE) 불가능
최적화 옵티마이저가 Materialize 하거나 인라인 처리 가능 옵티마이저가 Materialize 하거나 인라인 처리 가능
지원 버전 MySQL 8.0+ 이전 버전부터 지원

CTE 사용 시 고려사항

  • 성능: CTE는 가독성을 높이는 데 유용하지만, 반드시 성능 향상을 보장하는 것은 아닙니다. MySQL 옵티마이저가 CTE를 어떻게 처리하는지(EXPLAIN 확인)가 중요합니다. 때로는 CTE가 참조될 때마다 내부적으로 재실행될 수 있으며, 이 경우 서브쿼리보다 성능이 나빠질 수도 있습니다. 옵티마이저가 CTE 결과를 구체화(Materialize)하면 성능상 이점이 있을 수 있습니다.
  • 재귀 CTE의 종료 조건: 재귀 CTE를 작성할 때는 재귀가 무한히 반복되지 않도록 종료 조건을 명확히 설정해야 합니다. MySQL에는 cte_max_recursion_depth 시스템 변수(기본값 1000)가 있어 최대 재귀 깊이를 제한합니다.
  • 업데이트/삭제 제한: CTE는 주로 SELECT 문과 함께 사용되지만, 제한적으로 UPDATE나 DELETE 문의 대상 테이블 지정에도 사용될 수 있습니다. 하지만 복잡한 CTE 구조에서는 제약이 따를 수 있습니다.

공통 테이블 표현식(CTE)은 MySQL 8.0 이후 복잡한 SQL 쿼리를 작성하는 방식에 있어 중요한 진전을 나타냅니다. WITH 절을 통해 복잡한 로직을 의미 있는 단위로 분리하고 이름을 부여함으로써, 쿼리의 가독성과 구조적 명확성을 획기적으로 개선할 수 있습니다. 이는 특히 여러 단계의 데이터 처리나 동일 로직의 반복 사용이 필요한 경우 유용하며, WITH RECURSIVE를 통한 계층 구조 쿼리 기능은 CTE만의 독보적인 강점입니다.

하지만 CTE가 제공하는 가독성 및 구조 개선 효과가 항상 직접적인 성능 향상으로 이어지는 것은 아니라는 점을 명심해야 합니다. CTE의 성능은 MySQL 옵티마이저가 해당 CTE를 어떻게 처리하는지(예: 구체화(Materialize)하여 임시 테이블처럼 사용하는지, 아니면 참조될 때마다 인라인화하여 재실행하는지)에 따라 달라질 수 있습니다. 따라서 CTE를 도입할 때는 반드시 EXPLAIN을 통해 실제 실행 계획을 확인하고 성능 영향을 평가하는 과정이 필수적입니다. 경우에 따라서는 전통적인 서브쿼리나 임시 테이블 사용이 더 효율적일 수도 있습니다.

결론적으로, CTE는 코드의 명료성과 유지보수성을 높이고, 특히 재귀적인 데이터 탐색이 필요할 때 매우 효과적인 도구입니다. 이러한 장점을 적극 활용하되, 성능적 측면에서는 맹신하지 않고 항상 실행 계획 분석을 통해 최적의 접근 방식을 선택하는 현명함이 요구됩니다.