Temporary Tables in MySQL

Introduction

In MySQL, sometimes you need to store data temporarily for a short period while performing operations like calculations or intermediate queries.

For this purpose, MySQL provides Temporary Tables.

Temporary tables exist only for the duration of a session and are automatically deleted when the session ends.

What is a Temporary Table

A Temporary Table is a table that is:

• Created temporarily

• Used within a session

• Automatically deleted when the session ends.

It is useful when you do not want to store data permanently in the database.

Basic Syntax

CREATE TEMPORARY TABLE table_name ( column1 datatype, column2 datatype ); 

Example

CREATE TEMPORARY TABLE TempStudents ( id INT, name VARCHAR(50) ); 

This creates a temporary table named TempStudents.

Insert Data into Temporary Table

INSERT INTO TempStudents VALUES (1, 'Rahul'), (2, 'Aisha'); 

Using Temporary Tables

You can use temporary tables like normal tables:

SELECT * FROM TempStudents; 

Characteristics of Temporary Tables

• Exist only during the session.

• Automatically deleted after the session ends.

• Can have the same name as permanent tables

• Visible only to the current user/session

Example Scenario

Suppose you want to process student data temporarily:

CREATE TEMPORARY TABLE TempData AS SELECT * FROM Students WHERE age > 18; 

This creates a temporary table with filtered data.

Advantages of Temporary Tables

• Helps in complex queries

• Stores intermediate results

• Improves performance in some cases

• Avoids clutter in the main database

Limitations

• Data is not permanent.

• The table disappears after the session ends.

• Not shared between users

Common Mistakes

• Expecting temporary data to persist

• Forgetting that the table is session-based

• Confusing temporary tables with permanent tables

Key Points to Remember

• Temporary tables exist only for a session.

• Created using CREATE TEMPORARY TABLE

• Automatically deleted after use.

• Useful for intermediate data processing