SQL CTE(WITH) — 조회 대상 목록을 테이블처럼 다루기

여러 ID를 한 번에 검사할 때는 조회 대상 목록과 실제 테이블의 데이터를 비교해야 한다. 이때 목록 자체를 SQL 안에서 테이블처럼 다루면 쿼리를 읽기 쉬워진다.

1. 대상 목록을 테이블처럼 만들기

WITH target(id) AS (
    SELECT UNNEST(ARRAY['001', '002', '003']::varchar[])
)
SELECT * FROM target;

결과:

id
001
002
003

ARRAY는 값들을 배열로 묶고, PostgreSQL의 UNNEST는 배열 원소를 각각의 행으로 펼친다. ::varchar[]는 문자열 배열로 타입을 지정하는 PostgreSQL 문법이다.

여기서 WITH target(id) AS (...)의 의미는 다음과 같다.

• WITH: CTE 정의 시작
• target: 이후 쿼리에서 테이블처럼 참조할 이름
• (id): CTE 결과의 첫 번째 컬럼 이름
• AS (...): 결과를 만드는 쿼리

다음처럼 SELECT 안에서 컬럼 이름을 지정해도 된다.

WITH target AS (
    SELECT UNNEST(ARRAY['001', '002', '003']::varchar[]) AS id
)
SELECT target.id FROM target;

2. CTE는 무엇인가?

CTE(Common Table Expression)는 한 SQL 문 안에서 이름을 붙여 참조하는 결과 집합이다. 실제 영구 테이블을 생성하지 않으며, 같은 SQL 문 안의 후속 쿼리에서 사용할 수 있다.

서브쿼리를 FROM에 직접 넣는 방식과 비교하면 구조가 잘 보인다.

-- FROM 서브쿼리
SELECT *
FROM (SELECT id FROM users WHERE active = true) AS target;

-- CTE
WITH target AS (
    SELECT id FROM users WHERE active = true
)
SELECT * FROM target;

두 예제는 같은 행을 조회한다. 다만 CTE라고 해서 항상 더 빠른 것은 아니다. PostgreSQL은 조건에 따라 CTE를 상위 쿼리에 통합하거나 결과를 Materialize할 수 있으므로, 성능은 실행 계획으로 판단해야 한다.

3. 여러 CTE를 연결해 누락 데이터 확인하기

WITH target(id) AS (
    SELECT UNNEST(ARRAY['001', '002', '003']::varchar[])
),
target_rows AS (
    SELECT
        target.id AS target_id,
        outcomes.id AS found_id,
        outcomes.cci_score,
        CASE
            WHEN outcomes.id IS NULL THEN 'MISSING'
            ELSE 'FOUND'
        END AS status
    FROM target
    LEFT JOIN postoperative_outcomes AS outcomes
        ON outcomes.id = target.id
)
SELECT *
FROM target_rows
ORDER BY target_id;

위 예제의 postoperative_outcomes는 설명을 위한 테이블 이름이다.

쿼리는 다음 순서로 읽으면 된다.

target: 조회할 ID 목록 생성
  ↓ (다음 CTE가 target을 참조)
target_rows: 실제 테이블과 LEFT JOIN
  ↓ (최종 SELECT가 target_rows를 참조)
ID별 존재 여부와 CCI Score 출력

LEFT JOIN은 왼쪽의 target 행을 유지하므로 실제 테이블에 없는 ID도 결과에 남는다.

가령 다음과 같은 결과가 나올 수 있다.

target_id	found_id	cci_score	status
001	001	12.34	FOUND
002	NULL	NULL	MISSING
003	003	NULL	FOUND

여기서 MISSING은 행을 찾지 못했다는 뜻이다. FOUND인데 cci_score가 NULL이면 행은 있지만 점수 값이 비어 있다는 뜻이다. 둘을 구분해야 한다.

점수 입력 여부까지 분류하려면 다음처럼 바꿀 수 있다.

CASE
    WHEN outcomes.id IS NULL THEN 'MISSING'
    WHEN outcomes.cci_score IS NULL THEN 'CCI_NULL'
    ELSE 'CCI_EXISTS'
END AS status

단, outcomes.id가 유일하지 않다면 하나의 대상 ID에서 결과 행이 여러 개 나올 수 있다. 이 쿼리는 조회만 수행하며 데이터를 변경하지 않는다.

4. CTE의 유효 범위는 한 SQL 문장이다

CTE는 바로 뒤에 오는 SQL 문 하나에서만 유효하다.

WITH target(id) AS (
    SELECT UNNEST(ARRAY['001', '002', '003']::varchar[])
)
SELECT *
FROM target;

위 쿼리에서 target은 SELECT 문 안에서만 사용할 수 있다. 다음 문장에서 다시 target을 참조할 수는 없다.

-- 이 문장에서는 앞 문장의 target CTE를 사용할 수 없다.
UPDATE postoperative_outcomes AS outcomes
SET cci_score = 0
FROM target
WHERE outcomes.id = target.id;

따라서 같은 대상 목록을 여러 SELECT나 UPDATE에서 반복해서 써야 한다면 선택지는 둘이다.

1. 각 SQL 문마다 CTE를 다시 정의한다.
2. 대상 목록을 TEMP TABLE에 한 번 넣고 재사용한다.

