Using Temporary Tables
Temporary tables in MariaDB can be a powerful means of manipulating a data set. A temporary table exists only for the duration of your MariaDB 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.