Guidelines for Building UNION Statements

Follow these guidelines when building UNION statements:

• All select lists in the UNION statement must have the same number of expressions (column names, arithmetic expressions, aggregate functions, and so on). The following statement is not valid because the first select list is longer than the second:

  SELECT stor_id, date, title_id FROM sales
  UNION 
  SELECT stor_id, title_id FROM sales_east

  Note Because pubs does not actually contain a stores_east table, you cannot run the examples containing stores_east.

• Corresponding columns in all tables, or any subset of columns used in the individual queries, must be of the same datatype, an implicit data conversion must be possible between the two datatypes, or an explicit conversion must be supplied. For example, UNION is not possible between a column of char datatype and one of int datatype unless an explicit conversion is supplied. However, a UNION is possible between a column of money datatype and one of int datatype. For details about comparing datatypes in a UNION statement, see UNION Operator in the Microsoft SQL Server Transact-SQL Reference.
• Corresponding columns in the individual queries of a UNION statement must occur in the same order, because UNION compares the columns one to one in the order given in the individual queries. For example, suppose that you have the following tables:

table3

table4

a

b

c

a

b

int

char(4)

char(4)

char(4)

float

---

-------

-------

-------

-------

1

abc

jkl

jkl

1.000

2

def

mno

mno

5.000

3

ghi

pqr

The following query:

SELECT a, b FROM table3
UNION 
SELECT b, a FROM table4

produces this results set:

a          b
--------   -----
1.000000   abc
2.000000   def
3.000000   ghi
1.000000   jkl
5.000000   mno

• When different datatypes are combined in a UNION statement, they are converted to the datatype with the most precision. In the preceding example, the int values are converted to float.

  The following query produces an error message because the datatypes 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. Therefore, if you want to define a new column heading for the results set, you must do it in the first query. If you want to refer to a column in the results set by a new name (for example, in an ORDER BY statement), it must be referred to that way in the first SELECT statement, as shown in this example:

  SELECT Cities = city FROM stores
  UNION 
  SELECT city FROM stores_east
  ORDER BY Cities