[MySQL] WITH

MySQL에서 WITH 구문은 Common Table Expressions(CTEs)를 정의하는데 사용됩니다. CTE란 복잡한 쿼리를 단순화하기 위해 사용되는 일시적인 결과 집합으로, 쿼리 내에서 한 번 정의되면 쿼리의 다른 부분에서 재사용될 수 있습니다. WITH 구문을 사용하면 코드의 가독성과 유지 보수성이 향상되며, 복잡한 조인과 서브쿼리를 더 명확하게 표현할 수 있습니다.

기본 구조

WITH CTE이름 AS (
    SELECT 쿼리
)
SELECT *
FROM CTE이름;

예제

예를 들어, 직원 테이블에서 각 부서별로 최고 급여를 받는 직원의 정보를 찾는 쿼리를 작성한다고 가정해 봅시다. 이때 WITH 구문을 사용하여 CTE를 정의할 수 있습니다.

WITH HighestSalaries AS (
    SELECT DepartmentID, MAX(Salary) AS MaxSalary
    FROM Employees
    GROUP BY DepartmentID
)
SELECT E.Name, E.Salary, E.DepartmentID
FROM Employees E
JOIN HighestSalaries HS ON E.DepartmentID = HS.DepartmentID AND E.Salary = HS.MaxSalary;

위의 쿼리에서 HighestSalaries라는 CTE를 정의하고 있습니다. 이 CTE는 각 부서별로 최고 급여를 계산합니다. 그리고 메인 쿼리에서는 Employees 테이블을 HighestSalaries CTE와 조인하여 각 부서의 최고 급여를 받는 직원의 이름, 급여, 부서 ID를 선택합니다.

재귀적 CTE 사용하기

WITH 구문은 재귀적 쿼리를 작성하는 데에도 사용될 수 있습니다. 재귀적 CTE는 자기 자신을 참조하여 재귀적으로 데이터를 생성하거나 처리하는 CTE입니다.

기본 구조

WITH RECURSIVE CTE이름 AS (
    초기 쿼리
    UNION ALL
    재귀 쿼리
)
SELECT * FROM CTE이름;

예제

부서 테이블에서 모든 상위 부서와 하위 부서의 관계를 나타내는 쿼리를 작성해 보겠습니다. 이 경우, 재귀적 CTE를 사용할 수 있습니다.

WITH RECURSIVE DepartmentHierarchy AS (
    SELECT DepartmentID, ParentDepartmentID
    FROM Departments
    WHERE ParentDepartmentID IS NULL
    UNION ALL
    SELECT d.DepartmentID, dh.ParentDepartmentID
    FROM Departments d
    JOIN DepartmentHierarchy dh ON d.ParentDepartmentID = dh.DepartmentID
)
SELECT * FROM DepartmentHierarchy;

이 예제에서는 DepartmentHierarchy라는 재귀적 CTE를 정의하고 있습니다. 초기 쿼리는 최상위 부서(상위 부서가 없는 부서)를 선택하고, 재귀 쿼리는 하위 부서를 찾아 계층 구조를 구성합니다.

WITH 구문을 사용하면 SQL 쿼리의 가독성과 유지 보수성을 크게 향상시킬 수 있으며, 복잡한 데이터 처리 작업을 보다 효율적으로 수행할 수 있습니다.