Let's explore howtemporary tables work in MariaDB. First, we have to connect to the server. For example (use your own connection details):

mariadb-h 127.0.0.1-u root-p"RootPassword!"--database demo

Now, just to point something out, let's create a standard (permanent) table. Here's how:

CREATETABLEt(cINT);

This table,t, will persist in the database even after we exit the client:

exit

When we reconnect and check the existing tables usingSHOW TABLES;, the tablet will still be listed:

mariadb-h 127.0.0.1-u root-p"RootPassword!"--database demo

SHOWTABLES;

+----------------+| Tables_in_demo |+----------------+| t              |+----------------+

All this is pretty obvious, but now, let's recreate this table and try something different:

CREATEORREPLACETEMPORARYTABLEt(cINT);

Notice theTEMPORARY keyword. After creating this table, if we runSHOW TABLES;, it appears in the list. We can insert data into it, query it, join it with other tables. It behaves like a normal table during the current session. However, if we exit the client, then reconnect, and perform aSHOW TABLES; again, the temporary tablet will not be listed. A temporary table only exists for the duration of the session in which it was created and other sessions won't be able to see it.

Use Case for Temporary Tables

Temporary tables are quite useful for transient data operations. For instance, consider a table calledproducts in our database:

CREATETABLEproducts(idINTNOTNULLAUTO_INCREMENT,codeVARCHAR(100)NOTNULL,nameVARCHAR(250)NOTNULL,descriptionTEXTDEFAULTNULL,PRIMARYKEY(id),UNIQUEKEYcode(code))

We can create a temporary table that mimics the structure ofproducts:

CREATETEMPORARYTABLEtLIKEproducts;

We can confirm this by running:

DESCRIBEt;

+-------------+--------------+------+-----+---------+----------------+| Field       | Type         | Null | Key | Default | Extra          |+-------------+--------------+------+-----+---------+----------------+| id          | int(11)      | NO   | PRI | NULL    | auto_increment || code        | varchar(100) | NO   | UNI | NULL    |                || name        | varchar(250) | NO   |     | NULL    |                || description | text         | YES  |     | NULL    |                |+-------------+--------------+------+-----+---------+----------------+

Initially,t will be empty. However, suppose we want to transfer some data fromproducts tot. Let’s assume we only want to include products that contain the number 0 in their code:

INSERTINTOtSELECT*FROMproductsWHEREcodeLIKE'%0%';

After running this command, if we query the temporary tablet:

SELECT*FROMt;

+----+--------+------------------+---------------------------------------------------+| id | code   | name             | description                                       |+----+--------+------------------+---------------------------------------------------+|  1 | BG2024 | BugBlaster       | Eradicates software bugs with a single scan.      ||  3 | FW001  | FireWhale        | An oversized, comprehensive firewall solution.    ||  4 | CLD404 | CloudNine Finder | Find your way back from cloud outages and errors. |+----+--------+------------------+---------------------------------------------------+

We see the filtered data.

Conclusion

Temporary tables offer a powerful way to handle data for temporary processing without affecting the persistent data store. They are particularly useful in scenarios where data needs to be manipulated or transformed temporarily. You can use permanent tables for this kind of data manipulation but temporary tables are useful when you need automatic cleanup, reduced risk of naming conflicts, isolation and security, and resource management for query performance.