Recipe 12.12. Combining Several Result Sets in a Single Query
ProblemYou want to select rows from several tables, or several sets of rows from a single tableall as a single result set. SolutionUse a UNION operation to combine multiple SELECT results into one. DiscussionA 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 | +-----------+------------+---------------------+ | 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 to do is make sure to select the same number of columns from each table, and in the same order. The following statement 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 column names in the result set are taken from the names 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 rows, including duplicates, follow each UNION keyword with ALL: mysql> SELECT fname, lname, addr FROM prospect -> UNION ALL -> SELECT first_name, last_name, address FROM customer -> UNION ALL -> 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, place each SELECT statement within parentheses and add an ORDER BY clause after the final one. 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 columns in 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 to ensure that the results from each SELECT appear consecutively, although you must generate an extra column to use for sorting. Enclose each SELECT within parentheses, add a sort-value column to each one, and place an ORDER BY at the end that sorts using that column: mysql> (SELECT 1 AS sortval, CONCAT(lname,', ',fname) AS name, addr -> FROM prospect) -> UNION -> (SELECT 2 AS sortval, CONCAT(last_name,', ',first_name) AS name, address -> FROM customer) -> UNION -> (SELECT 3 AS sortval, company, street FROM vendor) -> ORDER BY sortval; +---------+-------------------+------------------------+ | sortval | name | addr | +---------+-------------------+------------------------+ | 1 | Jones, Peter | 482 Rush St., Apt. 402 | | 1 | Smith, Bernice | 916 Maple Dr. | | 2 | Peterson, Grace | 16055 Seminole Ave. | | 2 | Smith, Bernice | 916 Maple Dr. | | 2 | Brown, Walter | 8602 1st St. | | 3 | ReddyParts, Inc. | 38 Industrial Blvd. | | 3 | Parts-to-go, Ltd. | 213B Commerce Park. | +---------+-------------------+------------------------+ If you also want the rows within each SELECT sorted, include a secondary sort column in the ORDER BY clause. The following query sorts by name within each SELECT: mysql> (SELECT 1 AS sortval, CONCAT(lname,', ',fname) AS name, addr -> FROM prospect) -> UNION -> (SELECT 2 AS sortval, CONCAT(last_name,', ',first_name) AS name, address -> FROM customer) -> UNION -> (SELECT 3 AS sortval, company, street FROM vendor) -> ORDER BY sortval, name; +---------+-------------------+------------------------+ | sortval | name | addr | +---------+-------------------+------------------------+ | 1 | Jones, Peter | 482 Rush St., Apt. 402 | | 1 | Smith, Bernice | 916 Maple Dr. | | 2 | Brown, Walter | 8602 1st St. | | 2 | Peterson, Grace | 16055 Seminole Ave. | | 2 | Smith, Bernice | 916 Maple Dr. | | 3 | Parts-to-go, Ltd. | 213B Commerce Park. | | 3 | 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. Typically, LIMIT is combined with ORDER BY. Suppose that 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 statement will always return three rows.) You could of course assure three rows in all cases by using UNION ALL. |
