Guidelines when 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.

  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 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. 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
  

See Also

UNION