서브쿼리와 CTE(WITH) — 가독성 좋은 쿼리

JOIN 만으로 안 되는 쿼리가 등장하기 시작합니다 — "각 카테고리에서 매출 1위 상품" 같은 것. 답은 서브쿼리(쿼리 안의 쿼리) 와 CTE(가독성 좋은 명명 쿼리). 10편은 둘의 형태와 언제 무엇을 쓸지 정리하고, 재귀 CTE 까지 미리 봅니다.

서브쿼리 — 형태에 따라 3가지

① scalar 서브쿼리 — 단일 값

-- WHERE 안에서
SELECT * FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

-- SELECT 안에서 (각 행마다 실행됨 — 비싸기 쉬움)
SELECT o.id,
       o.total,
       (SELECT name FROM users WHERE id = o.user_id) AS user_name
FROM   orders o;

② row 서브쿼리 — 한 행, 여러 컬럼

SELECT * FROM orders
WHERE (user_id, total) = (SELECT id, max_total FROM ...);

③ table 서브쿼리 — 여러 행 + 여러 컬럼 (FROM 절에)

-- 카테고리별 매출 1위
SELECT *
FROM   (
  SELECT category_id, name, total,
         ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY total DESC) AS rn
  FROM   products
) ranked
WHERE  rn = 1;

IN · EXISTS · NOT EXISTS

-- IN (값 비교)
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE active = true);

-- EXISTS (존재만 확인 — 매치 즉시 멈춤, 보통 더 빠름)
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.active);

-- NOT EXISTS (anti-join, NULL 함정 회피)
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

WITH (CTE) — 명명된 임시 쿼리

WITH active_users AS (
  SELECT id, name FROM users WHERE active = true
),
big_orders AS (
  SELECT user_id, SUM(total) AS rev
  FROM   orders
  WHERE  total >= 50000
  GROUP BY user_id
)
SELECT u.name, COALESCE(o.rev, 0) AS rev
FROM   active_users u
LEFT JOIN big_orders o ON o.user_id = u.id
ORDER BY rev DESC;

같은 결과를 서브쿼리로 쓰면 중첩이 깊어집니다. WITH 로 분해하면 위에서 아래로 읽힘. 큰 쿼리는 거의 항상 WITH 로 구조를 잡는 게 가독성 정답.

여러 CTE 체이닝 — 단계별 데이터 변환

-- 분석 파이프라인 — 단계별 명확
WITH raw AS (
  SELECT user_id, total, created_at
  FROM   orders
  WHERE  created_at >= '2026-01-01'
),
by_month AS (
  SELECT user_id,
         date_trunc('month', created_at) AS month,
         SUM(total) AS rev
  FROM   raw
  GROUP BY user_id, date_trunc('month', created_at)
),
top_per_month AS (
  SELECT *,
         RANK() OVER (PARTITION BY month ORDER BY rev DESC) AS rk
  FROM   by_month
)
SELECT month, user_id, rev
FROM   top_per_month
WHERE  rk <= 3
ORDER BY month, rk;

재귀 CTE — 트리·계층 구조

-- 직원-매니저 트리, "준성" 의 모든 부하 직원 (간접 포함)
WITH RECURSIVE subordinates AS (
  -- 1) 시작점
  SELECT id, name, manager_id, 1 AS depth
  FROM   employees
  WHERE  name = '준성'

  UNION ALL

  -- 2) 재귀 — 직전 결과의 부하 직원
  SELECT e.id, e.name, e.manager_id, s.depth + 1
  FROM   employees e
  JOIN   subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates ORDER BY depth, name;

재귀 CTE 의 패턴: (anchor) UNION ALL (recursion). 트리·그래프·번호 시퀀스 생성 등에 사용. 무한 루프 위험 — 항상 depth < N 같은 조건이나 데이터에 사이클 없는 게 보장돼야 안전.

generate_series — 숫자/날짜 시퀀스

-- 1부터 10까지
SELECT * FROM generate_series(1, 10);

-- 날짜 (월별)
SELECT * FROM generate_series(
  '2026-01-01'::date,
  '2026-12-31'::date,
  '1 month'::interval
);

-- "지난 30일 매일 매출" — 데이터 없는 날도 0 으로 채우기
WITH days AS (
  SELECT generate_series(
    CURRENT_DATE - INTERVAL '29 days',
    CURRENT_DATE,
    '1 day'::interval
  )::date AS d
)
SELECT d.d, COALESCE(SUM(o.total), 0) AS rev
FROM   days d
LEFT JOIN orders o ON o.created_at::date = d.d
GROUP BY d.d
ORDER BY d.d;

서브쿼리 vs CTE vs JOIN — 선택 가이드