[MySQL 심층 탐구] 쿼리의 품격을 높이는 'WITH' 절 (CTE) 완벽 가이드

지난 포스팅에서 데이터베이스에 영구적으로 저장되는 가상 테이블인 VIEW에 대해 알아보았습니다. 하지만 모든 가상 테이블이 영구적일 필요는 없습니다. 특정 쿼리 안에서만 잠시 사용하고 버려질 '일회성' 가상 테이블이 필요할 때, 우리는 WITH 절(Common Table Expression, CTE)을 사용합니다.

MySQL 8.0부터 도입된 이 강력한 기능을 통해 지저분한 서브쿼리를 어떻게 깔끔하게 정리할 수 있는지 분석해 보겠습니다.

---

1. WITH 절(CTE)이란 무엇인가?

CTE(Common Table Expression)는 복잡한 쿼리문 내에서 임시적으로 정의하여 사용하는 결과 집합입니다.

• 일시성: 쿼리가 실행되는 동안에만 존재하며, 실행이 끝나면 메모리에서 사라집니다.
• 가독성: 쿼리 상단에 로직을 정의하므로, 서브쿼리가 중첩되어 발생하는 '스파게티 코드'를 방지합니다.
• 재사용성: 하나의 WITH 절에서 정의한 CTE를 본 쿼리 내에서 여러 번 참조할 수 있습니다.

---

2. 왜 서브쿼리 대신 WITH 절을 써야 하는가?

① 선언적 구조 (위에서 아래로 흐르는 로직)

서브쿼리는 가장 안쪽부터 밖으로 읽어야 하지만, WITH 절은 상단에 '준비물'을 먼저 정의하고 하단에서 '조립'하는 방식입니다. 이는 개발자의 인지 부하를 크게 줄여줍니다.

② 중복 로직의 제거

동일한 서브쿼리가 한 쿼리 내에서 두 번 이상 등장한다면, 이를 CTE로 정의하여 단 한 번만 계산하고 재사용할 수 있습니다.

③ 재귀적 쿼리(Recursive) 지원

WITH 절의 진가는 재귀 호출에 있습니다. 조직도, 카테고리 계층, 탐색 알고리즘 등을 SQL만으로 구현할 수 있게 해주는 유일한 통로입니다.

---

3. 실전 예제: 1편의 테이블 활용하기

1편에서 만든 employees, departments, dept_emp, salaries 테이블을 활용하여 "부서별 평균 급여보다 많이 받는 직원 리스트"를 뽑아보겠습니다. 서브쿼리로 짜면 매우 복잡해질 내용입니다.

아래의 링크의 실전예제에서 만들어진 데이터 테이블을 기준으로 쿼리를 작성해보도록 하겠습니다.

 

https://gapal.tistory.com/97

-- 1. Marketing 부서에 신입 사원 'Kevin' 추가
INSERT INTO employees VALUES (103, 'Kevin', 'White', '2023-12-01');

-- 2. Kevin을 Marketing(D001) 부서에 배치
INSERT INTO dept_emp VALUES (103, 'D001', '2023-12-01', '9999-01-01');

-- 3. Kevin의 급여를 낮게 설정 (40,000)
-- 이렇게 하면 Marketing 부서 평균은 (60,000 + 40,000) / 2 = 50,000이 됩니다.
INSERT INTO salaries VALUES (103, 40000, '2023-12-01', '9999-01-01');

[WITH 절 활용 쿼리]

-- 1. 부서별 평균 급여를 먼저 정의(CTE)
WITH Dept_Avg_Salary AS (
    SELECT de.dept_no, AVG(s.salary) AS avg_sal
    FROM dept_emp de
    JOIN salaries s ON de.emp_no = s.emp_no
    WHERE s.to_date = '9999-01-01'
    GROUP BY de.dept_no
)
-- 2. 정의된 CTE를 본 쿼리에서 사용
SELECT 
    e.first_name, 
    d.dept_name, 
    s.salary, 
    round(das.avg_sal, 0) AS dept_average
FROM employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
JOIN departments d ON de.dept_no = d.dept_no
JOIN salaries s ON e.emp_no = s.emp_no
JOIN Dept_Avg_Salary das ON de.dept_no = das.dept_no  -- CTE와 조인
WHERE s.salary > das.avg_sal;  -- 평균보다 높은 사람만 필터링

 

---

 

4. VIEW와 WITH 절, 무엇이 다른가? (심화 비교)

기술적으로 가장 큰 차이는 "저장소의 유무"와 "범위"입니다.

비교 항목	VIEW (뷰)	WITH 절 (CTE)
저장 위치	DB 객체로 메타데이터가 저장됨	메모리 내 임시 저장 (일시적)
재사용성	모든 쿼리에서 호출 가능	해당 쿼리문 내에서만 사용 가능
유지보수	한 번 바꾸면 모든 곳에 적용됨	쿼리를 수정할 때마다 직접 고쳐야 함
용도	공용 인터페이스, 보안 강화	복잡한 쿼리 최적화 및 가독성 향상

---

5. 주의사항: 성능 오버헤드

냉정하게 말해, MySQL의 CTE는 만능이 아닙니다.

• 물리적 생성: MySQL 옵티마이저는 경우에 따라 CTE 결과를 임시 테이블로 물리적으로 생성(Materialize)합니다. 데이터 양이 방대할 경우, 인덱스가 없는 임시 테이블을 조회하는 쿼리가 되어 오히려 성능이 떨어질 수 있습니다.
  따라서 성능이 중요하거나 데이터가 많은경우에는 with을 신중하게 사용해야합니다.
• 버전 확인: 반드시 MySQL 8.0 이상이어야 합니다. 5.7 이하 버전에서는 서브쿼리로 대체해야 합니다.

---

WITH 절은 복잡한 비즈니스 로직을 단계별로 나누어 사고하게 해주는 설계 도구 입니다. 데이터베이스 전체를 관통하는 공통 로직은 VIEW로, 특정 쿼리의 복잡함을 해결할 때는 WITH 절을 선택하는 방법이 효과적일수 있습니다.