Guidelines for Using Union
Follow these guidelines when using UNION operators:
All select lists in the statements that are being combined with UNION must have the same number of expressions (column names, arithmetic expressions, aggregate functions, and so on).
Corresponding columns in the result sets that are being combined with UNION, or any subset of columns used in individual queries, must be of the same data type, have an implicit data conversion possible between the two data types, or have an explicit conversion supplied. For example, a UNION between a column of datetime data type and one of binary data type will not work unless an explicit conversion is supplied. However, a UNION will work between a column of money data type and one of int data type, because they can be implicitly converted.
Columns of the xml data type must be equivalent. All columns must be either typed to an XML schema, or untyped. If typed, they must be typed to the same XML schema collection.
Corresponding result set columns in the individual statements that are being combined with UNION must occur in the same order, because UNION compares the columns one-to-one in the order given in the individual queries.
Here is an example.
Execute this query:
SELECT a, b FROM table3 UNION SELECT b, a FROM table4
Here is the result set:
a b -------- ----- 1.000000 abc 2.000000 def 3.000000 ghi 1.000000 jkl 5.000000 mno
When different data types are combined in a UNION operation, they are converted by using the rules of data type precedence. In the previous example, the int values are converted to float, because float has a higher precedence than int. For more information, see Data Type Precedence (Transact-SQL).
This query produces an error message, because the data types of corresponding columns are not compatible:
SELECT b, c FROM table3 UNION SELECT a, b FROM table4
The column names in the table that result from UNION are taken from the first individual query in the UNION statement. To refer to a column in the result set by a new name (for example, in an ORDER BY clause), the column must be referred to that way in the first SELECT:
SELECT city AS Cities FROM stores_west UNION SELECT city FROM stores_east ORDER BY city