이미 알다시피 CTE를 쓰면 코드가 훨씬 읽기 쉬워져. 근데 항상 CTE가 답일까? 가끔은 간단한 서브쿼리가 더 빠르고 효율적일 때도 있어. 각각의 도구가 언제 빛을 발하는지, 그리고 어떻게 선택해야 할지 같이 알아보자.
서브쿼리: 빠르고 간단하게
서브쿼리가 뭐였는지 기억나? SQL 안에 또 다른 SQL이 들어가는 거야. 메인 쿼리 안에 바로 넣어서 "그 자리에서" 실행돼. 한 번만 쓰는 간단한 작업에 딱 좋아:
-- 평균 가격보다 비싼 상품 찾기
SELECT product_name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);
여기서 서브쿼리는 평균 가격을 한 번만 계산하고 끝이야. 쓸데없는 구조도 필요 없어.
성능: 누가 더 빠를까?
서브쿼리는 단순한 작업에서는 속도에서 이기는 경우가 많아. PostgreSQL은 이런 서브쿼리를 "즉석에서" 최적화해주거든, 특히 한 번만 실행될 때:
-- 빠름: 서브쿼리가 한 번만 실행됨
SELECT customer_id, order_total
FROM orders
WHERE order_date = (SELECT MAX(order_date) FROM orders);
CTE는 기본적으로 materialize 돼 — PostgreSQL이 먼저 CTE 결과를 계산해서 임시 테이블로 저장한 다음에 써. 이러면 단순한 쿼리는 오히려 느려질 수도 있어:
-- 느림: CTE가 임시 테이블로 materialize됨
WITH latest_date AS (
SELECT MAX(order_date) AS max_date FROM orders
)
SELECT customer_id, order_total
FROM orders, latest_date
WHERE order_date = max_date;
하지만! PostgreSQL 12부터는 materialize를 직접 컨트롤할 수 있어:
-- 강제로 materialize 안 함
WITH latest_date AS NOT MATERIALIZED (
SELECT MAX(order_date) AS max_date FROM orders
)
SELECT customer_id, order_total
FROM orders, latest_date
WHERE order_date = max_date;
여러 번 쓸 때: 여기선 CTE가 짱
같은 중간 결과가 여러 번 필요할 땐, CTE가 진짜 유용해:
-- 서브쿼리: 같은 로직을 두 번 반복
SELECT
(SELECT COUNT(*) FROM orders WHERE status = 'completed') AS completed_orders,
(SELECT COUNT(*) FROM orders WHERE status = 'completed') * 100.0 / COUNT(*) AS completion_rate
FROM orders;
-- CTE: 한 번 계산해서 두 번 사용
WITH completed_orders AS (
SELECT COUNT(*) AS count FROM orders WHERE status = 'completed'
)
SELECT
co.count AS completed_orders,
co.count * 100.0 / (SELECT COUNT(*) FROM orders) AS completion_rate
FROM completed_orders co;복잡한 분석: CTE가 점수 따는 부분
여러 단계로 분석해야 할 때 CTE가 진짜 깔끔하게 정리해줘. 판매 리포트 예시를 보자:
서브쿼리로 하면 (머릿속이 복잡해짐):
SELECT
category,
revenue,
revenue * 100.0 / (
SELECT SUM(p.price * oi.quantity)
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE EXTRACT(year FROM o.order_date) = 2024
) AS revenue_share
FROM (
SELECT
p.category,
SUM(p.price * oi.quantity) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE EXTRACT(year FROM o.order_date) = 2024
GROUP BY p.category
) category_revenue;
CTE로 하면 (정리정돈 끝):
WITH yearly_sales AS (
SELECT
p.category,
p.price * oi.quantity AS sale_amount
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN orders o ON oi.order_id = o.order_id
WHERE EXTRACT(year FROM o.order_date) = 2024
),
category_revenue AS (
SELECT
category,
SUM(sale_amount) AS revenue
FROM yearly_sales
GROUP BY category
),
total_revenue AS (
SELECT SUM(sale_amount) AS total FROM yearly_sales
)
SELECT
cr.category,
cr.revenue,
cr.revenue * 100.0 / tr.total AS revenue_share
FROM category_revenue cr, total_revenue tr;
재귀: CTE만의 영역
계층 구조 같은 건 서브쿼리로는 못 해.
재귀 CTE만이 "매니저의 모든 부하 직원 찾기" 같은 걸 할 수 있어:
WITH RECURSIVE employee_hierarchy AS (
-- CEO부터 시작
SELECT employee_id, manager_id, name, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- 각 단계의 부하 직원 추가
SELECT e.employee_id, e.manager_id, e.name, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT * FROM employee_hierarchy ORDER BY level, name;디버깅과 코드 유지보수
CTE는 단계별로 쉽게 디버깅할 수 있어:
-- 첫 단계 확인
WITH active_customers AS (
SELECT customer_id FROM customers WHERE status = 'active'
)
SELECT COUNT(*) FROM active_customers; -- 로직이 맞는지 확인
-- 두 번째 단계 추가
WITH active_customers AS (...),
recent_orders AS (
SELECT customer_id, COUNT(*) as order_count
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id
)
SELECT COUNT(*) FROM recent_orders; -- 이것도 확인서브쿼리는 디버깅이 더 힘들어 — 컨텍스트에서 빼내야 하거든.
실전 꿀팁
서브쿼리를 쓰면 좋은 경우:
- 로직이 간단하고 한 줄로 끝날 때
- 단순 작업에서 최대 성능이 필요할 때
- 중간 결과를 한 번만 쓸 때
- 데이터 양이 적을 때
CTE를 쓰면 좋은 경우:
- 쿼리가 복잡하고 논리적으로 단계가 나눠질 때
- 중간 결과를 여러 번 써야 할 때
- 코드의 가독성과 유지보수가 중요할 때
- 계층 구조(재귀 CTE)를 다룰 때
- 복잡한 로직을 단계별로 디버깅할 때
황금 규칙
처음엔 서브쿼리로 시작해. 근데 읽기 힘들거나 로직이 반복되면 CTE로 바꿔. 미래의 동료(혹은 6개월 뒤의 너)가 고마워할 거야!
GO TO FULL VERSION