[SQL 개념 정리 8] CTE (WITH 문)

CTE (WITH 문)

CTE(Common Table Expressions) 또는 WITH문은 SQL에서 쿼리를 간결하고 구조적으로 작성할 수 있도록 도와주는 기능입니다. CTE는 임시 테이블처럼 사용할 수 있는 쿼리를 정의하여, 복잡한 쿼리를 여러 단계로 나누거나 반복해서 사용할 때 유용합니다.

---

CTE (WITH 문) 개념

CTE는 WITH 절을 사용하여 이름이 지정된 쿼리를 미리 정의하고, 이후의 메인 쿼리에서 이를 참조할 수 있도록 합니다. WITH로 정의된 CTE는 일종의 임시 테이블 역할을 하며, 코드 가독성을 높이고 중첩 쿼리를 줄일 수 있어 복잡한 SQL을 더 쉽게 작성할 수 있습니다.

 

기본 구문

WITH cte_name AS (
    서브쿼리
)
SELECT ...
FROM cte_name;

• cte_name: CTE의 이름입니다. 이후에 이 이름을 테이블처럼 사용합니다.
• 서브쿼리: CTE에서 사용할 쿼리입니다.

CTE의 장점

1. 복잡한 쿼리를 단계적으로 작성할 수 있어 가독성이 높아집니다.
2. 재사용이 가능하여 동일한 서브쿼리를 여러 번 사용할 때 유용합니다.
3. 중첩 쿼리를 줄이고, 쿼리 구조를 단순하게 만들어줍니다.

---

CTE 사용 예시

예시 1: 기본적인 CTE 사용

 

직원 테이블에서 급여(salary)가 50000 이상인 직원 목록을 CTE로 정의하고, 이를 활용하여 특정 조건의 데이터를 조회합니다.

WITH high_salary_employees AS (
    SELECT employee_name, department, salary
    FROM employees
    WHERE salary >= 50000
)
SELECT *
FROM high_salary_employees;

• high_salary_employees라는 CTE를 정의하여 salary가 50000 이상인 직원의 데이터를 추출합니다.
• 이후 메인 쿼리에서 high_salary_employees CTE를 테이블처럼 참조하여 데이터를 조회합니다.

예시 2: 여러 CTE 사용

 

여러 개의 CTE를 정의할 수 있으며, CTE들은 쉼표(,)로 구분합니다.

직원 테이블에서 각 부서별 평균 급여를 계산하는 avg_salary_per_department CTE와 급여가 평균 이상인 직원 목록을 구하는 above_avg_employees CTE를 정의하고, 이를 활용하여 최종적으로 급여가 높은 직원들을 조회합니다.

WITH avg_salary_per_department AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
),
above_avg_employees AS (
    SELECT e.employee_name, e.department, e.salary
    FROM employees e
    JOIN avg_salary_per_department a ON e.department = a.department
    WHERE e.salary >= a.avg_salary
)
SELECT *
FROM above_avg_employees;

• 첫 번째 CTE avg_salary_per_department에서는 부서별 평균 급여를 계산합니다.
• 두 번째 CTE above_avg_employees는 직원의 급여가 소속된 부서의 평균 급여 이상인 직원들을 조회합니다.
• 최종적으로 급여가 평균 이상인 직원들의 데이터를 조회합니다.

---

재귀 CTE (Recursive CTE)

재귀 CTE는 자기 자신을 참조하여 반복적으로 데이터를 계산하는 CTE입니다. 주로 트리 구조의 데이터나 계층적인 데이터를 처리할 때 사용됩니다.

 

예시 : 직원 계층 구조 조회

다음 예제에서는 직원 테이블을 사용하여 관리자와 직원의 계층 구조를 조회합니다.

• employee_id는 직원의 ID, manager_id는 해당 직원의 상사의 ID입니다.

WITH RECURSIVE employee_hierarchy AS (
    -- 기본(Anchoring) 쿼리: 최상위 관리자를 선택
    SELECT employee_id, employee_name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- 재귀 쿼리: 상위 관리자의 하위 직원을 찾음
    SELECT e.employee_id, e.employee_name, e.manager_id, h.level + 1
    FROM employees e
    JOIN employee_hierarchy h ON e.manager_id = h.employee_id
)
SELECT *
FROM employee_hierarchy;

• 기본 쿼리는 최상위 관리자(상사가 없는 직원)를 찾습니다.
• 재귀 쿼리는 상위 관리자를 기준으로 하위 직원을 계속 찾아나가며, 계층 구조를 level로 표시합니다.
• 최종적으로 직원 계층 구조가 출력됩니다

CTE의 활용 요약

1. 단순화: 복잡한 쿼리를 단계적으로 나누어 구조를 단순화.
2. 반복 사용: 동일한 쿼리 로직을 여러 번 사용해야 할 때 중복을 줄임.
3. 재귀 쿼리: 트리, 계층형 데이터 구조를 쉽게 조회 가능.

---

CTE는 복잡한 SQL 쿼리를 간결하고 구조적으로 작성할 수 있게 해주는 유용한 도구입니다. 임시 테이블을 정의해 중복을 줄이고, 재귀 쿼리를 통해 계층 구조를 쉽게 조회할 수 있는 CTE의 장점을 잘 활용하면, SQL 작성이 한층 더 수월해집니다.