[SQL] WITH AS (Common Table Expression, CTE)

WITH AS

WITH AS 구문은 공통 테이블 표현식 (CTE, Common Table Expression) 을 정의하는 데 사용한다.

→  임시적인 결과 집합을 생성하여 복잡한 쿼리를 더 읽기 쉽게 만들고, 재사용할 수 있다.

---

기본

WITH cte_name AS (
    -- 서브쿼리 (임시 테이블)
    SELECT column1, column2
    FROM table_name
    WHERE condition
)
SELECT * FROM cte_name;

• WITH 키워드 다음에 cte_name을 정의한다.
• AS (...) 부분에는 실제로 실행될 서브쿼리를 작성한다.
• 이후 SELECT 문에서 cte_name을 테이블처럼 사용한다.

---

예제 1: CTE 사용

employees 테이블에서 연봉이 5000 이상인 직원 목록 가져오기.

WITH high_salary_employees AS ( --high_salary_employees는 일종의 임시 테이블처럼 작동하여 이후 SELECT 문에서 사용
    SELECT id, name, salary
    FROM employees
    WHERE salary >= 5000
)
SELECT * FROM high_salary_employees;

 

---

예제 2: CTE 여러 개 사용

WITH
    high_salary AS (
        SELECT id, name, salary FROM employees WHERE salary >= 5000
    ),
    low_salary AS (
        SELECT id, name, salary FROM employees WHERE salary < 5000
    )
SELECT * FROM high_salary
UNION ALL
SELECT * FROM low_salary;

 

---

예제 3: 재귀 CTE 사용 (Recursive CTE)

manager_id를 기준으로 계층 구조를 분석하기.

WITH RECURSIVE employee_hierarchy AS ( -- RECURSIVE 키워드를 사용하면 자기 자신을 참조하는 방식으로 쿼리를 실행
    -- 기초 단계 (Anchor Member)
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL  -- 최상위 관리자 (CEO)

    UNION ALL

    -- 재귀 단계 (Recursive Member)
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

 

 

---

CTE vs. 서브쿼리 vs. 임시 테이블

구분	특징
CTE (WITH AS)	코드 가독성이 좋고, 여러 번 재사용 가능
서브쿼리	한 번만 사용되며 가독성이 낮음
임시 테이블 (TEMP TABLE)	여러 번 사용 가능하지만 생성 및 삭제가 필요