[SQL] CTE(Common Table Expression)

# CTE(Common Table Expression)

• SQL 쿼리 내에서 임시로 사용할 수 있는 이름이 있는 결과 집합을 정의하는 방식
• 일반적으로 긴 쿼리의 가독성을 높이고, 반복적인 서브쿼리를 간소화하며, 복잡한 쿼리를 단계별로 처리할 때 유용하다.
• CTE는 WITH 절을 사용하여 정의하며, CTE로 정의된 데이터는 해당 쿼리에서만 사용 가능하다.

 

# 구조

WITH cte_name AS (
    -- CTE를 정의하는 쿼리
    SELECT column1, column2, ...
    FROM table_name
    WHERE conditions
)
-- CTE를 활용한 주 쿼리
SELECT *
FROM cte_name
WHERE additional_conditions;

• WITH cte_name AS:
  • cte_name은 정의된 CTE의 이름이며, 이 이름은 이후의 주 쿼리에서 참조할 수 있다.
• (...):
  • CTE 내부에 실행할 쿼리를 작성한다.
• CTE는 주 쿼리에서 테이블처럼 사용할 수 있다.

 

# 특징

 

• 가독성 향상: 긴 SQL 쿼리를 논리적으로 분리하여 더 이해하기 쉽게 만든다.
• 재사용 가능: 정의된 CTE는 주 쿼리 내에서 여러 번 참조할 수 있다.
• 임시 데이터 저장: 데이터의 중간 결과를 임시로 저장하여 복잡한 데이터 처리에 사용한다.
• 계층적 데이터 처리: 재귀 쿼리 작성 시 유용하다.

 

 

# 예시

## 기본 예시: 특정 테이블의 직원 데이터 처리하기

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

 

• employee_cte:
  • 각 부서의 평균 급여를 계산한다.
• 주 쿼리:
  • 직원의 급여가 해당 부서 평균보다 높은 직원만 출력한다.

 

## 재귀 CTE

• CTE는 계층적 데이터(예: 조직 구조, 디렉토리 트리 등)를 처리할 때 유용하다.

WITH RECURSIVE hierarchy_cte AS (
    -- Anchor 쿼리
    SELECT employee_id, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    -- Recursive 쿼리
    SELECT e.employee_id, e.manager_id, h.level + 1
    FROM employees e
    JOIN hierarchy_cte h
    ON e.manager_id = h.employee_id
)
SELECT *
FROM hierarchy_cte;

 

• RECURSIVE:
  • CTE가 재귀적으로 데이터를 처리함을 나타낸다.
• Anchor 쿼리:
  • 계층의 시작점(예: 최고 관리자)을 정의한다.
• Recursive 쿼리:
  • 이전 단계의 결과를 사용하여 다음 단계 데이터를 가져온다.

 

 

# CTE vs 서브쿼리

특징	CTE	서브쿼리
가독성	쿼리를 논리적으로 분리하여 가독성 향상	길어질 경우 가독성이 떨어질 수 있음
재사용성	주 쿼리에서 여러 번 재사용 가능	재사용 불가능
중첩 처리	복잡한 쿼리를 단계별로 처리 가능	중첩 서브쿼리는 성능과 가독성에 부담을 줄 수 있음
재귀 처리	재귀 CTE를 사용하여 계층적 데이터 처리 가능	재귀 처리 불가능