The UNION operator allows you to combine the results of two or more SELECT statements into a single result set. The result sets combined using UNION must all have the same structure. They must have the same number of columns, and the corresponding result set columns must have compatible data types. For more information, see Guidelines for Using UNION.
UNION is specified as:
select_statement UNION [ALL] select_statement
For example, two tables contain:
Table1 |
Table2 |
|||
ColumnA |
ColumnB |
ColumnC |
ColumnD |
|
char(4) |
int |
char(4) |
int |
|
------- |
--- |
------- |
--- |
|
abc |
1 |
ghi |
3 |
|
def |
2 |
jkl |
4 |
|
ghi |
3 |
mno |
5 |
This query creates a UNION between the two tables:
SELECT * FROM Table1
UNION
SELECT * FROM Table2
Here is the result set:
ColumnA ColumnB
------- --------
abc 1
def 2
ghi 3
jkl 4
mno 5
The result set column names of a UNION are the same as the column names in the result set of the first SELECT statement in the UNION. The result set column names of the other SELECT statements are ignored.
By default, the UNION operator removes duplicate rows from the result set. If you use ALL, all rows are included in the results and duplicates are not removed.
Important The exact results of a UNION operation depend on the sort order chosen during installation and the ORDER BY clause. For information about the effects of different sort orders, see Sort Order.
Any number of UNION operators can appear in a Transact-SQL statement, for example:
SELECT * FROM TableA
UNION
SELECT * FROM TableB
UNION
SELECT * FROM TableC
UNION
SELECT * FROM TableD
By default, Microsoft® SQL Server™ evaluates a statement containing UNION operators from left to right. Use parentheses to specify the order of evaluation. For example, the following statements are not equivalent:
/* First statement. */
SELECT * FROM TableA
UNION ALL
( SELECT * FROM TableB
UNION
SELECT * FROM TableC
)
GO
/* Second statement. */
(SELECT * FROM TableA
UNION ALL
SELECT * FROM TableB
)
UNION
SELECT * FROM TableC)
GO
In the first statement, duplicates are eliminated in the union between TableB and TableC. Then, in the union between that set and TableA, duplicates are not eliminated. In the second statement, duplicates are included in the union between TableA and TableB but are eliminated in the subsequent union with TableC. ALL has no effect on the final result of this expression.
When UNION is used, the individual SELECT statements cannot have their own ORDER BY or COMPUTE clauses. There can only be one ORDER BY or COMPUTE clause after the last SELECT statement, it is applied to the final, combined result set.
GROUP BY and HAVING can only be specified in the individual SELECT statements.
| UNION |