Akshit Batra

Posted on Jun 30

CTE vs Temporary Tables in SQL: Which One Should You Use?

#cte #temptable #sql

One of the most common questions I get from developers working with SQL is:

Should I use a CTE or create a temporary table?

Both solve similar problems—they let you work with intermediate datasets—but they behave very differently under the hood.

Choosing the right one can make your queries cleaner, easier to maintain, and in some cases, significantly faster.

Let's break it down.

What is a CTE?

A Common Table Expression (CTE) is essentially a named result set that exists only for the duration of a single SQL statement.

Think of it as giving a temporary name to a subquery.

WITH HighSalaryEmployees AS (
    SELECT employee_id,
           name,
           salary
    FROM employees
    WHERE salary > 100000
)
SELECT *
FROM HighSalaryEmployees
WHERE salary < 150000;

Once this query finishes executing, the CTE disappears.

When should you use a CTE?

A CTE shines when you want to make complex SQL readable.

Instead of deeply nested subqueries, you can split your logic into logical building blocks.

For example:

Breaking complex queries into smaller steps
Improving readability
Recursive queries (organization hierarchy, category trees)
One-time transformations before the final result

Example:

WITH MonthlySales AS (
    SELECT
        store_id,
        SUM(amount) AS total_sales
    FROM sales
    GROUP BY store_id
),
TopStores AS (
    SELECT *
    FROM MonthlySales
    WHERE total_sales > 50000
)

SELECT *
FROM TopStores;

This is much easier to understand than nesting multiple SELECT statements.

What is a Temporary Table?

A temporary table is an actual table created inside the database's temporary storage.

Unlike a CTE, it survives beyond a single statement and can be reused throughout your session or stored procedure.

CREATE TEMP TABLE high_salary_employees AS
SELECT *
FROM employees
WHERE salary > 100000;

SELECT COUNT(*)
FROM high_salary_employees;

SELECT AVG(salary)
FROM high_salary_employees;

DROP TABLE high_salary_employees;

Notice how the same dataset is reused multiple times.

When should you use Temporary Tables?

Temporary tables are better when performance becomes more important than query elegance.

Typical scenarios include:

Large datasets (millions of rows)
Reusing intermediate results multiple times
Performing several joins on the same data
Creating indexes for faster lookups
Complex stored procedures

For example:

CREATE TEMP TABLE yearly_sales AS
SELECT
    customer_id,
    SUM(amount) AS total_sales
FROM sales
GROUP BY customer_id;

CREATE INDEX idx_customer
ON yearly_sales(customer_id);

SELECT *
FROM yearly_sales
WHERE customer_id = 1001;

Because the table can be indexed, subsequent queries become much faster.

Performance Considerations

A common misconception is:

"CTEs are always stored in memory."

Not exactly.

A CTE is primarily a query expression. Whether the database materializes it or not depends on the SQL engine and optimizer.

Different databases optimize CTEs differently:

PostgreSQL may inline or materialize them depending on the version.
SQL Server often treats them similarly to inline views.
MySQL optimizes them differently again.

The key takeaway is:

Don't assume a CTE automatically improves performance.

Its primary goal is readability.

Temporary tables, however, physically store data (in the database's temporary storage) and allow indexes, statistics, and multiple reads.

Side-by-Side Comparison

Feature	CTE	Temporary Table
Lifetime	Single statement	Entire session or stored procedure
Reusable	❌ No	✅ Yes
Readability	⭐ Excellent	Good
Supports recursion	✅ Yes	❌ No
Can create indexes	❌ No	✅ Yes
Best for	Query organization	Large intermediate datasets
Performance tuning	Limited	Excellent

Real-world Example

Suppose you're calculating yearly sales for 20 million orders.

Option 1: CTE

WITH Sales AS (
    SELECT
        customer_id,
        SUM(amount) total
    FROM orders
    GROUP BY customer_id
)

SELECT *
FROM Sales
WHERE total > 10000;

Works perfectly if this is your only query.

Option 2: Temporary Table

CREATE TEMP TABLE Sales AS
SELECT
    customer_id,
    SUM(amount) total
FROM orders
GROUP BY customer_id;

CREATE INDEX idx_sales_customer
ON Sales(customer_id);

SELECT *
FROM Sales
WHERE total > 10000;

SELECT AVG(total)
FROM Sales;

SELECT COUNT(*)
FROM Sales;

Notice that the expensive aggregation happens only once, and every subsequent query reuses the same dataset.

Quick Rule of Thumb

Choose a CTE when:

You need clean, readable SQL.
The intermediate result is used only once.
You're writing recursive queries.
The dataset is relatively small.

Choose a Temporary Table when:

You're processing millions of rows.
The same data is referenced multiple times.
You need indexes.
You're building long-running stored procedures or ETL pipelines.

Final Thoughts

Both CTEs and temporary tables are valuable tools—they're just designed for different purposes.

If your goal is clarity, reach for a CTE.

If your goal is performance and data reuse, especially with large datasets, a temporary table is usually the better choice.

The best SQL developers know when to use each rather than trying to use one solution everywhere.

What do you prefer in your projects?

CTEs for cleaner SQL?
Temporary tables for performance?
Or do you have another approach?

I'd love to hear your thoughts in the comments.

DEV Community