공통 테이블 표현식(CTE, Common Table Expression)

WITH는 SQL에서 서브쿼리를 정의하고 이를 마치 테이블처럼 사용할 수 있도록 하는 기능입니다. WITH는 보통 공통 테이블 표현식(CTE, Common Table Expression) 이라고 불리며, 반복적인 쿼리나 복잡한 서브쿼리를 단순화하고 가독성을 높이는 데 유용합니다.

기본 문법

WITH cte_name AS (
    -- 서브쿼리
    SELECT column1, column2
    FROM table
    WHERE condition
)
SELECT *
FROM cte_name
WHERE other_condition;

여기서 cte_name은 CTE의 이름이고, 그 뒤에 서브쿼리 내용을 적습니다. 그 후에 SELECT 구문에서 CTE를 테이블처럼 사용할 수 있습니다.

예시 1: 간단한 WITH 사용

WITH avg_salary AS (
    SELECT department_id, AVG(salary) AS average_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.name, e.salary, a.average_salary
FROM employees e
JOIN avg_salary a ON e.department_id = a.department_id
WHERE e.salary > a.average_salary;

위 예시는 employees 테이블에서 각 부서별 평균 급여를 계산하는 avg_salary라는 CTE를 정의한 후, 각 직원의 급여가 해당 부서의 평균 급여보다 높은 직원들만 선택합니다.

예시 2: 여러 개의 CTE 사용

WITH는 여러 개의 CTE를 정의할 수도 있습니다. CTE들을 쉼표로 구분하여 연결합니다.

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
),
high_salary AS (
    SELECT name, salary, department_id
    FROM employees
    WHERE salary > 100000
)
SELECT h.name, h.salary, d.avg_salary
FROM high_salary h
JOIN dept_avg d ON h.department_id = d.department_id;

위 예시에서는 dept_avg와 high_salary라는 두 개의 CTE를 사용하여, 높은 급여를 받는 직원들과 해당 부서의 평균 급여를 비교합니다.

CTE의 장점

1. 가독성 향상: 복잡한 서브쿼리를 WITH 구문으로 분리하면 쿼리 구조가 명확해집니다.
2. 재사용성: 하나의 CTE를 여러 번 참조할 수 있어, 같은 서브쿼리를 반복해서 작성할 필요가 없습니다.
3. 재귀적 쿼리: CTE는 재귀적 쿼리를 작성하는 데 유용합니다.

재귀 CTE

재귀 CTE는 자기 자신을 참조하는 쿼리로, 예를 들어 계층적 데이터(부모-자식 관계)를 처리하는 데 사용됩니다.

WITH RECURSIVE org_chart AS (
    SELECT employee_id, manager_id, name
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.name
    FROM employees e
    INNER JOIN org_chart o ON e.manager_id = o.employee_id
)
SELECT * FROM org_chart;

이 예시는 employees 테이블에서 상위 관리자부터 시작해, 각 직원들의 관리자 관계를 재귀적으로 탐색하여 전체 조직도를 출력합니다.

WITH의 사용 범위

• WITH는 SELECT, INSERT, UPDATE, DELETE 쿼리와 함께 사용될 수 있습니다.
• 서브쿼리가 복잡할 때 유용하고, 쿼리 성능을 향상시킬 수 있습니다.

CTE는 SQL 쿼리의 가독성을 높이고 복잡한 연산을 쉽게 표현할 수 있도록 돕는 강력한 도구입니다.