The UNION Operator

The UNION operator allows you to manipulate the results of two or more queries by combining the results of each query into a single results set. The syntax is as follows:

Query1
[UNION [ALL] QueryN ] ...
[ORDER BY clause]
[COMPUTE clause]

where Query1 is:

SELECT select_list
[INTO clause]
[FROM clause]
[WHERE clause]
[GROUP BY clause]
[HAVING clause]

and QueryN is:

SELECT select_list
[FROM clause]
[WHERE clause]
[GROUP BY clause]
[HAVING clause]

For example, suppose that you have the following tables:

table1		table2
a	b	c	d
char(4)	int	char(4)	int
-------	---	-------	---
abc	1	ghi	3
def	2	jkl	4
ghi	3	mno	5

The following query creates a UNION between the two tables:

SELECT * FROM table1
UNION
SELECT * FROM table2

This is the results set:

a	b
-------	---
abc	1
def	2
ghi	3
jkl	4
mno	5

Notice that, by default, the UNION operator removes duplicate rows from the results set. If you use the ALL option, all rows are included in the results and duplicates are not removed. Notice also that the columns in the results set have the same names as the columns in table1.

Important The exact results of a UNION operation depend on the sort order chosen during installation. For information about the effects of different sort orders, see Microsoft SQL Server Setup.

Any number of UNION operators can appear in a Transact-SQL statement. For example:

x UNION y UNION z

By default, SQL Server evaluates a statement containing UNION operators from left to right. You can use parentheses to specify the order of evaluation. For example, the following expressions are not equivalent:

x UNION ALL (y UNION z)

(x UNION ALL y) UNION z

In the first expression, duplicates are eliminated in the union between y and z. Then, in the union between that set and x, duplicates are not eliminated. In the second expression, duplicates are included in the union between x and y but are then eliminated in the subsequent union with z. ALL has no effect on the final result of this statement.