# Temporary Table

A temporary table, as the name suggests, is a special type of database table that exists temporarily and allows you to store result set, which you can reuse within the current session.

A temporary table is very handy when it is impossible or expensive to query data within a single sql statement. In such cases, you can use a temporary table to store the immediate results.

# Use cases

Debugging stored procedure.
As a staging table for generating reports that involve several tables. Imagine generating a report that involves 9-10 tables with millions of records. It is advisable to join the tables having least rows and store the output in temporary table before joining it to the other bigger tables.
Lookup table for temporary data. Suppose that your application has the feature to upload and map csv data before saving it. Temporary table has your back.
Replacing subqueries with joins. MySQL doesn’t do some types of constant subqueries well, so running the constant subquery into a temporary table and using a join instead can produce big performance gains.
Complex calculations. A procurement system having multiple stock movement strategies can use a temporary table to calculate the stock dispensing algorithm. The possiblities are limitless.

# Expectations

While there are a plethora of relational databases, the scope of this article is limited to MySQL & PostgresSQL. Before diving in, let's take a step back and explore the characteristics of temporary tables.

Temporary table is usually created by using CREATE TEMPORARY TABLE statement.
Temporary table is automatically removed when the session ends, the connection is terminated or when the DROP TABLE statement is used.
Temporary table is only available and accessible to the client that creates it. Different clients can create temporary tables with similar name without naming collision. However, two temporary tables cannot share the same name in the same session.
Temporary table can have the same name as a normal table. When such a scenario happens, the permanent table becomes inaccessible until the temporary is destroyed.

Even though a temporary table can have the same name as a normal table, it is not recommended!

# Syntax

Let's explore how a temporary table is created and destroyed. Similar to creating a normal table, temporary table has the addition of TEMPORARY keyword when creating it.


// Creating
CREATE TEMPORARY TABLE table_name(column_name);


CREATE TEMPORARY TABLE payroll_details_temp(
    employee_id INT
    ...
)


// destroying. Notice, we dont have TEMPORARY here
DROP TABLE payroll_details_temp;

# Conclusion

When accessing and manipulating large data across multiple tables becomes a pain in the ass due to performance bottlenecks, temporary tables might help you out. I hope this article answered your questions relating to temporary tables and empowered you. Feel free to share your thoughts on Twitter with me.

Adios 👋