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 select_list [into clause] 
			[from clause] [where clause] 
			[group by clause] [having clause] 
	[union [all]
		select select_list 
			[from clause] [where clause] 
			[group by clause] [having clause] ]... 
	[order by clause] 
	[compute clause]

Parameters

union: creates the union of data specified by two select statements.

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

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.

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)

Usage

The total number of tables that can appear on all sides of a union query is 256.

You can use union in select statements, for example:

create view
select * from Jan1998Sales
union all
select * from Feb1998Sales
union all

The order by and compute clauses are allowed only at the end of the union statement to define the order of the final results or to compute summary values.
The group by and having clauses can be used only within individual queries and cannot be used to affect the final result set.
The default evaluation order of a SQL statement containing union operators is left-to-right.
Since union is a binary operation, parentheses must be added to an expression involving more than two queries to specify evaluation order.
The first query in a union statement may contain an into clause that creates a table to hold the final result set. The into statement must be in the first query, or you receive an error message (see Example 2).

The union operator can appear within an insert...select statement. For example:

insert into sales.overall 
  select * from sales 
  union 
  select * from sales_east

All select lists in a SQL statement must have the same number of expressions (column names, arithmetic expressions, aggregate functions, and so on). For example, the following statement is invalid because the first select list contains more expressions than the second:
```
/* Example of invalid command--shows imbalance */ /* in select list items */
select au_id, title_id, au_ord from titleauthor 
union 
select stor_id, date from sales
```
Corresponding columns in the select lists of union statements must occur in the same order, because union compares the columns one-to-one in the order given in the individual queries.
The column names in the table resulting from a union are taken from the first individual query in the union statement. To define a new column heading for the result set, do it in the first query. Also, to refer to a column in the result set by a new name (for example, in an order by statement), refer to it by that name in the first select statement. For example, the following query is correct:
```
select Cities = city from stores 
union 
select city from stores_east 
order by Cities
```

The descriptions of the columns that are part of a union operation do not have to be identical. Table 1-39 lists the rules for the datatypes and the corresponding column in the result table.

**Table 1-39: Resulting datatypes in union operations**
Datatype of columns in union operation	Datatype of corresponding column in result table
Not datatype-compatible (data conversion is not handled implicitly by Adaptive Server)	Error returned by Adaptive Server.
Both are fixed-length character with lengths L1 and L2	Fixed-length character with length equal to the greater of L1 and L2.
Both are fixed-length binary with lengths L1 and L2	Fixed-length binary with length equal to the greater of L1 and L2.
Either or both are variable-length character	Variable-length character with length equal to the maximum of the lengths specified for the column in the union.
Either or both are variable-length binary	Variable-length binary with length equal to the maximum of the lengths specified for the columns in the union.
Both are numeric datatypes (for example, smallint, int, float, money)	A datatype equal to the maximum precision of the two columns. For example, if a column in table A is of type int and the corresponding column in table B is of type float, then the datatype of the corresponding column of the result table is float, because float is more precise than int.
Both column descriptions specify NOT NULL	Specifies NOT NULL.

Restrictions

You cannot use the union operator in a subquery.
You cannot use the union operator with the for browse clause.
You cannot use the union operator on queries that select text or image data.

Standards

ANSI SQL – Compliance level: Entry-level compliant

The following are Transact-SQL extensions:

The use of union in the select clause of an insert statement
Specifying new column headings in the order by clause of a select statement when the union operator is present in the select statement