UNION Operator
Combines the results of two or more queries into a single result set consisting of all the rows belonging to all queries in the union. This is different from using joins that combine columns from two tables.
Two basic rules for combining the result sets of two queries with UNION are:
- The number and the order of the columns must be identical in all queries.
- The data types must be compatible.
Syntax
{ < query specification > | ( < query expression > ) }
UNION [ ALL ]
< query specification | ( < query expression > )
[ UNION [ ALL ] < query specification | ( < query expression > )
[ ...n ] ]
Arguments
< query_specification > | ( < query_expression > )
Is a query specification or query expression that returns data to be combined with the data from another query specification or query expression. The definitions of the columns that are part of a UNION operation do not have to be identical, but they must be compatible through implicit conversion.
The table shows the rules for comparing the data types and options of corresponding (ith) columns.
| Data type of ith column | Data type of ith column of results table |
|---|---|
| Not data type-compatible (data conversion not handled implicitly by Microsoft® 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 data types (for example, smallint, int, float, money). | Data type 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 data type 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. |
UNION
Specifies that multiple result sets are to be combined and returned as a single result set.
ALL
Incorporates all rows into the results, including duplicates. If not specified, duplicate rows are removed.