Combining queries: the union operator

The union operator combines the results of two or more queries into a single result set. The Transact-SQL extension to union allows you to:

• Use union in the select clause of an insert statement.
• Specify new column headings in the order by clause of a select statement when union is present in the select statement.

The syntax of the union operator is as follows:

query1
     [union [all] queryN ] ... 
     [order by clause] 
     [compute
clause]

where query1 is:

select select_list[into clause]
[from clause]
[where clause]
[group by clause]
[having clause]

and queryN is:

select select_list[from clause]
[where clause]
[group by clause]
[having clause]

Figure 3-1: Union combining queriesFor example, suppose you have the following two tables containing the data shown:
The following query creates a union between the two tables:

create table T1 (a char(4), b int)

insert T1 values ("abc", 1)

insert T1 values ("def", 2)

insert T1 values ("ghi", 3)

create table T2 (a char(4), b int)

insert T2 values ("ghi", 3)

insert T2 values ("jkl", 4)

insert T2 values ("mno", 5)

select * from T1 
union 
select * from T2 

a     b
----  --------- 
abc           1 
def           2 
ghi           3 
jkl           4 
mno           5 
 
(5 rows affected)

By default, the union operator removes duplicate rows from the result set. Use the all option to include duplicate rows. Notice also that the columns in the result set have the same names as the columns in T1. You can use any number of union operators in a Transact-SQL statement. For example:

x union y union z 

By default, Adaptive Server evaluates a statement containing union operators from left to right. You can use parentheses to specify a different evaluation order.
For example, the following two expressions are not equivalent:

x union all (y union z)

(x union all y) union z 

In the first expression, duplicates are eliminated in the union between y and z. Then, in the union between that set and x, duplicates are not eliminated. In the second expression, duplicates are included in the union between x and y, but are then eliminated in the subsequent union with z; all does not affect the final result of this statement.

Guidelines for union queries

When you use union statements:

• All select lists in the union statement must have the same number of expressions (such as column names, arithmetic expressions, and aggregate functions). The following statement is invalid because the first select list is longer than the second:
  

  create table stores_east
  (stor_id char(4) not null,
  stor_name varchar(40) null,
  stor_address varchar(40) null,
  city varchar(20) null,
  state char(2) null,
  country varchar(12) null,
  postalcode char(10) null,
  payterms varchar(12) null)

  select stor_id, city, state from stores 
  union 
  select stor_id, city from stores_east

  drop table stores_east

• Corresponding columns in all tables, or any subset of columns used in the individual queries, must be of the same datatype, or an implicit data conversion must be possible between the two datatypes, or an explicit conversion should be supplied. For example, a union is not possible between a column of the char datatype and one of the int datatype, unless an explicit conversion is supplied. However, a union is possible between a column of the money datatype and one of the int datatype. See union and "Datatype Conversion Functions" in the Reference Manual for more information about comparing datatypes in a union statement.
• You must place corresponding columns in the individual queries of a union statement in the same order, because union compares the columns one to one in the order given in the query. For example, suppose you have the following tables:
  Figure 3-2: Union comparing columnsThis query:
  

  select a, b from T3 
  union 
  select b, a from T4 

  produces:
  

  a          b
  ---------  --- 
          1  abc 
          2  def 
          3  ghi 
   
  (3 rows affected)

  The following query results in an error message, because the datatypes of corresponding columns are not compatible:
  

  select a, b from T3 
  union 
  select a, b from T4

  drop table T3

  drop table T4

  When you combine different (but compatible) datatypes such as float and int in a union statement, Adaptive Server converts them to the datatype with the most precision.
• Adaptive Server takes the column names in the table resulting from a union from the first individual query in the union statement. Therefore, to define a new column heading for the result set, do so in the first query. In addition, to refer to a column in the result set by a new name, for example, in an order by statement, refer to it in that way in the first select statement.
  The following query is correct:
  

  select Cities = city from stores 
  union 
  select city from authors 
  order by Cities 

Using union with other Transact-SQL commands

When you use union statements with other Transact-SQL commands:

• The first query in the union statement may contain an into clause that creates a table to hold the final result set. For example, the following statement creates a table called results that contains the union of tables publishers, stores, and salesdetail:
  

  use master

  sp_dboption pubs2, "select into", true

  use pubs2

  checkpoint

  select pub_id, pub_name, city into results 
  from publishers 
  union 
  select stor_id, stor_name, city from stores 
  union 
  select stor_id, title_id, ord_num from salesdetail 

  You can use the into clause only in the first query; if it appears anywhere else, you get an error message.
• You can use order by and compute clauses only at the end of the union statement to define the order of the final results or to compute summary values. You cannot use them within the individual queries that make up the union statement.
• You can use group by and having clauses within individual queries only; you cannot use them to affect the final result set.
• You can also use the union operator within an insert statement. For example:
  

  create table tour (city varchar(20), state char(2))

  insert into tour 
      select city, state from stores 
      union 
      select city, state from authors 

  drop table tour

• You cannot use the union operator within a create view statement.
• You cannot use the union operator on text and image columns.
• You cannot use the for browse clause in statements involving the union operator.