Table of Contents for

MySQL in a Nutshell, 2nd Edition

The UNION keyword unites the results of multiple SELECT statements into one results set. The SELECT statements can retrieve data from the same table or from different tables. If different tables are used, the results set generated by each SQL statement should match in column count and the order of column types. The column names do not need to be the same, but the data sent to the respective fields in the results set needs to match.

Don’t confuse this statement with the JOIN clause or a subquery, which are used to merge columns of data from multiple tables into rows in the results of a SELECT statement. In contrast, the UNION clause is used to merge together the results tables of separate and distinct SELECT statements into one results table.

Here is an example of a UNION used to merge the results of two SELECT statements:

SELECT CONCAT(name_first, SPACE(1), name_last) AS Name,
telephone_work AS Telephone
FROM employees
UNION
SELECT location, telephone FROM branches
ORDER BY Name;

This statement presents a list of employees and branch office locations in one column, with the telephone number for each in the second. The column headings used for the results set will be the ones used for the first SELECT statement. Because of the ORDER BY clause, the results will be sorted by the values for the alias Name. Otherwise, the names of employees would be listed before the names of offices. The example shown merges the results of only two SELECT statements. You can merge several SELECT statements, entering the UNION keyword before each additional SELECT statement.

If the results set is to be sorted based on a column, the table name must not be specified in the ORDER BY clause (i.e., table.column is not accepted). To resolve ambiguity, use an alias for the columns to order by. If an alias has been given for a column that is to be part of the ORDER BY clause, that alias must be used instead of the column name. The use of column position has been deprecated.

The keyword DISTINCT indicates that any duplicated rows (rows where all of the data of all columns is the same as a previous row) are not included in the results. This is the default, so it’s not necessary to include the keyword DISTINCT. Including the keyword ALL, though, will instruct MySQL to include all rows, including duplicates.

To limit the results of a union, add the LIMIT clause to the end of the SQL statement:

SELECT CONCAT(name_first, SPACE(1), name_last) AS Name,
telephone_work AS Telephone
FROM employees
UNION
SELECT location, telephone FROM branches
ORDER BY Name
LIMIT 10;

To limit the results of one table in a union and not the final results set, put parentheses around the individual SELECT statements and add the LIMIT clause to the end of the SELECT statement or statements that you want to limit:

( SELECT CONCAT(name_first, SPACE(1), name_last) AS Name,
telephone_work AS Telephone FROM employees LIMIT 10 )
UNION
( SELECT location, telephone FROM branches )
ORDER BY Name;

This statement limits the results to only 10 employees, but allows all of the branches to be displayed. You can put limits on each SELECT statement if you want, and limit the final results by adding the LIMIT clause to the end of the full SQL statement.