Common Table Expressions (CTE)
A CTE (WITH) lets you break a complex query into named steps. This improves reviewability and reduces repeated subqueries.
CTE Workflow
flowchart LR
A[Base tables] --> B[CTE step 1]
B --> C[CTE step 2]
C --> D[Final SELECT]
Syntax and Usage
| Pattern | Use case | Example |
|---|---|---|
| Single CTE | One reusable intermediate set | WITH recent_orders AS (...) SELECT ... |
| Multiple CTEs | Multi-step transformations | WITH a AS (...), b AS (...) SELECT ... |
WITH recent_orders AS (
SELECT customer_id, total_amount
FROM orders
WHERE order_date >= '2026-01-01'
)
SELECT customer_id, SUM(total_amount) AS total_spend
FROM recent_orders
GROUP BY customer_id;
Practical Example
WITH paid_orders AS (
SELECT customer_id, total_amount
FROM orders
WHERE status = 'paid'
),
ranked_customers AS (
SELECT customer_id,
SUM(total_amount) AS spend
FROM paid_orders
GROUP BY customer_id
)
SELECT customer_id, spend
FROM ranked_customers
WHERE spend >= 10000
ORDER BY spend DESC;
| customer_id | spend |
|---|---|
| 1024 | 24300.00 |
| 2031 | 13120.00 |
Common Pitfalls
| Pitfall | Consequence | Prevention |
|---|---|---|
| Treating CTE as persisted object | Query fails outside statement | Remember CTE scope is one statement |
| Over-stacking too many CTE layers | Hard to maintain query plans | Keep 2-4 meaningful layers, then refactor |
| Ignoring indexes on base tables | Slow CTE execution | Tune source table indexes used in joins/filters |
Quick Reference
WITH cte AS (SELECT ...)
SELECT ... FROM cte;
WITH a AS (...), b AS (...)
SELECT ... FROM b;