Back to Subqueries & CTEs
Q6Question 6 of 7

Temporary Tables Versus CTEs

Key Concepts
  • A temporary table is a real table that exists only for the session (or transaction) and is auto-dropped at session end.
  • A CTE is a named subquery scoped to a single statement; the optimizer usually inlines it.
  • A temp table persists across multiple statements and can be indexed; a CTE cannot.
  • Use a CTE for readability and single-statement logic; use a temp table when the same intermediate result is needed across multiple statements.
  • A CTE referenced multiple times in one statement may be recomputed; a temp table is stored once.
  • Temp tables incur write I/O and catalog overhead, so do not use them when a CTE suffices.

Definition

A CTE is a named subquery scoped to one statement and usually inlined; a temp table is a real, session-lived table you reuse across many statements.
  • Temporary table – created with CREATE TEMPORARY TABLE; exists only for the session (or transaction with ON COMMIT DROP); auto-dropped at session end; can be indexed, analyzed, and queried repeatedly.
  • CTE – defined with WITH name AS (...) scoped to a single statement; the optimizer usually inlines it; disappears when the statement finishes.
  • Key difference – lifetime and scope: a temp table lives across statements, a CTE lives within one statement.
  • Indexing – a temp table can carry its own indexes; a CTE cannot.
  • Recomputation – a CTE referenced multiple times in one statement may be recomputed each time; a temp table is stored once.

Suppose the National Bank of India's month-end ETL has several steps that all need the same filtered set of this month's high-value transactions. A CTE is like writing that filter as a note inside one single instruction – the clerk reads the note while doing that one instruction and then throws it away. A temporary table is like the clerk copying those transactions onto a scratch pad that stays on the desk all shift, so every later instruction in the pipeline can reuse the same scratch pad without re-deriving it.

Understanding the Concepts

What is a temporary table?

A temporary table is a genuine table in a special temporary schema, visible only to the session that created it, and dropped automatically at session end (or transaction end if you use ON COMMIT DROP). You can insert into it, index it, query it repeatedly, and join it across several statements.

SQL
-- Create a temporary table for this month's high-value transactions
CREATE TEMPORARY TABLE tmp_high_value_txns AS
SELECT t.id, t.account_id, t.amount, t.txn_date
FROM   transactions t
WHERE  t.txn_date >= DATE_TRUNC('month', CURRENT_DATE)
  AND  t.amount > 100000;

-- Index it for fast lookups
CREATE INDEX idx_tmp_account ON tmp_high_value_txns (account_id);

-- Use it across multiple statements
SELECT account_id, COUNT(*) FROM tmp_high_value_txns GROUP BY account_id;
SELECT account_id, SUM(amount) FROM tmp_high_value_txns GROUP BY account_id;

-- Dropped automatically when the session ends

What is a CTE?

A CTE is a named subquery defined with WITH name AS (...) that exists for the duration of a single statement. It improves readability by letting you name and reuse intermediate results within that statement.

SQL
-- CTE: named subquery for one statement
WITH high_value AS (
    SELECT account_id, SUM(amount) AS total
    FROM   transactions
    WHERE  txn_date >= DATE_TRUNC('month', CURRENT_DATE)
      AND  amount > 100000
    GROUP BY account_id
)
SELECT h.account_id, a.holder_name, h.total
FROM   high_value h
JOIN   accounts a ON a.account_id = h.account_id
WHERE  h.total > 500000;
-- CTE disappears after this statement

How do they differ in lifetime and scope?

| Aspect | Temporary Table | CTE |
|---|---|---|
| Lifetime | Session (or transaction) | One statement |
| Reuse across statements | Yes | No |
| Indexable | Yes | No |
| Statistics (ANALYZE) | Yes | No |
| Write I/O | Yes (real table) | No (usually inlined) |
| Catalog overhead | Yes | No |
| Syntax | CREATE TEMPORARY TABLE | WITH name AS (...) |

When should you use a CTE?

Use a CTE for readability and for breaking one complex statement into logical steps. It is the right choice when the intermediate set is small, used once or twice within one statement, and does not need an index.

SQL
-- CTE for readability: three named stages
WITH
  active_accounts AS (
      SELECT account_id FROM accounts WHERE status = 'ACTIVE'
  ),
  large_txns AS (
      SELECT account_id, amount
      FROM   transactions
      WHERE  account_id IN (SELECT account_id FROM active_accounts)
        AND  amount > 100000
  )
SELECT account_id, SUM(amount) AS total_large
FROM   large_txns
GROUP BY account_id
ORDER BY total_large DESC;

When should you use a temporary table?

Use a temporary table when the same expensive intermediate result is needed by several separate statements, when you want to index the intermediate result, or when the dataset is large enough that materializing once beats recomputing.

SQL
-- ETL pipeline at National Bank of India: three steps use the same staged data

-- Step 1: stage this month's high-value transactions
CREATE TEMPORARY TABLE tmp_monthly_hvt AS
SELECT * FROM transactions
WHERE  txn_date >= DATE_TRUNC('month', CURRENT_DATE)
  AND  amount > 100000;

CREATE INDEX idx_tmp_hvt_account ON tmp_monthly_hvt (account_id);
ANALYZE tmp_monthly_hvt;

-- Step 2: per-account summary
SELECT account_id, COUNT(*), SUM(amount)
FROM   tmp_monthly_hvt
GROUP BY account_id;

