UNION Operator

In SQL Server 6.5, new behavior is introduced for the UNION Operator due to ANSI-NULL handling.For SQL Server 6.5 information, see ANSI-standard Null Handling in What's New for SQL Server 6.5.

Combines the results of two or more queries into a single results set consisting of all the rows belonging to all queries in the union.

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]

where

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 Transact-SQL statement is the only one that can contain an INTO clause.
UNION: Is a set operation that requires two operands.
ALL: Incorporates all rows into the results, including duplicates.

Remarks

Because UNION is a binary operation, you must add parentheses to an expression involving more than two queries to specify evaluation order. The default evaluation order of a Transact-SQL statement containing UNION operators is left to right. By default duplicate rows are eliminated; however, using UNION with the ALL keyword returns all rows, including duplicates.

The first query in the UNION statement can contain an INTO clause that creates a table to hold the final results set. The INTO statement must be in the first query or an error message will be returned.

GROUP BY and HAVING clauses can be used only within individual queries and cannot be used to affect the final results set. 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 UNION operator cannot appear within a CREATE VIEW statement or within a subquery. You cannot specify the FOR BROWSE option in queries with the UNION operator.

The UNION operator can appear within an INSERT-SELECT statement. For example:

INSERT INTO salesall
    SELECT * FROM sales
    UNION
    SELECT * FROM saleseast

All select lists in a Transact-SQL statement must have the same number of expressions (such as column names, arithmetic expressions, and aggregate functions). For example, the following statement is invalid because the first select list is longer than the second:

SELECT au_id, title_id, au_ord FROM titleauthor
UNION
SELECT stor_id, date FROM sales

Corresponding columns in the individual queries of a UNION statement must occur in the same order, because UNION compares the columns one to one in the order given in the individual queries.

The descriptions of the columns that are part of a UNION operation do not have to be identical. The following table shows the rules for comparing the datatypes and options of corresponding (ith) columns.



Datatype of ith column	Datatype of ith column of results table

Not datatype-compatible (data conversion not handled implicitly by SQL Server).	Error returned by SQL Server.
Both fixed-length char with lengths L1 and L2.	Fixed-length char with length equal to the greater of L1 and L2.
Both fixed-length binary with lengths L1 and L2.	Fixed-length binary with length equal to the greater of L1 and L2.
Either or both variable-length char.	Variable-length char with length equal to the maximum of the lengths specified for the ith columns.
Either or both variable-length binary.	Variable-length binary with length equal to the maximum of the lengths specified for the ith columns.
Both numeric datatypes (for example, smallint, int, float, money).	Datatype equal to the maximum precision of the two columns. For example, if the ith column of table A is of type int and the ith column of table B is of type float, then the datatype of the ith column of the results table is float, because float is more precise than int.
Both columns' descriptions specify NOT NULL.	Specifies NOT NULL.

Examples

A. Simple UNION

The results set in this example includes the contents of the stor_id and stor_name columns of both the stores and storeseast tables.

SELECT stor_id, stor_name FROM stores
UNION
SELECT stor_id, stor_name FROM storeseast

B. Selecting INTO with UNION

In this example, the INTO clause in the first query specifies that the table called results hold the final results set of the union of the designated columns of the publishers, stores, and storeseast 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 storeseast

C. UNION of Two SELECT Statements with ORDER BY

The order of certain parameters used with the UNION clause is important. This example illustrates the incorrect and correct usage of UNION in two SELECT statements in which a column is to be renamed in the output.

/* INCORRECT */
SELECT city FROM storeseast
    ORDER BY cities
UNION
SELECT cities = city FROM stores

/* CORRECT */
SELECT cities = city FROM storeseast
UNION
SELECT city FROM stores
ORDER BY cities