SQL in a Nutshell, 3rd Edition

Name

UNION Set Operator

The UNION set operator combines the result sets of two or more queries, showing all the rows returned by each of the queries as one single result set.

UNION is in a class of keyword known as set operators. Other set operators include INTERSECT and EXCEPT/MINUS. (EXCEPT and MINUS are functually equivalent; EXCEPT is the ANSI standard.) All set operators are used to simultaneously manipulate the result sets of two or more queries; hence the term “set operators.”

Platform	Command
MySQL	Not supported
Oracle	Supported, with limitations
PostgreSQL	Supported, with limitations
SQL Server	Supported, with limitations

SQL2003 Syntax

There are technically no limits to the number of queries that you may combine with the UNION statement. The general syntax is:

<SELECT statement1>
UNION [ALL | DISTINCT]
<SELECT statement2>
UNION [ALL | DISTINCT]
...

Keywords

UNION

Determines which result sets will be combined into a single result set. Duplicate rows are, by default, excluded.

ALL | DISTINCT

Combines duplicate rows from all result sets (ALL) or eliminates duplicate rows from the final result set (DISTINCT). Columns containing a NULL value are considered duplicates. If neither ALL nor DISTINCT is used, DISTINCT behavior is the default.

Rules at a Glance

There is only one significant rule to remember when using UNION: the order, number, and datatypes of the columns should be the same in all queries.

The datatypes do not have to be identical, but they should be compatible. For example, CHAR and ...