Combined Queries-UNION

Mosy SQL queries contain just one SELECT statement that can return data from one table, or several tables using a join. The technique of combining two or more independent queries into a single data set is usually known as a union or a compound query.

You might want to use this technique to retrieve records from two tables that have a similar structure in a single query. For instance, suppose you have archived off some data so that you have a customers table that contains your current customers and another table called old_customers.

These tables would have the same structureor at least would share many common columns if new columns had been added to the customers table since the archive took place. Therefore, you could perform a query on this table that takes into account both current and archived customers. This would look something like the following:

SELECT name, telephone, email
FROM customers
UNION
SELECT name, telephone, email
FROM old_customers;

You can also use UNION to perform two different queries on the same table and combine the two results into a single data set.

mysql> SELECT first_name, last_name
    -> FROM customer_contacts
    -> WHERE customer_code = 'SCICORP'
    -> UNION
    -> SELECT first_name, last_name
    -> FROM customer_contacts
    -> WHERE customer_code = 'PRESINC';
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| Albert     | Einstein  |
| Charles    | Darwin    |
| Marie      | Curie     |
| Benjamin   | Franklin  |
| Abraham    | Lincoln   |
| Richard    | Nixon     |
| Franklin   | Roosevelt |
| Theodore   | Roosevelt |
+------------+-----------+
8 rows in set (0.01 sec)