intro
Let's introduce everything you need to know about the CREATE TEMPORARY TABLE SQL statement, including which databases support it and how to use it effectively.
Temporary tables are a useful mechanism supported by most databases to temporarily store data for reuse in scripts or to simplify complex queries. Keep in mind that each database has its own way of handling temporary tables. Now, how does the CREATE TEMPORARY TABLE SQL statement work, and which databases support it? Read this guide to find out!
Temporary Tables in SQL: What Are They and Why Are They Useful?
While this article focuses specifically on the CREATE TEMPORARY TABLE statement, it is useful to take a moment to explain what temporary tables are, when they are used, and why they are important.
Definition
In SQL, a temporary table is a short-lived table that exists only for the duration of a session or transaction—depending on the database and specified configurations. These tables are session-specific, meaning other users cannot access them. Once the session ends, the temporary table is automatically deleted.
Benefits and Usage
Temporary tables introduce several benefits, such as:
In particular, some common use cases for temporary tables are:
CREATE TEMPORARY TABLE SQL Query: Syntax and Support
Disclaimer: Not all databases support the CREATE TEMPORARY TABLE SQL statement. You can see a breakdown of the most popular databases that support it below:
| Database | Supports CREATE TEMPORARY TABLE? |
|---|---|
| MySQL | ✅ |
| PostgreSQL | ✅ |
| SQL Server | ❌ (Uses CREATE TABLE #table_name instead) |
| Oracle | ❌ (Uses CREATE GLOBAL TEMPORARY TABLE instead) |
| MariaDB | ✅ |
| SQLite | ✅ |
As you can see, SQL Server and Oracle are the major databases that do not support CREATE TEMPORARY TABLE. These databases use alternative methods for creating temporary tables. You can learn more in our general guide on creating temporary tables in SQL.
Time to focus in more detail on how MySQL and PostgreSQL support the CREATE TEMPORARY TABLE SQL command!
MySQL
This is the syntax for creating a temporary table in MySQL:
1
CREATE TEMPORARY TABLE table_name (
2
column_1 datatype_1,
3
column_2 datatype_2,
4
-- ...
5
);
In detail, this query works just like a regular CREATE TABLE statement. The key difference is the TEMPORARY keyword, which keyword tells MySQL to create the table as a temporary one.
Notes:
PostgreSQL
Here is how you can use the CREATE TEMPORARY TABLE SQL statement to create a temporary table in PostgreSQL:
1
CREATE TEMPORARY TABLE table_name (
2
column_1 datatype_1,
3
column_2 datatype_2,
4
-- ...
5
);
This syntax works just like a regular CREATE TABLE statement. The main difference is the TEMPORARY keyword, which indicates the creation of a temporary table. In PostgreSQL, the TEMPORARY keyword can also be replaced by TEMP.
For more information, read our guide on temporary tables in Postgres.
Notes:
CREATE TEMPORARY TABLE Example
Note: The example below will be in MySQL, but the queries also work in PostgreSQL.
Suppose you have an ecommerce database and you want to run the following query:
1
SELECT
2
c.customer_id,
3
c.first_name,
4
c.last_name,
5
SUM(oi.quantity * oi.price) AS total_spent
6
FROM
7
customers c
8
JOIN
9
orders o ON c.customer_id = o.customer_id
10
JOIN
11
order_items oi ON o.order_id = oi.order_id
12
JOIN
13
products p ON oi.product_id = p.product_id
14
WHERE
15
p.category_id = 5
16
AND o.order_date BETWEEN '2025-01-01' AND '2025-12-31'
17
GROUP BY
18
c.customer_id, c.first_name, c.last_name;
The above query calculates the total amount spent by each customer on products in the category with category ID 5 during the year 2025. To do that, it joins four tables—customers, orders, order_items, and products—to combine the relevant data, then filters the results to include only orders made within the specified date range. Finally, it uses GROUP BY to group the results by the customer and sum up their total expenditure.
The result of the query in a multi-database client like DbVisualizer would look something like this:
To simplify the complex query above, you can introduce a temporary table using the CREATE TEMPORARY TABLE SQL statement below:
1
CREATE TEMPORARY TABLE temp_customer_spending AS
2
SELECT
3
c.customer_id,
4
c.first_name,
5
c.last_name,
6
o.order_id,
7
oi.product_id,
8
oi.quantity,
9
oi.price,
10
o.order_date,
11
p.category_id
12
FROM
13
customers c
14
JOIN
15
orders o ON c.customer_id = o.customer_id
16
JOIN
17
order_items oi ON o.order_id = oi.order_id
18
JOIN
19
products p ON oi.product_id = p.product_id;
The temporary table temp_customer_spending consolidates the necessary sales data from the four different tables. With this temporary table in place, you do not need to repeatedly join these tables in the main query.
Execute the CREATE TEMPORARY TABLE query to create the table in the database session:
You can now run a much simpler query to get the same result as before:
1
SELECT
2
customer_id,
3
first_name,
4
last_name,
5
SUM(quantity * price) AS total_spent
6
FROM
7
temp_customer_spending
8
WHERE
9
category_id = 5 AND order_date BETWEEN '2025-01-01' AND '2025-12-31'
10
GROUP BY
11
customer_id, first_name, last_name;
As you can verify in DbVisualizer or any other visual database client, you can now get the same result as before but with a much easier-to-understand query:
Note that the result is the same as before but with a much simpler query
Wonderful! You just saw how powerful the CREATE TEMPORARY TABLE statement is.
Conclusion
In this blog post, you learned more about the CREATE TEMPORARY TABLE SQL statement. You understood what temporary tables are, why they are useful, and how to use them in MySQL and PostgreSQL. Through an example, you also saw how much easier it is to handle complex queries using a fully-featured database client like DbVisualizer. Download it for free today!
FAQ
Is CREATE TEMPORARY TABLE part of the SQL ANSI/ISO standard?
The CREATE TEMPORARY TABLE SQL statement is part of the SQL ANSI/ISO standard, though it was added in later revisions. While it is standardized, different databases have implemented their own variations for creating temporary tables. As a result, the behavior and syntax can differ a lot across systems.
What is the difference between a regular table and a temporary table?
A regular table exists persistently within a database, storing data for the long term, and it must be explicitly dropped if no longer needed. In contrast, a temporary table exists only for the duration of a session or transaction. Once the session or transaction ends, the temporary table is automatically dropped.
Which databases support the CREATE TEMPORARY TABLE SQL statement?
Several popular databases support the CREATE TEMPORARY TABLE statement, including:
Why use a visual database client?
Using a visual database client like DbVisualizer to create a temporary table offers several advantages, making data management much easier compared to using command-line tools. With DbVisualizer, you can visually see your queries, monitor execution, and access powerful features like drag-and-drop query creation and query optimization. Grab a 21-day free trial for the Pro version!
