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.

  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

  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