9.6. Using UNION

9.6. Using UNION

The UNION keyword enables you to concatenate the results from two or more SELECT statements. The syntax for using it is as follows:

 SELECT ... UNION SELECT ... UNION SELECT ... 

The result of such a statement consists of the rows retrieved by the first SELECT, followed by the rows retrieved by the second SELECT, and so on. Each SELECT must produce the same number of columns.

By default, UNION eliminates duplicate rows from the result set. To retain all rows, replace each instance of UNION with UNION ALL. (UNION ALL is more efficient for the server to process because it need not perform duplicate removal. However, returning the result set to the client involves more network traffic.)

UNION is useful under the following circumstances:

• You have similar information in multiple tables and you want to retrieve rows from all of them at once.

• You want to select several sets of rows from the same table, but the conditions that characterize each set aren't easy to write as a single WHERE clause. UNION allows retrieval of each set with a simpler WHERE clause in its own SELECT statement; the rows retrieved by each are combined and produced as the final query result.

Suppose that you run three mailing lists, each of which is managed using a different MySQL-based software package. Each package uses its own table to store names and email addresses, but they have slightly different conventions about how the tables are set up. The tables used by the list manager packages look like this:

 CREATE TABLE list1 (     subscriber  CHAR(60),     email       CHAR(60) ); CREATE TABLE list2 (     name        CHAR(96),     address     CHAR(128) ); CREATE TABLE list3 (     email       CHAR(50),     real_name   CHAR(30) ); 

Note that each table contains similar types of information (names and email addresses), but they don't use the same column names or types, and they don't store the columns in the same order. To write a query that produces the combined subscriber list, use UNION. It doesn't matter that the tables don't have exactly the same structure. To select their combined contents, name the columns from each table in the order you want to see them. A query to retrieve names and addresses from the tables looks like this:

 SELECT subscriber, email FROM list1 UNION SELECT name, address FROM list2 UNION SELECT real_name, email FROM list3; 

The first column of the result contains names and the second column contains email addresses. The names of the columns resulting from a UNION are taken from the names of the columns in the first SELECT statement. This means that the result set column names are subscriber and email. If you provide aliases for columns in the first SELECT, the aliases are used as the output column names.

The data types of the output columns are determined by considering the values retrieved by all of the SELECT statements. For the query shown, the data types will be CHAR(96) and CHAR(128) because those are the smallest types that are guaranteed to be large enough to hold values from all three tables.

ORDER BY and LIMIT clauses can be used to sort or limit a UNION result set as a whole. To do this, surround each SELECT with parentheses and then add ORDER BY or LIMIT after the last parenthesis. Columns named in such an ORDER BY should refer to columns in the first SELECT of the statement. (This is a consequence of the fact that the first SELECT determines the result set column names.) The following statement sorts the result of the UNION by email address and returns the first 10 rows of the combined result:

 (SELECT subscriber, email FROM list1) UNION (SELECT name, address FROM list2) UNION (SELECT real_name, email FROM list3) ORDER BY email LIMIT 10; 

ORDER BY and LIMIT clauses also can be applied to individual SELECT statements within a UNION. Surround each SELECT with parentheses and add ORDER BY or LIMIT to the end of the appropriate SELECT. In this case, an ORDER BY should refer to columns of the particular SELECT with which it's associated. (Also, although LIMIT may be used by itself in this context, ORDER BY has no effect unless combined with LIMIT. The optimizer ignores it otherwise.) The following query sorts the result of each SELECT by email address and returns the first five rows from each one:

 (SELECT subscriber, email FROM list1 ORDER BY email LIMIT 5) UNION (SELECT name, address FROM list2 ORDER BY address LIMIT 5) UNION (SELECT real_name, email FROM list3 ORDER BY email LIMIT 5);