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]
queryN is:
select select_list [from clause] [where clause] [group by clause] [having clause]
For example, suppose you have the following two tables containing the data shown:
Figure 3-3: Union combining queries
Figure 3-1 shows two tables, T1 and T2. T1 shows two columns, “a, char(4),” and “b,”char(4). T2 contains two columns, “a char(4),” and “b, int.” Each table has three rows: in T1, Row 1 shows “abc” in the “a” column and “1” in the “b” column. T1 Row 2 shows “def” in the “a” column, and “2” in the “b” column. Row 3 shows “ghi” in the “a” column, and “3” in the “b int” column. Table T4, Row 1, shows “ghi” in the “a” column and “1” in the “b” column; Row 2 shows “jkl” in the “a” column and “2” in the “b” column; Row 3 shows “mno” in the “a” column and “3” in the “b(int)” column.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.
