CTE vs. Temporary Table: What’s the Difference?

SQL offers you two options to handle complex queries efficiently: CTEs and temporary tables. Read on to weigh the pros and cons of CTEs vs. temp tables.

In SQL (Structured Query Language), a common table expression (CTE) lets you define a subquery and assign a name to it so that it can be used within a main query. A temporary table is a database table that you create explicitly, like any other table – but with the particularity that it disappears when you close the database connection in which it was created. So yes, CTEs and temporary tables have some things in common. But there’s more to the CTE vs. temp table debate.

To thoroughly master this topic, you can take our Recursive Queries course. You’ll learn how to use CTEs to process complex data structures like trees and graphs. With its 114 exercises, this course will guide you to mastery of the most complex SQL queries. It also provides a final quiz to test your acquired knowledge.

CTEs and temporary tables have purposes in common. Both generate intermediate results for a query without leaving permanent objects in the database; this saves storage space. But there are important differences between them, so it is useful to know when to use one or the other. Let’s start by examining the syntax of the CTE.

CTE Syntax

Below we will see a use case where an SQL CTE is preferable to a recursive query.

An SQL CTE begins with a WITH clause, followed by a name and then the definition of its subquery. After that, there’s an ordinary SQL statement (usually a SELECT) that queries the results of the CTE like a regular table. In its simplest form, a CTE looks like this:

WITH cte_name AS (
  cte_query_definition
)
SELECT * 
FROM cte_name;

For more details on CTE syntax, read What Is a Common Table Expression in SQL.

Temporary Table Syntax

For temporary tables, the syntax varies slightly depending on your relational database management system (RDBMS). The following table shows how to create temporary tables in the most popular RDBMSs:

CREATE TEMPORARY TABLE MyTempTbl (
  Id INT, 
  Name VARCHAR(50)
);

-- Local temp table:
CREATE TABLE #MyTempTbl (
  Id INT, 
  Name NVARCHAR(50)
);

-- Global temp table:
CREATE TABLE ##MyTempTbl (
  Id INT, 
  Name NVARCHAR(50)
);

CREATE TEMPORARY TABLE MyTempTbl (
  Id INT, 
  Name VARCHAR(50)
);

CREATE GLOBAL TEMPORARY TABLE MyTempTbl (
  Id NUMBER, 
  Name VARCHAR2(50)
)
-- data is kept until the end 
-- of the current transaction
ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE MyTempTbl (
  Id NUMBER, 
  Name VARCHAR2(50))
-- data is kept until the end 
-- of the current session
ON COMMIT PRESERVE ROWS;

To learn more about creating all kinds of tables in SQL,  take our The Basics of Creating Tables in SQL course. Its 80 exercises will help you master the CREATE TABLE command and all its options. You’ll start by creating a table in its most basic form and then progress to defining primary and foreign keys, NULL/NOT NULL columns, and modifying the structure of existing tables.

Now that we’ve seen the syntax for both CTEs and temp tables, let’s examine how each one works

Solving the Same Problem with CTEs and Temp Tables

Let’s try an example problem that can be solved using a CTE and a temporary table.

You have two tables: a detailed Sales table and a Countries table with country-specific information. You want to list sales totals by country along with detailed country information. You can achieve this by using either a common table expression or a temporary table. We'll explore both methods to see how they can be effectively utilized for this task.

The CTE-based solution uses a CTE called CountryTotals with a subquery that calculates totals grouped by CountryCode. It also has a main query that combines those totals with the data for each country:

WITH CountryTotals (CountryCode, TotalSales) AS (
  SELECT 
    CountryCode, 
    SUM(Sales) AS TotalSales
  FROM Sales
  GROUP BY CountryCode
)
SELECT 
  c.*, 
  ct.TotalSales
FROM Countries c
INNER JOIN CountryTotals ct 
ON ct.CountryCode = C.CountryCode;

You can find many other examples of common table expressions in these CTEs explained with examples.

To obtain the same result using a temporary table, you must first create the temporary table with the columns CountryCode and TotalSales. The following example code uses MySQL syntax to do that:

CREATE TEMPORARY TABLE CountryTotals (
  CountryCode char(3), 
  TotalSales decimal(15, 2)
);

The columns that define the structure of the temporary table are the same as those included in the CTE definition from above. To create the temporary table, the data type of each column must match the one returned by the corresponding column of the query you will use to fill it with data. This query is shown below:

Note that the SELECT placed after the INSERT is similar to the one that defines the subquery in the original CTE.

Now that you have the sales totals by country in the temporary table, you can join it with the Countries table to obtain the final result:

SELECT c.*, ct.TotalSales
FROM Countries c
INNER JOIN CountryTotals ct 
ON ct.CountryCode = C.CountryCode;

