[ MySQL] WITH 절

MySQL의 WITH 절은 Common Table Expression (CTE)를 정의하는 데 사용됩니다. CTE는 임시 결과 집합으로, 쿼리 내에서 반복적으로 사용하거나 더 가독성이 좋은 쿼리를 작성하는 데 유용합니다.
MySQL에서는 버전 8.0 이상에서 WITH 절을 사용할 수 있습니다.

---

기본 문법

WITH cte_name AS (
    SELECT ... -- 임시 결과를 생성하는 서브쿼리
)
SELECT ... 
FROM cte_name;

• WITH: CTE를 정의하는 시작 부분.
• cte_name: CTE의 이름.
• AS: CTE에 대해 서브쿼리를 정의할 때 사용.
• 서브쿼리: CTE에 저장될 데이터 집합.

---

예제 1: 간단한 CTE

아래는 직원 테이블에서 특정 데이터를 CTE로 정의하고 활용하는 예입니다.

테이블 데이터

-- employees 테이블
+----+----------+------------+
| id | name     | salary     |
+----+----------+------------+
| 1  | Alice    | 5000       |
| 2  | Bob      | 7000       |
| 3  | Charlie  | 6000       |
+----+----------+------------+

CTE 사용

WITH high_salary_employees AS (
    SELECT name, salary
    FROM employees
    WHERE salary > 5500
)
SELECT * 
FROM high_salary_employees;

결과:

+--------+--------+
| name   | salary |
+--------+--------+
| Bob    | 7000   |
| Charlie| 6000   |
+--------+--------+

---

예제 2: CTE를 사용한 복잡한 쿼리

CTE를 활용하면 반복적인 서브쿼리를 제거하여 가독성을 높일 수 있습니다.

문제: 각 직원의 부서별 평균 연봉보다 높은 연봉을 가진 직원을 찾기

기존 서브쿼리 방식

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

CTE 활용

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

---

예제 3: 재귀 CTE (Recursive CTE)

재귀 CTE를 사용하면 계층 구조 데이터를 처리할 수 있습니다. 예를 들어, 직원 테이블에서 상사-부하 관계를 따라가며 조직도를 출력하는 방법입니다.

테이블 데이터

-- employees 테이블
+----+----------+------------+
| id | name     | manager_id |
+----+----------+------------+
| 1  | Alice    | NULL       |
| 2  | Bob      | 1          |
| 3  | Charlie  | 1          |
| 4  | Dave     | 2          |
+----+----------+------------+

재귀 CTE 쿼리

WITH RECURSIVE hierarchy AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL -- 최상위 관리자 선택

    UNION ALL

    SELECT e.id, e.name, e.manager_id, h.level + 1
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.id
)
SELECT * 
FROM hierarchy
ORDER BY level;

결과:

+----+----------+------------+-------+
| id | name     | manager_id | level |
+----+----------+------------+-------+
| 1  | Alice    | NULL       | 1     |
| 2  | Bob      | 1          | 2     |
| 3  | Charlie  | 1          | 2     |
| 4  | Dave     | 2          | 3     |
+----+----------+------------+-------+

---

CTE의 장점

1. 가독성 향상
   • 복잡한 쿼리를 여러 단계로 나누어 가독성을 높일 수 있습니다.
2. 재사용 가능
   • 동일한 서브쿼리를 반복적으로 작성하지 않아도 됩니다.
3. 재귀적 데이터 처리
   • 계층 구조 데이터를 쉽게 처리할 수 있습니다.
4. 임시 테이블 대체
   • 특정 데이터 세트를 임시로 생성하고 사용할 수 있습니다.

---

주의사항

• MySQL 8.0 이상에서만 사용 가능합니다.
• CTE는 쿼리 실행 시 메모리에서 처리되므로, 대량의 데이터셋에 대해 성능 영향을 미칠 수 있습니다.

---

WITH 절은 복잡한 SQL 쿼리를 간결하게 만들고 가독성을 높이는 강력한 도구입니다! 😊