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 UNIONTo 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 MINUSTo 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 INTERSECTTo 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. |