Chapter 3: Using Aggregates, Grouping, and Sorting

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 mastersp_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. Specifically, you cannot use compute clauses within an insert...select 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

Starting with Adaptive Server version 12.5, you can use the union operator within a create view statement. If you are using an earlier version of Adaptive Server, however, 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.

Chapter 4: Joins: Retrieving Data from Several Tables

View this book as PDF