CTE는 세션 전체에 남는 임시 테이블이 아니라, 단일 SQL 문 안에서 이름을 붙여 쓰는 결과 집합이다.

5. 여러 문장에서 재사용하려면 TEMP TABLE을 쓴다

검수 쿼리, 업데이트 쿼리, 사후 확인 쿼리에서 같은 ID 목록을 모두 써야 한다면 TEMP TABLE이 더 읽기 쉽다.

BEGIN;

CREATE TEMP TABLE target_ids (
    id varchar(50) PRIMARY KEY
) ON COMMIT DROP;

INSERT INTO target_ids (id) VALUES
    ('001'),
    ('002'),
    ('003');

-- 사전 검수
SELECT COUNT(*) AS target_rows
FROM target_ids;

-- 실제 업데이트
UPDATE postoperative_outcomes AS outcomes
SET cci_score = 0
FROM target_ids AS target
WHERE outcomes.id = target.id;

COMMIT;

CREATE TEMP TABLE은 View를 만드는 문법이 아니다. 현재 DB 세션에서만 보이는 작업용 임시 테이블을 만든다. ON COMMIT DROP을 붙이면 트랜잭션이 COMMIT될 때 자동으로 삭제된다.

방식	유효 범위	적합한 상황
CTE	바로 뒤 SQL 문 하나	한 번의 SELECT/UPDATE 안에서만 대상 목록을 쓸 때
TEMP TABLE	세션 또는 트랜잭션	여러 SELECT/UPDATE에서 같은 대상 목록을 재사용할 때

단순히 한 번 조회할 목록이면 CTE로 충분하다. 같은 목록을 여러 문장에서 재사용해야 하면 TEMP TABLE이 중복을 줄인다.

6. 요약 집계와 상세 조회는 분리하는 편이 낫다

대상 목록이 실제 테이블에 몇 개 매칭되는지 한 줄로 확인하려면 집계 쿼리를 쓸 수 있다.

SELECT
    COUNT(*) AS target_rows,
    COUNT(outcomes.id) AS matched_rows,
    ARRAY_AGG(target.id ORDER BY target.id)
        FILTER (WHERE outcomes.id IS NULL) AS missing_ids
FROM target_ids AS target
LEFT JOIN postoperative_outcomes AS outcomes
    ON outcomes.id = target.id;

각 값의 의미는 다음과 같다.

• COUNT(*): 검사 대상 전체 개수
• COUNT(outcomes.id): 실제 테이블에서 찾은 행 개수. NULL은 세지 않는다.
• ARRAY_AGG(...) FILTER (WHERE ...): 조건에 맞는 행만 배열로 모은다.

예를 들어 target_rows = 3, matched_rows = 2, missing_ids = {002}라면 검사 대상 3개 중 1개가 실제 테이블에 없다는 뜻이다.

여기에 일반 컬럼을 바로 섞으면 에러가 날 수 있다.

SELECT
    COUNT(*) AS target_rows,
    COUNT(outcomes.id) AS matched_rows,
    outcomes.cci_score
FROM target_ids AS target
LEFT JOIN postoperative_outcomes AS outcomes
    ON outcomes.id = target.id;

PostgreSQL은 보통 다음과 같은 에러를 낸다.

column "outcomes.cci_score" must appear in the GROUP BY clause
or be used in an aggregate function

이유는 COUNT(*)가 여러 행을 한 줄로 압축하는 반면, outcomes.cci_score는 행마다 값이 다를 수 있기 때문이다. 최종 결과 한 줄에 어느 행의 cci_score를 보여줘야 하는지 정할 수 없다.

이럴 때는 목적을 나누는 편이 좋다. 요약은 집계 쿼리로 보고, 현재 컬럼 값은 상세 조회 쿼리로 따로 본다.

SELECT
    target.id AS target_id,
    outcomes.id AS found_id,
    outcomes.complication_other,
    outcomes.cci_score,
    CASE
        WHEN outcomes.id IS NULL THEN 'MISSING'
        ELSE 'FOUND'
    END AS status
FROM target_ids AS target
LEFT JOIN postoperative_outcomes AS outcomes
    ON outcomes.id = target.id
ORDER BY target.id;

GROUP BY는 컬럼별로 행을 묶어 통계를 낼 때 사용한다. 전체 대상 개수와 행별 현재값을 동시에 보고 싶다는 이유로 억지로 GROUP BY를 추가하면, 의도와 다른 단위로 결과가 쪼개질 수 있다.

7. 언제 유용한가?

• 조회 대상 ID 목록을 실제 테이블과 비교할 때
• 여러 단계의 조회를 의미 있는 이름으로 나눌 때
• 같은 중간 결과를 한 SQL 문 안에서 재참조할 때
• 여러 검수·수정 문장에서 같은 대상 목록을 반복해서 써야 할 때
• 집계 요약과 상세 조회의 역할을 분리하고 싶을 때

핵심은 CTE가 테이블을 새로 만드는 문법이 아니라, 쿼리 결과에 이름을 붙여 다른 쿼리 부분에서 테이블처럼 참조하도록 하는 문법이라는 점이다. 여러 SQL 문에서 같은 대상 목록을 재사용해야 한다면 CTE를 반복 정의하거나 TEMP TABLE을 사용한다.

PostgreSQL에서 실행 방식이 궁금하다면 EXPLAIN 노드·필드 사전의 CTE Scan 항목을 함께 참고할 수 있다.