Temporary Tables vs CTEs vs Views vs Derived Tables

Concept

All four give a name to a query result. They differ on three axes that actually matter: how long they live, whether the work is repeated, and whether they can be indexed.

The same job, four ways

-- Derived table: fine for one step, unreadable when nested
SELECT c.name, t.lifetime_value
FROM   customers c
JOIN  (SELECT customer_id, SUM(total) AS lifetime_value
       FROM   orders WHERE status <> 'cancelled'
       GROUP BY customer_id) t ON t.customer_id = c.id;

-- CTE: same plan, far easier to read and to extend
WITH totals AS (
    SELECT customer_id, SUM(total) AS lifetime_value
    FROM   orders WHERE status <> 'cancelled'
    GROUP BY customer_id
)
SELECT c.name, t.lifetime_value
FROM   customers c
JOIN   totals t ON t.customer_id = c.id;

-- View: the definition becomes shared vocabulary for every query
CREATE OR REPLACE VIEW v_customer_totals AS
SELECT customer_id, SUM(total) AS lifetime_value
FROM   orders WHERE status <> 'cancelled'
GROUP BY customer_id;

SELECT c.name, t.lifetime_value
FROM   customers c JOIN v_customer_totals t ON t.customer_id = c.id;

-- Temporary table: computed once, indexed, reused by several statements
CREATE TEMPORARY TABLE tmp_totals AS
SELECT customer_id, SUM(total) AS lifetime_value
FROM   orders WHERE status <> 'cancelled'
GROUP BY customer_id;
CREATE INDEX idx_tmp_totals ON tmp_totals (customer_id);

The decision rule

1. One statement, one use? CTE. It is the most readable and costs nothing extra.
2. Recursive? CTE - nothing else can do it.
3. Wanted by many queries and many people? View. It becomes a shared definition.
4. Expensive, reused across several statements, or needs an index? Temporary table.
5. Trivial single step inside one query? Derived table is fine.

The one performance subtlety

-- If `expensive` is referenced twice, the engine may run it twice
WITH expensive AS (
    SELECT customer_id, SUM(total) AS v FROM orders GROUP BY customer_id
)
SELECT a.customer_id, a.v, b.v
FROM   expensive a
JOIN   expensive b ON b.customer_id = a.customer_id;

A CTE is not a guaranteed cache. MySQL may materialise it or merge it; PostgreSQL materialised CTEs by default until version 12 and now inlines them unless you write MATERIALIZED. If you need a single evaluation with certainty, a temporary table gives it.

Important rules

• Only a temporary table actually stores rows; the other three are query definitions.
• Only a temporary table can be indexed.
• Only a CTE can be recursive.
• Only a view persists between sessions and is visible to other users.
• A derived table needs an alias; a CTE needs a name; both vanish at the end of the statement.

Common mistakes

• Assuming a CTE is materialised once and using it as a cache.
• Creating a temporary table for a single simple step, adding DDL cost for nothing.
• Nesting derived tables three deep instead of chaining CTEs.
• Creating a view for logic used by exactly one query, cluttering the schema.
• Expecting a view to make anything faster - it changes readability, not work.

Best practices

• Default to CTEs. Move to a temporary table only when you need reuse across statements, an index, or a guaranteed single evaluation.
• Promote a CTE to a view when three or more queries need the same definition.
• Name every intermediate result after what it contains.
• Measure before switching from a CTE to a temporary table; read the plan first.

Practice

1. Choose the right construct for: a recursive category tree, a shared "active customer" definition, an expensive aggregate used by four report queries, a one off filter inside a single query.
2. Rewrite a two level nested derived table as chained CTEs.
3. Explain when a CTE referenced twice can be slower than a temporary table.