SQL in a Nutshell, 2nd Edition

Name

UNION Set Operator

Synopsis

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 called a set operator. 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
DB2	Supported, with limitations
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

Combines duplicate rows from all result sets.

DISTINCT

Eliminates duplicate rows from the final result set. Columns containing a NULL value are considered duplicates. (If neither ALL nor DISTINCT are 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 datatype of columns should be the same in all queries.

The datatypes do not have to be identical, but they should ...