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.




Sams Teach Yourself MySQL in 10 Minutes
Sams Teach Yourself MySQL in 10 Minutes
ISBN: 0672328631
EAN: 2147483647
Year: 2006
Pages: 165
Authors: Chris Newman

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net