O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required