Combining Results Sets with UNION
The UNION operator allows you to combine the results of two or more SELECT statements into a single result set. The result sets that are combined by 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, Table1 and Table2 have the same two-column structure.
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 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.
The exact results of a UNION operation depend on the collation chosen during installation and the ORDER BY clause. For more information about the effects of different collations, see Working with Collations.
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, SQL Server 2005 evaluates a statement that contains 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
Remarks
In the first statement, duplicates are eliminated in the union between TableB and TableC. 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 be only 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 be specified only in the individual SELECT statements.
See Also