CS2026.10.026 min read

CTE: WITH 절로 쿼리를 이름 붙은 단계로 분리하는 임시 결과 집합

CTE(Common Table Expression)는 WITH 절로 쿼리 하나의 범위 안에서 이름 붙인 임시 결과 집합을 정의하는 SQL 문법이다. 복잡한 쿼리를 단계별로 나눠 가독성을 높이고, 재귀 CTE로 계층 구조와 그래프 탐색까지 표현할 수 있다. PostgreSQL 12부터 CTE 인라이닝 규칙이 바뀌었으므로 MATERIALIZED 옵션과 함께 성

By 송민성

1. 개념

공통 테이블 식(CTE, Common Table Expression)은 WITH 절로 정의하는, 이름 붙은 임시 결과 집합이다. 하나의 SQL 문(SELECT, INSERT, UPDATE, DELETE) 안에서만 존재하며, 문이 끝나면 사라진다. 뷰(View)처럼 이름으로 참조하지만 스키마에 저장되지 않는다.

sql

WITH 이름 AS (
    SELECT ...
)
SELECT * FROM 이름;

CTE는 크게 두 종류다.

비재귀 CTE: 서브쿼리에 이름을 붙여 뒤쪽 쿼리에서 참조한다.
재귀 CTE(Recursive CTE): 자기 자신을 참조해 반복적으로 행을 만들어낸다. WITH RECURSIVE를 사용한다.

2. 왜 사용하는가

가독성: 중첩 서브쿼리를 위에서 아래로 읽히는 단계로 풀어쓸 수 있다.
재사용: 같은 결과 집합을 한 쿼리 안에서 여러 번 참조할 수 있다.
계층/그래프 표현: 조직도, 카테고리 트리, 댓글 스레드처럼 깊이를 알 수 없는 구조를 재귀 CTE로 조회한다.
데이터 변경 파이프라인: PostgreSQL에서는 INSERT ... RETURNING 결과를 CTE로 받아 다음 문장에 연결할 수 있다.

3. 동작 원리

비재귀 CTE

CTE는 쿼리 안에서 논리적으로 먼저 정의된 결과 집합처럼 동작한다. 다만 물리적으로 먼저 실행되어 저장된다는 뜻은 아니다. PostgreSQL 12 이후에는 다음 조건을 모두 만족하면 CTE를 메인 쿼리에 인라이닝(inlining)한다.

비재귀이다.
부작용이 없다(데이터 변경 문이 아니고 volatile 함수를 포함하지 않는다).
쿼리에서 한 번만 참조된다.

두 번 이상 참조되면 기본적으로 한 번 계산한 결과를 재사용하는 구체화(materialization)가 일어난다. MATERIALIZED 또는 NOT MATERIALIZED로 동작을 명시할 수 있다.

재귀 CTE

재귀 CTE는 UNION 또는 UNION ALL로 두 부분을 연결한다.

앵커 멤버(anchor member): 재귀 없이 초기 행을 만든다.
재귀 멤버(recursive member): CTE 자신을 참조해 다음 행을 만든다.

실행 절차는 다음과 같다.

앵커 멤버를 실행해 결과를 작업 테이블(working table)에 넣는다.
작업 테이블을 CTE 이름으로 참조해 재귀 멤버를 실행한다.
재귀 멤버가 새 행을 만들면 결과에 추가하고, 그 새 행들을 다음 작업 테이블로 삼아 2번을 반복한다.
재귀 멤버가 행을 하나도 만들지 않으면 종료한다.

4. 코드 예제

비재귀: 단계별 집계

sql

CREATE TABLE orders (
    id          serial PRIMARY KEY,
    user_id     int           NOT NULL,
    amount      numeric(10,2) NOT NULL,
    ordered_at  date          NOT NULL
);

INSERT INTO orders (user_id, amount, ordered_at) VALUES
    (1, 120.00, '2026-09-01'),
    (1,  80.00, '2026-09-15'),
    (2, 300.00, '2026-09-03'),
    (3,  45.00, '2026-09-20'),
    (3,  55.00, '2026-09-21');

WITH user_total AS (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
),
avg_total AS (
    SELECT AVG(total) AS avg_value FROM user_total
)
SELECT u.user_id, u.total
FROM user_total u, avg_total a
WHERE u.total > a.avg_value
ORDER BY u.total DESC;

user_total을 먼저 정의하고, 이를 avg_total이 참조한 뒤 최종 쿼리가 둘을 함께 사용한다. 평균(약 200)보다 큰 사용자만 조회된다.

재귀: 조직도 탐색

sql

CREATE TABLE employees (
    id         int PRIMARY KEY,
    name       text NOT NULL,
    manager_id int REFERENCES employees(id)
);

INSERT INTO employees VALUES
    (1, 'CEO',   NULL),
    (2, 'CTO',   1),
    (3, 'CFO',   1),
    (4, '백엔드 팀장', 2),
    (5, '프론트 팀장', 2),
    (6, '개발자 A', 4);

WITH RECURSIVE org AS (
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, o.depth + 1
    FROM employees e
    JOIN org o ON e.manager_id = o.id
)
SELECT repeat('  ', depth) || name AS tree, depth
FROM org
ORDER BY depth, id;

