CTEs vs Subqueries vs Temp Tables
When to use each, what the optimizer does with them, and how to write long transformations that stay readable.
SQLBeginner2 min readUpdated 11 Oct 2026
Same logic, three shapes
-- subquery
SELECT * FROM (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY 1) t WHERE total > 1000;
-- CTE
WITH totals AS (
SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY 1
)
SELECT * FROM totals WHERE total > 1000;
-- temp table
CREATE TEMPORARY TABLE totals AS
SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY 1;
SELECT * FROM totals WHERE total > 1000;How they differ
| Subquery | CTE | Temp table | |
|---|---|---|---|
| Readability | Poor when nested | Reads top to bottom | Good, split into statements |
| Reuse in one query | Repeat it | Reference by name | Reference by name |
| Stored? | No | Usually inlined by the optimizer | Yes, for the session |
| Reuse across statements | No | No | Yes |
Most modern engines inline a CTE like a view. Writing a CTE does not by itself cache the result.
Rules of thumb
- Use CTEs to name steps:
cleaned,deduped,daily,final. Each should do one thing. - Use a temp table when several later statements read the same expensive result, or to stage data before a
MERGE. - Avoid deep nesting. If you indent past three levels, switch to CTEs.
WITH cleaned AS (...),
deduped AS (... QUALIFY ROW_NUMBER() OVER (...) = 1),
daily AS (SELECT business_date, SUM(net) AS net FROM deduped GROUP BY 1)
SELECT * FROM daily;Finished this note?