Creating Temporary Tables in a SELECT Statement Without a Separate CREATE TABLE

1. Introduction

In SQL, a common need arises to create temporary tables to store intermediate results for complex queries. Traditionally, this involves a two-step process: first, creating the table, and then inserting data into it.

However, SQL provides a more streamlined approach where we can create a temporary table directly within a SELECT statement. So, there is no need for a separate CREATE TABLE.

In this tutorial, we’ll delve into this technique using the Baeldung schema as an example and explore practical use cases for effective understanding.

2. Understanding Temporary Tables in a SELECT Statement

SQL uses temporary tables to store data briefly while a query executes. So, the system automatically drops these tables when the session that created them ends.

Essentially, creating temporary tables directly within a SELECT statement simplifies the process, making the code more concise and easier to read.

3. Syntax

Several SQL databases support creating a temporary table directly within a SELECT statement. So, databases such as PostgreSQL, MySQL, and SQL Server have different syntax and behavior for this operation.

Let’s review these database syntaxes.

3.1. Using AS

PostgreSQL, MySQL, and SQL Server use similar methods for creating temporary tables. In all cases, we can create temporary tables from existing tables using the SELECT statement in combination with the AS statement:

SELECT *
FROM (
    SELECT column1, column2
    FROM Existing_table
    WHERE condition
) AS Temp_table;

As we see above, the AS statement is used to create Temp_table; the table is dropped after the query session ends.

In a nutshell, we simply selected all columns from a subquery that selected column1 and column2 from an existing_table table based on a set condition.

Later in this tutorial, we’ll explore this with practical examples using our Baeldung schema.

3.2. Using Alternative Syntax in SQL Server

Here, let’s explore the general syntax to create a temporary table within a SELECT statement in SQL Server:

SELECT column1, column2, ...
INTO #Temporary_table
FROM Source_table
WHERE condition;

In SQL Server, the INTO statement combined with the # prefix designates Temporary_table as a temporary table and populates it with the selected data.

Also, we’ll explore this with practical examples using our Baeldung schema later in this tutorial.

4. Practical Use Case

Let’s explore the course and teaching tables from the Baeldung schema. In this case, we’ll explore both simple and complex use cases.

4.1. Extract Data Into a Temporary Table

For a simple use case, let’s extract courses that are inactive into a temporary table from the Course table on the Baeldung schema.

This query works for PostgreSQL, MySQL, and SQL Server databases:

SELECT * 
FROM (
    SELECT id, name, textbook, credits
    FROM Course
    WHERE is_active = 'No'
) AS Temp_table;

Here is the result of that query:

As we can see, the output is saved as Temp_table.

Furthermore, there is another method that is unique to SQL Server alone, as we noted earlier:

SELECT id, name, textbook, credits
INTO #Inactive_courses
FROM Course
WHERE is_active = 'No';
SELECT * FROM #Inactive_courses;

As seen above, we selected all the columns from the #inactive_courses temporary table. Here are the results from the query:

In this method, the selected columns from the Course table based on the specified condition were saved into a temporary table named #inactive_courses. However, the temporary table is dropped after the close of the session or when the database is closed.

4.2. Use a Temporary Table With a JOIN Statement

Here, let’s advance our usage of a temporary table in a SQL query by combining it with a JOIN statement.

So, this is how to do this on PostgreSQL and MySQL:

SELECT *
FROM (
    SELECT id, name, textbook, credits
    FROM Course
    WHERE is_active = 'No'
) AS Temp_table
JOIN Teaching ON Temp_table.id = Teaching.course_id;

Here is the result:

As we see above, we used our temporary table to get all the entries for the inactive course on the Teaching table.

For SQL Server, this is how the query is built:

SELECT id, name, textbook, credits
INTO #Inactive_courses
FROM Course
WHERE is_active = 'No';

SELECT *
FROM #Inactive_courses
JOIN Teaching ON #Inactive_courses.id = Teaching.course_id;

The result from the above query is the same as the previous output.

5. Conclusion

In this article, we explored creating temporary tables directly within a SELECT statement in SQL. We discussed the syntax and behavior for PostgreSQL, MySQL, and SQL Server, including practical use cases for extracting data and joining temporary tables.

So, creating temporary tables within a SELECT statement simplifies complex queries, making code more concise and easier to read, with each database having its own unique syntax and behavior for this operation.