To create a temporary table, you can use the same syntax that we used for creating a permanent table, except we will add the TEMPORARY keyword. You can either create a temporary table with a definition or create one from an existing permanent table. Let's have a look at the syntax that's used to create temporary tables:

• Explicit creation of a temporary table:

Use the following syntax to create a temporary table schema explicitly: 

CREATE TEMPORARY TABLE temptablename(
 col1 col1type,
 col2 col2type);

You will define each column name, data type, and any constraints on it, such as NOT NULL, the same as you would when you create a permanent table. 

• Implicit creation of a temporary table:

Use the following syntax to create a temporary table schema implicitly from one or more tables via a query: 

CREATE TEMPORARY TABLE temptablename
SELECT * FROM permanenttable
LIMIT 0;

Setting LIMIT to 0 means it will only create the schema, but if you want the data as well, you can leave off LIMIT, or you can set it to a different LIMIT of your choosing.

When you create a temporary table with the following query, you will have a blank table with the columns specified in the query: 

USE lahmansbaseballdb; 
 CREATE TEMPORARY TABLE tempplayerinfo
 SELECT p.playerid, birthyear,
 a.yearid, a.teamid,
 G_defense AS defensegames,
 H AS numberofhits
 FROM lahmansbaseballdb.appearances AS a
 JOIN lahmansbaseballdb.people AS p
 ON p.playerid = a.playerid
 JOIN lahmansbaseballdb.batting AS b
 ON a.playerid = b.playerid
 AND a.yearid = b.yearid
 AND a.teamid = b.teamid
 WHERE b.yearid = 2017
 AND H <> 0
 LIMIT 0;

After creating the preceding temporary table, you can execute the following query to get information about the columns in the temporary table: 

USE lahmansbaseballdb; 
DESCRIBE tempplayerinfo;

The following screenshot shows the results of running the preceding query:

You can see that each of the columns listed in the SELECT portion of the query has been placed into the temporary table, along with any specification of the columns that exist in the permanent tables that those columns refer to.

When you execute the following query, you will see that the table is empty: 

USE lahmansbaseballdb;
SELECT * FROM tempplayerinfo;

This previous query returns no results because the original creation was set to LIMIT 0. 

If you set LIMIT to another number or leave LIMIT off when creating the temporary table, the table will have data in it, so you will have to drop it first and then recreate it.