# 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.

- Source: https://akshaythakare.com/notes/sql/ctes-subqueries-temp-tables
- Topic: SQL
- Level: beginner
- Reading time: 2 min
- Tags: sql, cte, readability, interview
- Updated: 2026-10-11

## Same logic, three shapes

```sql
-- 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.

```sql
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;
```

> **Tip: Interview framing**
>
> "A CTE is for readability; a temp table is for reuse and staging. I check the query plan before assuming either one is faster."