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.
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]
The top limit applies to the individual selects that form a union, not to the union as a whole.
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.
creates the union of data specified by two select statements.
includes all rows in the results; duplicates are not removed.
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
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
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)
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
The maximum number of subqueries within a single side of a union is 50.
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-35 lists the rules for the datatypes and the corresponding column in the result table.
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. |
You cannot use the union operator in a subquery.
You cannot use the union operator with the for browse clause.
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
Commands compute clause, declare, group by and having clauses, order by clause, select, where clause
Functions convert
