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.