TEMPORARY TABLE Temporary Table in SQL

This guide covers temporary table temporary table in SQL with practical context and easy-to-follow details. Use it to understand the subject and apply the information confidently.

TEMPORARY TABLE, also known as a temporary table, Is a special form of table that is temporarily stored and handles intermediate results on SQL using the same options as selecting, updating, and matching as you can use. With typical tables in SQL Server.

However, you should note that Temporary Table can be deleted when the current Client Session ends.

Temporary tables are available from MySQL version 3. 23 onwards. If you use an older version, you cannot use this feature, you can use the Heap Table instead.

As mentioned, the temporary table will only exist when the session is available. If you run the code in PHP script, the temporary table will be canceled automatically when the script finishes executing. If you are connected to MySQL Database through the MySQL Client program, the temporary table will exist until you close the Client or cancel the table.

The Example Illustrates How to Use a Temporary Table

mysql> CREATE TEMPORARY TABLE DOANHTHU (
 -> ten_sanpham VARCHAR(50) NOT NULL 
 -> , tong_doanhthu DECIMAL(12,2) NOT NULL DEFAULT 0.00
 -> , gia_trungbinh DECIMAL(7,2) NOT NULL DEFAULT 0.00 
 -> , tong_soluong INT UNSIGNED NOT NULL DEFAULT 0 );
Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO DOANHTHU -> (ten_sanpham, tong_doanhthu, gia_trungbinh, tong_soluong) -> VALUES -> ('galaxys10', 100.25, 90, 2); mysql> SELECT * FROM DOANHTHU; +--------------+---------------+----------------+------------------+ | ten_sanpham | tong_doanhthu | gia_trungbinh | tong_soluong | +--------------+---------------+----------------+------------------+ | galaxys10 | 100.25| 90.00 | 2 | +--------------+---------------+----------------+------------------+ 1 row in set (0.00 sec) 

When you execute the SHOW TABLES command, your temp table will not be listed. Now, if you log out of the MySQL session and then execute a SELECT command, you will not find the data in the database and then your temporary table will no longer exist.

How to Delete Temporary Tables in SQL

By default, all temporary tables in MySQL will be deleted when your database connection ends. However, if you still want to delete them without disconnecting, you can use the DROP TABLE command.

The following is an example of deleting a temporary table.

mysql> CREATE TEMPORARY TABLE DOANHTHU (
 -> ten_sanpham VARCHAR(50) NOT NULL 
 -> , tong_doanhthu DECIMAL(12,2) NOT NULL DEFAULT 0.00
 -> , gia_trungbinh DECIMAL(7,2) NOT NULL DEFAULT 0.00 
 -> , tong_soluong INT UNSIGNED NOT NULL DEFAULT 0 );
Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO DOANHTHU -> (ten_sanpham, tong_doanhthu, gia_trungbinh, tong_soluong) -> VALUES -> ('galaxys10', 100.25, 90, 2); mysql> SELECT * FROM DOANHTHU; +--------------+---------------+----------------+------------------+ | ten_sanpham | tong_doanhthu | gia_trungbinh | tong_soluong | +--------------+---------------+----------------+------------------+ | galaxys10 | 100.25| 90.00 | 2 | +--------------+---------------+----------------+------------------+ 1 row in set (0.00 sec) mysql> DROP TABLE DOANHTHU; mysql> SELECT * FROM DOANHTHU; ERROR 1146: Table 'QTM.DOANHTHU' doesn't exist 

In the next section, we will learn about the Clone Table in SQL , so keep in mind.

Previous article: Function handling DATE / TIME in SQL - Part 2

Next article: CLONE TABLE in SQL