22_ CTE (Common Table Expressions)

1. CTE의 개념

CTE는 SQL 쿼리 안에서 임시로 정의되는 가상 테이블이다.
WITH 절로 정의되며, 쿼리 실행 중에만 존재한다.
즉, 실제 데이터베이스에 저장되는 건 아니고 한 번의 쿼리 실행 내에서만 유효하다.

CTE를 사용하면 복잡한 쿼리를 단계적으로 나눌 수 있어서
가독성이 좋아지고, 유지보수도 훨씬 쉬워진다.

 

 

2. 기본 구문

WITH [RECURSIVE] 임시테이블명 [(컬럼명, ...)] AS (
    -- 서브쿼리
)
SELECT * FROM 임시테이블명;

 

• WITH : CTE를 정의할 때 사용
• 임시테이블명 : 가상의 테이블 이름
• 컬럼명 : (선택 사항) 결과 컬럼의 이름
• AS ( ... ) : 괄호 안에 서브쿼리 작성
• SELECT ... : 이후 메인 쿼리에서 CTE를 참조

 

3. CTE의 특징

1. 임시적인 성격 – 쿼리 실행 중에만 존재한다.
2. 가독성 향상 – 복잡한 서브쿼리를 이름 붙여 관리할 수 있다.
3. 재사용 가능 – 같은 CTE를 한 쿼리 안에서 여러 번 사용할 수 있다.
4. 여러 개 정의 가능 – 하나의 WITH 절에 CTE를 여러 개 연결할 수 있다.
5. 재귀 CTE 지원 – 자기 자신을 참조하면서 계층 구조 데이터를 처리할 수 있다.

 

4. 기본 CTE 예제

사원 테이블에서 급여가 평균 이상인 사원을 조회하는 예제다.

WITH HighPaidEmployees AS (
    SELECT employee_id, first_name, last_name, salary
    FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees)
)
SELECT *
FROM HighPaidEmployees
ORDER BY salary DESC;

 

설명:

• HighPaidEmployees라는 이름의 CTE를 정의하고
• 그 안에서 평균 급여보다 높은 사람들을 먼저 추려낸 뒤
• 메인 쿼리에서 그 결과를 불러와 정렬한다.

이렇게 하면 서브쿼리를 계속 반복해서 작성하지 않아도 된다.

 

5. 다중 CTE 예제

CTE는 하나만 쓸 수도 있지만, 여러 개를 동시에 정의할 수도 있다.
이걸 이용하면 단계적으로 계산하는 쿼리를 만들 수 있다.

WITH
  AvgSalaryByDept AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
  ),
  EmployeesWithDept AS (
    SELECT e.employee_id, e.first_name, e.last_name, e.salary, e.department_id, d.department_name
    FROM employees e
    JOIN departments d ON e.department_id = d.department_id
  )
SELECT e.employee_id, e.first_name, e.last_name, e.salary, e.department_name,
       a.avg_salary,
       CASE WHEN e.salary > a.avg_salary THEN '평균 이상' ELSE '평균 이하' END AS salary_status
FROM EmployeesWithDept e
JOIN AvgSalaryByDept a ON e.department_id = a.department_id
ORDER BY e.department_id, e.salary DESC;

설명:

1. AvgSalaryByDept – 부서별 평균 급여 계산
2. EmployeesWithDept – 사원과 부서 정보를 조인
3. 메인 쿼리 – 두 CTE를 다시 조인해 부서별 평균 대비 급여 상태를 표시

이런 구조를 쓰면 긴 SQL문도 훨씬 깔끔해지고,
각 단계를 따로 이해할 수 있어서 유지보수하기 좋다.

 

6. 정리

• CTE는 WITH 절을 사용한 임시 결과 집합이다.
• 쿼리의 구조를 논리적으로 나눠서 가독성과 재사용성을 높인다.
• 하나 이상의 CTE를 정의할 수 있고, 재귀적인 구조도 가능하다.
• 실제 테이블이 아니기 때문에 쿼리 실행이 끝나면 사라진다.