MySQL Temporary Table

Introduction

In this chapter, we will learn about temporary tables in MySQL. Temporary tables are used to store temporary data that is needed for a short duration during a session. These tables are created on the fly and automatically dropped when the session ends or the connection is closed. Temporary tables are useful for storing intermediate results and simplifying complex queries.

Creating a Temporary Table

To create a temporary table, we use the CREATE TEMPORARY TABLE statement. This command defines the table's structure, including its columns, data types, and constraints.

Syntax

CREATE TEMPORARY TABLE temp_table_name (
    column1 datatype constraints,
    column2 datatype constraints,
    ...
    columnN datatype constraints
);

• temp_table_name: The name of the temporary table you want to create.
• column1, column2, ..., columnN: The names of the columns in the table.
• datatype: The data type for the column (e.g., INT, VARCHAR(50), DATE).
• constraints: Optional constraints for the column (e.g., PRIMARY KEY, NOT NULL, AUTO_INCREMENT).

Example

CREATE TEMPORARY TABLE temp_employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

This example creates a temporary table named temp_employees with five columns: id, first_name, last_name, email, and hire_date.

Using a Temporary Table

Temporary tables can be used just like regular tables. You can insert data into them, query them, and perform various operations.

Inserting Data into a Temporary Table

INSERT INTO temp_employees (first_name, last_name, email, hire_date) VALUES ('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01');
INSERT INTO temp_employees (first_name, last_name, email, hire_date) VALUES ('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02');

Querying Data from a Temporary Table

SELECT * FROM temp_employees;

Output

id	first_name	last_name	email	hire_date
1	Rahul	Sharma	rahul.sharma@example.com	2023-07-01
2	Priya	Singh	priya.singh@example.com	2023-07-02

Dropping a Temporary Table

Temporary tables are automatically dropped when the session ends or the connection is closed. However, you can also drop them explicitly using the DROP TABLE statement.

Syntax

DROP TEMPORARY TABLE temp_table_name;

Example

DROP TEMPORARY TABLE temp_employees;

This example explicitly drops the temporary table named temp_employees.

Full Example

Let's go through a full example where we create a temporary table, insert data, query the data, and drop the table.

1. Create a Temporary Table:

CREATE TEMPORARY TABLE temp_employees (
    id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE,
    hire_date DATE
);

2. Insert Data into the Temporary Table:

INSERT INTO temp_employees (first_name, last_name, email, hire_date) VALUES ('Rahul', 'Sharma', 'rahul.sharma@example.com', '2023-07-01');
INSERT INTO temp_employees (first_name, last_name, email, hire_date) VALUES ('Priya', 'Singh', 'priya.singh@example.com', '2023-07-02');

3. Query Data from the Temporary Table:

SELECT * FROM temp_employees;

Output

id	first_name	last_name	email	hire_date
1	Rahul	Sharma	rahul.sharma@example.com	2023-07-01
2	Priya	Singh	priya.singh@example.com	2023-07-02

4. Drop the Temporary Table:

DROP TEMPORARY TABLE temp_employees;

Important Considerations

• Scope: Temporary tables are visible only within the session in which they are created. They are not visible to other sessions.
• Automatic Deletion: Temporary tables are automatically dropped when the session ends or the connection is closed.
• Naming Conflicts: If a temporary table has the same name as a permanent table, the temporary table will be used in the session.

Conclusion

Temporary tables in MySQL are useful for storing intermediate results and simplifying complex queries. This chapter covered how to create, use, and drop temporary tables, providing a full example to illustrate the process. In the next chapter, we will learn how to insert data into tables.