MySQL in a Nutshell, 2nd Edition

Name

UNION

Synopsis

SELECT... UNION [ALL|DISTINCT] SELECT...[, UNION...]

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 ...