union operator

Description

Returns a single result set that combines the results of two or more queries. Duplicate rows are eliminated from the result set unless the all keyword is specified.

Syntax

select [top unsigned_integer] select_list
	[into clause] [from clause] [where clause]
	[group by clause] [having clause]
	[union [all]
	select [top unsigned_integer] select_list
	[from clause] [where clause]
	[group by clause] [having clause]]...
	[order by clause]
	[compute clause]

Parameters

top unsigned_integer

The top limit applies to the individual selects that form a union, not to the union as a whole.

into

creates a new table based on the columns specified in the select list and the rows chosen in the where clause. The first query in the union operation is the only one that can contain an into clause.

union

creates the union of data specified by two select statements.

all

includes all rows in the results; duplicates are not removed.

Examples

Example 1

The result set includes the contents of the stor_id and stor_name columns of both the sales and sales_east tables:

select stor_id, stor_name from sales 
union 
select stor_id, stor_name from sales_east

Example 2

The into clause in the first query specifies that the results table holds the final result set of the union of the specified columns of the publishers, stores, and stores_east tables:

select pub_id, pub_name, city into results
from publishers 
union 
select stor_id, stor_name, city from stores 
union 
select stor_id, stor_name, city from stores_east

Example 3

First, the union of the specified columns in the sales and sales_east tables is generated. Then, the union of that result with publishers is generated. Finally, the union of the second result and authors is generated:

select au_lname, city, state from authors 
union 
 ((select stor_name, city, state from sales 
union 
select stor_name, city, state from sales_east) 
union 
select pub_name, city, state from publishers)

Example 4

Returns six rows. The top limit applies to the individual selects that form a union, not to the union as a whole:

select top 3 au_lname from authors
union all
select top 3 title from titles

Usage


Restrictions

Standards

ANSI SQL – Compliance level: Entry-level compliant

The following are Transact-SQL extensions:

See also

Commands compute clause, declare, group by and having clauses, order by clause, select, where clause

Functions convert