-- Step 3: flagged for review
SELECT t.* FROM tmp_monthly_hvt t
JOIN   watchlist w ON w.account_id = t.account_id;

-- Step 4: audit extract
INSERT INTO audit_log (txn_id, flagged_at)
SELECT id, NOW() FROM tmp_monthly_hvt
WHERE  amount > 500000;

What happens when a CTE is referenced multiple times?

A CTE referenced multiple times in one statement may be recomputed each time (in databases that inline it). A temp table is stored once and never recomputed.

SQL
-- CTE referenced twice: may be computed twice
WITH monthly_totals AS (
    SELECT account_id, SUM(amount) AS total
    FROM   transactions
    GROUP BY account_id
)
SELECT * FROM monthly_totals WHERE total > 1000000
UNION ALL
SELECT * FROM monthly_totals WHERE total < 10000;
-- The optimizer may run the GROUP BY twice

-- Temp table: computed once, reused
CREATE TEMPORARY TABLE tmp_monthly_totals AS
SELECT account_id, SUM(amount) AS total
FROM   transactions
GROUP BY account_id;

SELECT * FROM tmp_monthly_totals WHERE total > 1000000;
SELECT * FROM tmp_monthly_totals WHERE total < 10000;
-- GROUP BY ran once

What is ON COMMIT DROP?

By default, temp tables live for the session. With ON COMMIT DROP, the table is dropped at the end of the current transaction, which is useful in stored procedures where you want automatic cleanup.

SQL
BEGIN;
CREATE TEMPORARY TABLE tmp_calc (
    account_id VARCHAR(10),
    interest   DECIMAL(15,2)
) ON COMMIT DROP;

INSERT INTO tmp_calc
SELECT account_id, balance * 0.04 / 12 FROM accounts;

SELECT * FROM tmp_calc;  -- works within the transaction
COMMIT;
-- tmp_calc is automatically dropped

How do you use temp tables in Java with JDBC?

Rajesh creates a temp table in a Spring service for the month-end ETL pipeline.

JAVA
package com.bank.service;

import java.sql.Connection;
import java.sql.Statement;

public class MonthEndEtlService {

    public void runPipeline(Connection conn) throws Exception {
        try (Statement st = conn.createStatement()) {
            // Stage high-value transactions
            st.execute(
                "CREATE TEMPORARY TABLE tmp_hvt AS " +
                "SELECT * FROM transactions " +
                "WHERE txn_date >= DATE_TRUNC('month', CURRENT_DATE) " +
                "AND amount > 100000"
            );

            // Index for subsequent queries
            st.execute("CREATE INDEX idx_tmp ON tmp_hvt (account_id)");

            // Multiple queries reuse the staged data
            st.executeQuery("SELECT account_id, SUM(amount) FROM tmp_hvt GROUP BY account_id");
            st.executeQuery("SELECT * FROM tmp_hvt WHERE amount > 500000");
        }
        // Temp table dropped when session/connection closes
    }
}

What should a senior engineer know?

  • Temp tables incur write I/O and catalog overhead to create and drop. Heavy temp-table churn can bloat the temp space (especially in PostgreSQL where temp tables create catalog entries).
  • In PostgreSQL, UNLOGGED tables are faster than temp tables for large datasets because they skip WAL logging (but are not crash-safe).
  • MySQL automatically creates internal temp tables for some queries (visible in EXPLAIN as "Using temporary").
  • Do not reach for a temp table when a CTE or plain subquery would do – the overhead is not worth it for simple cases.

What are common interview follow-up questions?

  • Can other sessions see my temp table? (No, it is session-scoped and invisible to others.)
  • Can you create a foreign key on a temp table? (Generally no; temp tables cannot reference permanent tables with FK constraints.)
  • What is the difference between a temp table and a materialized view? (A temp table is session-scoped and manually populated; a materialized view is persistent and refreshable.)

Visual Overview

DIAGRAM
  CTE (single statement scope)

  WITH name AS (subquery) ──→ main query ──→ result
  │                              │
  └── disappears after statement─┘

  Temporary Table (session scope)

  CREATE TEMP TABLE ──→ INSERT/INDEX ──→ Statement 1
                                     ──→ Statement 2
                                     ──→ Statement 3
                                     ──→ ...
  │                                         │
  └── dropped at session end ───────────────┘

Key Takeaway

A temporary table is a genuine table that lives only for the duration of the database session, can be indexed and queried repeatedly, and is automatically dropped when the session ends. A CTE is a named subquery scoped to a single statement that the optimizer usually inlines. The crucial difference is lifetime and scope: a CTE disappears when its statement finishes, while a temp table persists across every statement in the session.

Use a CTE for readability and single-statement logic, especially when the intermediate set is small or used once. Use a temp table when the same expensive intermediate result is needed by several separate statements, when you want to index the intermediate result, or when materializing once beats recomputing.

At the National Bank of India, Pankaj's month-end reconciliation pipeline needed this month's high-value transactions for a per-account summary, a flagged-for-review list, and an audit extract. Staging that expensive filter into a temporary table once and indexing it meant every step read the staged data instead of re-scanning the two-hundred-million-row source. For a simple one-off count in another report, Rajesh used a CTE because it was lighter and the set was used once.

Remember one sentence: a CTE is a named subquery scoped to one statement, while a temp table is a real session-lived table you reuse across many statements.

---

Share
Use ← → arrow keys to navigate