MySQL Cookbook, 2nd Edition

Combining Several Result Sets in a Single Query

Problem

You want to select rows from several tables, or several sets of rows from a single table—all as a single result set.

Solution

Use a UNION operation to combine multiple SELECT results into one.

Discussion

A join is useful for combining columns from different tables side by side. It’s not so useful when you want a result set that includes a set of rows from several tables, or multiple sets of rows from the same table. These are instances of the type of operation for which a UNION is useful. A UNION enables you to run several SELECT statements and combine their results. That is, rather than running multiple queries and receiving multiple result sets, you receive a single result set.

Suppose that you have two tables that list prospective and actual customers, and a third that lists vendors from whom you purchase supplies, and you want to create a single mailing list by merging names and addresses from all three tables. UNION provides a way to do this. Assume that the three tables have the following contents:

mysql>SELECT * FROM prospect;
+---------+-------+------------------------+
| fname   | lname | addr                   |
+---------+-------+------------------------+
| Peter   | Jones | 482 Rush St., Apt. 402 |
| Bernice | Smith | 916 Maple Dr.          |
+---------+-------+------------------------+
mysql> SELECT * FROM customer; +-----------+------------+---------------------+ | last_name | first_name | address | +-----------+------------+---------------------+ ...