CTE

WITH 절로 쿼리에 이름을 붙여 뒤에서 테이블처럼 쓰는 문법.

왜 쓰나 — 중첩을 평평하게

-- ❌ 서브쿼리 중첩 — 안에서 밖으로 읽어야 한다
SELECT * FROM (
  SELECT dept, avg(salary) a FROM (
    SELECT * FROM emp WHERE hired_at >= '2020-01-01'
  ) x GROUP BY dept
) y WHERE a > 5000;

-- ✅ CTE — 위에서 아래로 읽힌다
WITH recent AS (
  SELECT * FROM emp WHERE hired_at >= '2020-01-01'
), by_dept AS (
  SELECT dept, avg(salary) AS a FROM recent GROUP BY dept
)
SELECT * FROM by_dept WHERE a > 5000;

같은 CTE를 여러 번 참조할 수 있다는 것도 장점이다. 서브쿼리는 쓸 때마다 다시 적어야 한다.

재귀 CTE — 계층 구조를 순회한다

-- 조직도에서 특정 사원의 모든 하위 조직을 찾는다
WITH RECURSIVE subtree AS (
    SELECT id, name, manager_id, 1 AS depth      -- ① 시작점(앵커)
      FROM emp WHERE id = 1
  UNION ALL
    SELECT e.id, e.name, e.manager_id, s.depth + 1   -- ② 자기 자신을 참조해 확장
      FROM emp e JOIN subtree s ON e.manager_id = s.id
)
SELECT * FROM subtree ORDER BY depth;

• ① 앵커로 시작한다
• ② 직전 결과를 입력으로 다시 돌린다
• ③ 더 나오는 행이 없으면 멈춘다

무한 루프 주의 — 데이터에 순환이 있으면 영원히 돈다. 경로를 배열로 들고 다니며 중복을 막는 것이 표준 처방이다.

WITH RECURSIVE t AS (
    SELECT id, ARRAY[id] AS path FROM emp WHERE id = 1
  UNION ALL
    SELECT e.id, t.path || e.id FROM emp e JOIN t ON e.manager_id = t.id
     WHERE NOT e.id = ANY(t.path)          -- 이미 지나온 노드는 제외
)
SELECT * FROM t;

성능에서 중요한 차이 — 최적화 장벽

• PostgreSQL 11 이하 — CTE 는 항상 별도로 실행되고 결과가 물질화됐다(optimization fence)
  • CTE 밖의 조건이 안으로 밀려 들어가지 못해 느릴 수 있었다
• PostgreSQL 12 이상 — 기본이 인라인. 필요하면 명시한다
• MySQL 8.0 — 기본이 인라인(MERGE), 필요 시 물질화

WITH big AS MATERIALIZED   ( ... )   -- 강제로 한 번만 계산하게 한다
WITH big AS NOT MATERIALIZED ( ... ) -- 강제로 인라인

같은 CTE를 여러 번 참조하는데 계산이 비싸면 MATERIALIZED 가 유리하고, 한 번만 쓰면서 바깥 조건으로 좁혀지길 원하면 인라인이 낫다.

면접 함정

• ❌ "CTE는 임시 테이블이다" → 디스크에 만들어지는 임시 테이블과 다르다. 대개 쿼리 실행 중에만 존재한다.
• ❌ "CTE로 바꾸면 빨라진다" → 가독성 도구다. 성능은 인라인 여부와 계획에 달렸다.