Common Table Expression (CTE)

 

• CTE는 SQL에서 쿼리 안에서 일시적으로 생성되는 임시 결과 집합입니다.
• WITH 키워드를 사용하여 정의하며, 하나의 쿼리에서 반복적으로 사용할 수 있고 가독성과 성능 최적화에 매우 유용합니다.

CTE 기본 구조

WITH cte_name AS (
    SELECT 컬럼1, 컬럼2
    FROM 테이블
    WHERE 조건
)
SELECT * FROM cte_name;

CTE의 장점

• 복잡한 쿼리 단순화 : 긴 쿼리를 여러 단계로 나눠 작성할 수 있습니다.
• 재사용성 : 하나의 CTE를 여러 번 호출해도 실제 실행은 한 번만 수행됩니다.
• 가독성 : 코드가 더 깔끔해지고 유지보수가 쉬워집니다.
• 임시 테이블 대체 : 세션마다 유지되는 임시 테이블을 생성하지 않아도 됩니다.

예제 1: 중복 계산 방지

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

• dept_avg CTE에서 AVG(salary)를 한 번만 계산하고, 이후의 JOIN에서 반복적으로 사용합니다.

예제 2: 재귀 CTE 사용 (자기 자신 호출)

WITH RECURSIVE emp_hierarchy (emp_id, manager_id, level) AS (
    SELECT employee_id, manager_id, 1
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, h.level + 1
    FROM employees e
    JOIN emp_hierarchy h ON e.manager_id = h.emp_id
)
SELECT * FROM emp_hierarchy;

• 재귀 CTE를 사용하여 상위 관리자부터 하위 직원까지 계층 구조를 탐색할 수 있습니다.

CTE 활용 팁

• 동일한 서브쿼리를 여러 번 작성할 필요 없이 한 번 작성하고 반복 사용.
• WITH절을 사용하여 쿼리를 단계별로 작성하면 쿼리 튜닝이 더 쉬워집니다.
• 대규모 데이터 처리 시에도 성능 향상에 효과적입니다.