Using Temporary Tables

Temporary tables in MySQL can be a powerful means of manipulating a data set. A temporary table exists only for the duration of your MySQL session, after which it is automatically deleted. Otherwise, it works just the same as a regular database table.

You will learn more about creating database tables in Lesson 14, "Creating and Modifying Tables." For now, consider some examples to simulate different types of a compound query.

Performing a UNION

To join two or more queries using a temporary table, you need to insert the data rows from both queries into the same table.

First, create a new table using the first query to define the columns. The result of this query will also be inserted into the table.

 CREATE TEMPORARY TABLE myunion AS SELECT first_name FROM customer_contacts; 

In this case, a temporary table with a single column is created. You can then insert the result of the second query as follows:

 INSERT INTO myunion SELECT last_name FROM customer_contacts; 

The table myunion now contains the union. In fact, if you performed a SELECT on this table, it would give the same result that a UNION ALL operation on the two queries would produce. To simulate a UNION, in which only distinct values are returned, execute this command:

 SELECT DISTINCT first_name FROM myunion; 

Performing a MINUS

To simulate a MINUS using a temporary table, first create a new table using the first query to define the columns and populate the initial data rows.

 CREATE TEMPORARY TABLE myminus AS SELECT first_name FROM customer_contacts; 

Then delete the rows that also appear in the second query using a subquery, as follows:

 DELETE FROM myminus WHERE first_name IN (   SELECT last_name   FROM customer_contacts ); 

The data remaining in the myminus table is the same result that you would have achieved using a MINUS operator on the two queries.

Performing an INTERSECT

To simulate an INTERSECT using a temporary table, first create a new table using the first query to define the columns and populate the initial data rows.

 CREATE TEMPORARY TABLE myminus AS SELECT first_name FROM customer_contacts; 

Then delete the rows that do not also appear in the second query using a subquery and the NOT IN operator, as follows:

 DELETE FROM myminus WHERE first_name NOT IN (   SELECT last_name   FROM customer_contacts ); 

The data remaining in the myminus table is the same result that you would have achieved using an INTERSECT operator on the two queries.