[MySQL] WITH

MySQL에서 WITH 절(공통 테이블 표현식, CTE: Common Table Expressions)은 임시적인 이름이 있는 결과 집합을 정의하여 복잡한 쿼리를 간소화하고 가독성을 높이는 데 사용됩니다. WITH 절은 재사용 가능한 서브쿼리처럼 동작하며, 한 번 정의된 결과를 쿼리에서 여러 번 참조할 수 있습니다.

MySQL 8.0부터 WITH 절이 지원되기 시작했습니다. 이를 통해 중첩된 서브쿼리를 깔끔하게 처리할 수 있으며, 쿼리 구조를 논리적으로 분리하여 더 쉽게 이해할 수 있게 만듭니다.

기본 구조

WITH cte_name AS (
    -- 서브쿼리
    SELECT ...
)
SELECT ...
FROM cte_name;

설명:
WITH cte_name AS (서브쿼리):
cte_name은 서브쿼리의 결과를 참조하기 위한 임시 테이블 이름입니다.
AS 뒤에 서브쿼리를 정의합니다.
WITH 절을 사용하면 이 서브쿼리를 여러 번 사용할 필요 없이 한 번 정의하고 쿼리에서 여러 번 참조할 수 있습니다.

SELECT ... FROM cte_name:
WITH 절에서 정의한 cte_name을 쿼리에서 참조하여 결과를 사용할 수 있습니다.

예시 1: 기본 CTE 사용

다음 예시는 employees 테이블에서 직원들의 부서별 평균 급여를 계산한 후, 평균 급여 이상을 받는 직원들을 조회하는 쿼리입니다.

WITH dept_avg_salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.employee_id, e.name, e.salary, d.avg_salary
FROM employees e
JOIN dept_avg_salary d
    ON e.department_id = d.department_id
WHERE e.salary >= d.avg_salary;

설명:
dept_avg_salary: 각 부서의 평균 급여를 계산한 결과를 담고 있는 임시 테이블입니다.
SELECT 쿼리: dept_avg_salary를 참조하여 직원의 급여가 해당 부서의 평균 급여 이상인 직원을 조회합니다.

예시 2: 여러 CTE 사용

MySQL에서는 여러 개의 WITH 절을 정의할 수 있습니다. 이 경우 쉼표(,)로 구분하여 여러 CTE를 연달아 정의할 수 있습니다.

WITH dept_avg_salary AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
),
high_salary_employees AS (
    SELECT employee_id, name, salary
    FROM employees
    WHERE salary > 100000
)
SELECT e.name, e.salary, d.avg_salary
FROM high_salary_employees e
JOIN dept_avg_salary d
    ON e.department_id = d.department_id;

설명:
dept_avg_salary: 부서별 평균 급여를 계산합니다.
high_salary_employees: 100,000 이상의 급여를 받는 직원들을 추출합니다.
최종 쿼리에서 두 CTE를 결합하여 고액 연봉을 받는 직원과 그들의 부서별 평균 급여를 조회합니다.

재귀적 CTE

MySQL의 WITH 절은 재귀적인 쿼리도 지원합니다. 이를 통해 계층적 데이터(예: 조직 구조, 디렉터리 구조 등)를 처리할 수 있습니다.

WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, manager_id, name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL  -- 최고 관리자를 선택

    UNION ALL

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

설명:
재귀적 CTE는 WITH RECURSIVE로 정의됩니다.
상위 관리자(최고 관리자)를 기준으로 각 직원이 속한 계층(level)을 계산합니다.
UNION ALL을 사용하여 재귀적으로 직원의 상사 정보를 연결합니다.

요약:
WITH 절은 서브쿼리를 재사용 가능하게 만들고, 쿼리의 가독성을 높이며, 복잡한 SQL 쿼리를 단계적으로 처리할 수 있게 합니다.
여러 CTE를 정의할 수 있고, 재귀적 CTE도 처리 가능합니다.
쿼리를 여러 번 작성하지 않고도 동일한 서브쿼리 결과를 사용할 수 있어 성능 개선에도 도움을 줄 수 있습니다.
WITH 절은 복잡한 쿼리에서 특히 유용하며, 서브쿼리를 재사용할 수 있도록 해 주기 때문에 쿼리 작성의 유연성을 크게 향상시킵니다.