Guidelines for Using UNION

Follow these guidelines when using UNION operators:

All select lists in the statements 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 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, UNION is not possible between a column of datetime data type and one of binary data type unless an explicit conversion is supplied, while UNION is possible between a column of money data type and one of int data type because they can be implicitly converted.
Corresponding result set columns in the individual statements 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.
For example, two tables contain:

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 using the rules of data type precedence. In the preceding example, the int values are converted to float because float has a higher precedence than int. For more information, see Data Type Precedence.
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 resulting from UNION are taken from the first individual query in the UNION statement. If you want to refer to a column in the result set by a new name (for example, in an ORDER BY clause), it 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