This query is also identical to the main CTE query (the one outside the WITH clause), as the temporary table has the same name as the CTE.

It is important to note that it is not always possible to replace temporary tables with CTEs. It can be done when the rows of the temporary table come from a single SELECT. But if the data is modified by UPDATE, DELETE or other INSERT commands after it's been loaded into the temp table, you won’t be able to replace the temporary table with a CTE.

When to Use a CTE vs. a Temporary Table

Even in those situations in which a CTE can be replaced by a temporary table (or vice versa), it’s not always a good practice to do so. The use cases of CTEs and those of temporary tables are different; there are situations where a CTE is preferable and situations where a temporary table is preferable.

Let’s examine the use cases of CTEs and temp tables, starting with CTEs.

CTE Use Cases

CTEs do not generate persistent objects in the database. In other words, the CTE lives only as long as the query that contains it is executed. Once the execution of the query is finished, the CTE disappears without a trace. In addition, the CTE and the query that consumes it are contained in the same SQL statement. These qualities make the CTE preferable for improving the readability and organization of SQL scripts – i.e. for making queries easier for humans to understand.

Small Data Sets

When the result of a subquery yields a relatively small number of rows (i.e. 10 or 20 thousand rows) a CTE is the ideal way to implement it. Since it does not use persistent storage, it does not generate disk I/O operations. Therefore, queries are resolved much faster.

Inside Views

You cannot create, fill with data, or query a temp table inside a view. But you can include a CTE inside a view and use it anywhere as if it were a read-only table.

Refactoring

When refactoring database queries, CTEs are great allies. In long and complex queries, CTEs allow you to define intermediate results (i.e. named subqueries) that can be reused repeatedly within the same query.

Also, the complexity of a long query can be gradually reduced with nested CTEs until it is simplified to a SELECT of only a few lines.

Improving Database Performance

In some RDBMSs (e.g. MS SQL Server), CTEs allow us to create more efficient execution plans than temporary tables do. This can substantially improve database performance by reducing query execution times.

The performance benefit obtained depends on the RDBMS optimizer, which means that using CTEs does not always result in better performance. But it is a factor to consider when choosing between a CTE and a temporary table for subquery handling.

Recursion

One area where the use of CTEs is mandatory is in recursive queries. In SQL, a recursive CTE is capable of invoking itself to implement recursive processes, such as traversing hierarchical data structures. In other words, recursion requires CTEs.

Allowing recursion in SQL avoids the need to use other programming languages; this promotes greater efficiency in queries that must invoke themselves. You can read more in our article What Is a Recursive CTE in SQL?

Now that you know in which cases it is convenient to use a CTE, you can boost your knowledge by reviewing the most common SQL CTE interview questions and training yourself with these CTE exercises.

Temporary Table Use Cases

CTE’s main advantage – not storing data persistently – can be a disadvantage. In some use cases, it is preferable to have data stored in tables (even temporary ones). Let’s look at the most typical situations.

Reusing Data Sets

Reusing CTE code is limited to a single query. Data stored in a temporary table, on the other hand, can be used repeatedly in different queries. The key requirement is that these queries are executed within the same database connection.

ETL Processes

When you implement ETL (Extract, Transform, Load) processes using SQL scripts, you often need to generate intermediate results and apply successive transformations to a single set of data. Only when that is done can you finally dump it into permanent tables. In these situations, keeping temporary tables available throughout the transformation process is very helpful.

Large Datasets

When working on large-volume, temporary datasets – say, millions of records – temporary tables are always preferred over CTEs. If you try to run a CTE that includes a large-volume subquery, it is very likely that the database engine would try to store the temporary results in the server’s memory. This would exhaust the available capacity or would require some operating system resources to dump the memory to disk. In short, it would cause poor database performance. So if you know in advance that the results of a subquery will be very large, it is best to use temp tables instead of CTEs.

Generating Indexes

Like any other table in a database, temporary tables support the creation of indexes to speed up query execution. When intermediate results require a large number of rows (and must be used repeatedly), store those results in a temporary table with one or more indexes. The creation of the indexes may take some time, but this time is more than recouped if several queries must then be run on the same data set.

Generally, the best candidates for indexing are columns used in JOINs, WHERE, or GROUP BY.

Wrapping Up the CTE vs. Temp Table Debate

Choosing between CTEs and temporary tables is not always easy. We have discussed the syntax and typical use cases for both of these solutions.

Remember to explore our Recursive Queries course, which will teach you how to use common table expressions. The course is aimed at beginning database analysts, students, and developers looking to deepen their knowledge of SQL. To take the course, you only need a computer with an Internet connection and a web browser.

Now it’s time to deploy your wisdom! If you know when to use CTEs vs. temp tables, you’ll always achieve maximum efficiency when working with temporary data.