앵커가 최상위(CEO)를 만들고, 재귀 멤버가 직속 부하를 한 단계씩 찾아 내려간다.

순환 방지

그래프에 사이클이 있으면 재귀가 끝나지 않는다. 방문 경로를 배열로 추적해 막는다.

sql

WITH RECURSIVE walk AS (
    SELECT id, manager_id, ARRAY[id] AS path
    FROM employees
    WHERE id = 6

    UNION ALL

    SELECT e.id, e.manager_id, w.path || e.id
    FROM employees e
    JOIN walk w ON e.id = w.manager_id
    WHERE NOT e.id = ANY(w.path)
)
SELECT * FROM walk;

개발자 A에서 위쪽 상급자로 거슬러 올라가며, 이미 지나간 id는 다시 방문하지 않는다.

구체화 제어

sql

WITH heavy AS MATERIALIZED (
    SELECT user_id, SUM(amount) AS total
    FROM orders
    GROUP BY user_id
)
SELECT * FROM heavy WHERE total > 100;

5. 시간 복잡도 또는 성능 특성

CTE 자체에 고정된 시간복잡도는 없다. 비용은 내부 쿼리와 실행 계획에 따라 달라진다.

인라이닝된 CTE: 서브쿼리와 동일하게 취급되어, 옵티마이저가 메인 쿼리의 조건을 안쪽으로 밀어 넣는 최적화(predicate pushdown)를 적용할 수 있다.
구체화된 CTE: 결과를 한 번 계산해 저장하므로 여러 번 참조할 때 재계산을 피한다. 반면 바깥 쿼리의 WHERE 조건이 안쪽으로 전달되지 않아 불필요한 행까지 계산할 수 있고, 구체화된 결과에는 원본 테이블의 인덱스가 없다.
재귀 CTE: 비용은 반복 횟수와 각 반복에서 생성되는 행 수에 비례한다. 재귀 멤버의 조인 키(위 예에서 employees.manager_id)에 인덱스가 있는지가 성능에 크게 영향을 준다.

성능은 추측하지 말고 EXPLAIN (ANALYZE)로 확인한다.

sql

EXPLAIN (ANALYZE, BUFFERS)
WITH heavy AS MATERIALIZED (
    SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id
)
SELECT * FROM heavy WHERE total > 100;

6. 실무 사용 사례

리포트 쿼리: 일별 집계 → 사용자별 집계 → 순위 계산처럼 단계가 있는 분석 쿼리를 CTE로 분리한다.
카테고리/메뉴 트리: parent_id 구조에서 특정 노드의 모든 하위 노드를 조회한다.
댓글 스레드: 대댓글의 깊이를 계산하며 정렬한다.
날짜 시퀀스 생성: 재귀 CTE나 generate_series로 빈 날짜를 채워 일별 통계에서 누락을 막는다.
데이터 이동: 삭제한 행을 보관 테이블로 옮기는 작업을 한 문장으로 처리한다.

sql

CREATE TABLE orders_archive (LIKE orders);

WITH moved AS (
    DELETE FROM orders
    WHERE ordered_at < '2026-09-10'
    RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved;

7. 주의할 점

재귀 종료 조건: 사이클이 있는 데이터에서는 무한 반복이 일어날 수 있다. 경로 추적이나 깊이 제한(WHERE depth < N)을 둔다.
`UNION`과 `UNION ALL`: UNION은 중복을 제거해 사이클을 막는 데 도움이 될 수 있지만, 매 반복마다 중복 비교 비용이 든다. 중복이 없다고 확신하면 UNION ALL이 일반적이다.
PostgreSQL 버전 차이: 11 이하에서는 CTE가 항상 구체화되어 최적화 경계(optimization fence)로 동작했다. 12부터는 위 조건에 따라 인라이닝된다. 버전에 따라 같은 쿼리의 계획이 다를 수 있다.
데이터 변경 CTE의 실행 보장: INSERT/UPDATE/DELETE를 포함한 CTE는 메인 쿼리가 결과를 참조하지 않아도 실행된다. 같은 문장 안의 여러 하위 문장은 동일한 스냅샷을 보므로, 서로의 변경 결과는 RETURNING으로만 전달된다.
DBMS별 문법 차이: MySQL은 8.0부터 CTE를 지원한다. RECURSIVE 키워드 요구 여부와 세부 옵션은 DBMS마다 다르므로 대상 DB 문서를 확인한다.
남용: 단순한 쿼리까지 CTE로 나누면 오히려 읽기 어려워진다.

8. 핵심 정리

CTE는 WITH 절로 정의하는, 하나의 SQL 문 안에서만 유효한 이름 붙은 결과 집합이다.
비재귀 CTE는 복잡한 쿼리를 단계로 나눠 가독성을 높이고, 재귀 CTE는 앵커 멤버와 재귀 멤버로 계층·그래프를 탐색한다.
PostgreSQL 12부터 비재귀·부작용 없음·단일 참조 CTE는 인라이닝되며, 여러 번 참조되면 기본적으로 구체화된다. MATERIALIZED/NOT MATERIALIZED로 제어할 수 있다.
재귀 CTE는 종료 조건과 순환 방지가 필수이고, 조인 키 인덱스가 성능을 좌우한다.
성능 판단은 EXPLAIN (ANALYZE)로 실행 계획을 확인한 뒤 내린다.