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 ...
Get MySQL in a Nutshell, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.