Selecting Records in Parallel from Multiple Tables

12.17.1 Problem

You want to select rows one after the other from several tables, or several sets of rows from a single tableall as a single result set.

12.17.2 Solution

Use a UNION operation to combine multiple result sets into one.

12.17.3 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 one after the other, or different sets of rows from the same table. These are instances of the type of operation for which a UNION is useful. A UNION allows you to run several SELECT statements and concatenate their results "vertically." You receive the output in a single result set, rather than running multiple queries and receiving multiple result sets.

UNION is available as of MySQL 4.0. This section illustrates how to use it, then describes some workarounds if you have an older version of MySQL.

Suppose you have two tables that list prospective and actual customers, 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 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 |
+-----------+------------+---------------------+
| Peterson | Grace | 16055 Seminole Ave. |
| Smith | Bernice | 916 Maple Dr. |
| Brown | Walter | 8602 1st St. |
+-----------+------------+---------------------+
mysql> SELECT * FROM vendor;
+-------------------+---------------------+
| company | street |
+-------------------+---------------------+
| ReddyParts, Inc. | 38 Industrial Blvd. |
| Parts-to-go, Ltd. | 213B Commerce Park. |
+-------------------+---------------------+

The tables have columns that are similar but not identical. prospect and customer use different names for the first name and last name columns, and the vendor table includes only a single name column. None of that matters for UNION; all you need do is make sure to select the same number of columns from each table, and in the same order. The following query illustrates how to select names and addresses from the three tables all at once:

mysql> SELECT fname, lname, addr FROM prospect
 -> UNION
 -> SELECT first_name, last_name, address FROM customer
 -> UNION
 -> SELECT company, '', street FROM vendor;
+-------------------+----------+------------------------+
| fname | lname | addr |
+-------------------+----------+------------------------+
| Peter | Jones | 482 Rush St., Apt. 402 |
| Bernice | Smith | 916 Maple Dr. |
| Grace | Peterson | 16055 Seminole Ave. |
| Walter | Brown | 8602 1st St. |
| ReddyParts, Inc. | | 38 Industrial Blvd. |
| Parts-to-go, Ltd. | | 213B Commerce Park. |
+-------------------+----------+------------------------+

The names and types in the result set are taken from the names and types of the columns retrieved by the first SELECT statement. Notice that, by default, a UNION eliminates duplicates; Bernice Smith appears in both the prospect and customer tables, but only once in the final result. If you want to select all records, including duplicates, follow the first UNION keyword with ALL:

mysql> SELECT fname, lname, addr FROM prospect
 -> UNION ALL
 -> SELECT first_name, last_name, address FROM customer
 -> UNION
 -> SELECT company, '', street FROM vendor;
+-------------------+----------+------------------------+
| fname | lname | addr |
+-------------------+----------+------------------------+
| Peter | Jones | 482 Rush St., Apt. 402 |
| Bernice | Smith | 916 Maple Dr. |
| Grace | Peterson | 16055 Seminole Ave. |
| Bernice | Smith | 916 Maple Dr. |
| Walter | Brown | 8602 1st St. |
| ReddyParts, Inc. | | 38 Industrial Blvd. |
| Parts-to-go, Ltd. | | 213B Commerce Park. |
+-------------------+----------+------------------------+

Because it's necessary to select the same number of columns from each table, the SELECT for the vendor table (which has just one name column) retrieves a dummy (empty) last name column. Another way to select the same number of columns is to combine the first and last name columns from the prospect and customer tables into a single column:

mysql> SELECT CONCAT(lname,', ',fname) AS name, addr FROM prospect
 -> UNION
 -> SELECT CONCAT(last_name,', ',first_name), address FROM customer
 -> UNION
 -> SELECT company, street FROM vendor;
+-------------------+------------------------+
| name | addr |
+-------------------+------------------------+
| Jones, Peter | 482 Rush St., Apt. 402 |
| Smith, Bernice | 916 Maple Dr. |
| Peterson, Grace | 16055 Seminole Ave. |
| Brown, Walter | 8602 1st St. |
| ReddyParts, Inc. | 38 Industrial Blvd. |
| Parts-to-go, Ltd. | 213B Commerce Park. |
+-------------------+------------------------+

To sort the result set as a whole, add an ORDER BY clause after the final SELECT statement. Any columns specified by name in the ORDER BY should refer to the column names used in the first SELECT, because those are the names used for the result set. For example, to sort by name, do this:

mysql> SELECT CONCAT(lname,', ',fname) AS name, addr FROM prospect
 -> UNION
 -> SELECT CONCAT(last_name,', ',first_name), address FROM customer
 -> UNION
 -> SELECT company, street FROM vendor
 -> ORDER BY name;
+-------------------+------------------------+
| name | addr |
+-------------------+------------------------+
| Brown, Walter | 8602 1st St. |
| Jones, Peter | 482 Rush St., Apt. 402 |
| Parts-to-go, Ltd. | 213B Commerce Park. |
| Peterson, Grace | 16055 Seminole Ave. |
| ReddyParts, Inc. | 38 Industrial Blvd. |
| Smith, Bernice | 916 Maple Dr. |
+-------------------+------------------------+

It's possible in MySQL to sort the results of individual SELECT statements within the UNION. To do this, enclose a given SELECT (including its ORDER BY clause) within parentheses:

mysql> (SELECT CONCAT(lname,', ',fname) AS name, addr
 -> FROM prospect ORDER BY 1)
 -> UNION
 -> (SELECT CONCAT(last_name,', ',first_name), address
 -> FROM customer ORDER BY 1)
 -> UNION
 -> (SELECT company, street FROM vendor ORDER BY 1);
+-------------------+------------------------+
| name | addr |
+-------------------+------------------------+
| Jones, Peter | 482 Rush St., Apt. 402 |
| Smith, Bernice | 916 Maple Dr. |
| Brown, Walter | 8602 1st St. |
| Peterson, Grace | 16055 Seminole Ave. |
| Parts-to-go, Ltd. | 213B Commerce Park. |
| ReddyParts, Inc. | 38 Industrial Blvd. |
+-------------------+------------------------+

Similar syntax can be used for LIMIT as well. That is, you can limit the result set as a whole with a trailing LIMIT clause, or for individual SELECT statements by enclosing them within parentheses. In some cases, it may even be useful to combine ORDER BY and LIMIT. Suppose you want to select a lucky prizewinner for some kind of promotional giveaway. To select a single winner at random from the combined results of the three tables, do this:

mysql> SELECT CONCAT(lname,', ',fname) AS name, addr FROM prospect
 -> UNION
 -> SELECT CONCAT(last_name,', ',first_name), address FROM customer
 -> UNION
 -> SELECT company, street FROM vendor
 -> ORDER BY RAND( ) LIMIT 1;
+-----------------+---------------------+
| name | addr |
+-----------------+---------------------+
| Peterson, Grace | 16055 Seminole Ave. |
+-----------------+---------------------+

To select a single winner from each table and combine the results, do this instead:

mysql> (SELECT CONCAT(lname,', ',fname) AS name, addr
 -> FROM prospect ORDER BY RAND( ) LIMIT 1)
 -> UNION
 -> (SELECT CONCAT(last_name,', ',first_name), address
 -> FROM customer ORDER BY RAND( ) LIMIT 1)
 -> UNION
 -> (SELECT company, street
 -> FROM vendor ORDER BY RAND( ) LIMIT 1);
+------------------+---------------------+
| name | addr |
+------------------+---------------------+
| Smith, Bernice | 916 Maple Dr. |
| ReddyParts, Inc. | 38 Industrial Blvd. |
+------------------+---------------------+

If that result surprises you ("Why didn't it pick three rows?"), remember that Bernice is listed in two tables and that UNION eliminates duplicates. If the first and second SELECT statements each happen to pick Bernice, one instance will be eliminated and the final result will have only two rows. (If there are no duplicates among the three tables, the query will always return three rows.) You could of course assure three records in all cases by using UNION ALL, or by running the SELECT statements individually.

If you don't have MySQL 4.0, you can't use UNION. But you can achieve a similar result by creating a temporary table, storing the result of multiple SELECT queries into that table, and then selecting its contents. With MySQL 3.23, you can use CREATE TABLE ... SELECT for the first SELECT, then successively retrieve the other result sets into it:

mysql> CREATE TABLE tmp SELECT CONCAT(lname,', ',fname) AS name, addr
 -> FROM prospect;
mysql> INSERT INTO tmp (name, addr)
 -> SELECT CONCAT(last_name,', ',first_name), address
 -> FROM customer;
mysql> INSERT INTO tmp (name, addr)
 -> SELECT company, street FROM vendor;

If your version of MySQL is older than 3.23, create the table first, then select each result set into it:

mysql> CREATE TABLE tmp (name CHAR(40), addr CHAR(40));
mysql> INSERT INTO tmp (name, addr)
 -> SELECT CONCAT(lname,', ',fname), addr
 -> FROM prospect;
mysql> INSERT INTO tmp (name, addr)
 -> SELECT CONCAT(last_name,', ',first_name), address
 -> FROM customer;
mysql> INSERT INTO tmp (name, addr)
 -> SELECT company, street FROM vendor;

After selecting the individual results into the temporary table, select its contents:

mysql> SELECT * FROM tmp;
+-------------------+------------------------+
| name | addr |
+-------------------+------------------------+
| Jones, Peter | 482 Rush St., Apt. 402 |
| Smith, Bernice | 916 Maple Dr. |
| Peterson, Grace | 16055 Seminole Ave. |
| Smith, Bernice | 916 Maple Dr. |
| Brown, Walter | 8602 1st St. |
| ReddyParts, Inc. | 38 Industrial Blvd. |
| Parts-to-go, Ltd. | 213B Commerce Park. |
+-------------------+------------------------+

Note that the result is more like UNION ALL than UNION, because duplicates are not suppressed. To achieve the effect of UNION, create the table with a unique index on the name and addr columns:

mysql> CREATE TABLE tmp (name CHAR(40), addr CHAR(40), UNIQUE (name, addr));
mysql> INSERT INTO ...
...
mysql> SELECT * FROM tmp;
+-------------------+------------------------+
| name | addr |
+-------------------+------------------------+
| Brown, Walter | 8602 1st St. |
| Jones, Peter | 482 Rush St., Apt. 402 |
| Parts-to-go, Ltd. | 213B Commerce Park. |
| Peterson, Grace | 16055 Seminole Ave. |
| ReddyParts, Inc. | 38 Industrial Blvd. |
| Smith, Bernice | 916 Maple Dr. |
+-------------------+------------------------+

If you create the table without a unique index, you can remove duplicates at retrieval time by using DISTINCT, though that is less efficient.