Reference Manual: Commands

Home

Reference Manual: Commands

Chapter 1 Commands

union operator

Usage

The total number of tables that can appear on all sides of a union query is 256.

You can use union in select statements, for example:

create view
select * from Jan1998Sales
union all
select * from Feb1998Sales
union all

The order by and compute clauses are allowed only at the end of the union statement to define the order of the final results or to compute summary values.
The group by and having clauses can be used only within individual queries and cannot be used to affect the final result set.
The default evaluation order of a SQL statement containing union operators is left-to-right.
Since union is a binary operation, parentheses must be added to an expression involving more than two queries to specify evaluation order.
The first query in a union statement may contain an into clause that creates a table to hold the final result set. The into statement must be in the first query, or you receive an error message (see Example 2).

The union operator can appear within an insert...select statement. For example:

insert into sales.overall 
  select * from sales 
  union 
  select * from sales_east

All select lists in a SQL statement must have the same number of expressions (column names, arithmetic expressions, aggregate functions, and so on). For example, the following statement is invalid because the first select list contains more expressions than the second:
```
/* Example of invalid command--shows imbalance */ /* in select list items */
select au_id, title_id, au_ord from titleauthor 
union 
select stor_id, date from sales
```
Corresponding columns in the select lists of union statements must occur in the same order, because union compares the columns one-to-one in the order given in the individual queries.
The column names in the table resulting from a union are taken from the first individual query in the union statement. To define a new column heading for the result set, do it in the first query. Also, to refer to a column in the result set by a new name (for example, in an order by statement), refer to it by that name in the first select statement. For example, the following query is correct:
```
select Cities = city from stores 
union 
select city from stores_east 
order by Cities
```

The descriptions of the columns that are part of a union operation do not have to be identical. Table 1-39 lists the rules for the datatypes and the corresponding column in the result table.

Table 1-39: Resulting datatypes in union operations

Datatype of columns in union operation	Datatype of corresponding column in result table
Not datatype-compatible (data conversion is not handled implicitly by Adaptive Server)	Error returned by Adaptive Server.
Both are fixed-length character with lengths L1 and L2	Fixed-length character with length equal to the greater of L1 and L2.
Both are fixed-length binary with lengths L1 and L2	Fixed-length binary with length equal to the greater of L1 and L2.
Either or both are variable-length character	Variable-length character with length equal to the maximum of the lengths specified for the column in the union.
Either or both are variable-length binary	Variable-length binary with length equal to the maximum of the lengths specified for the columns in the union.
Both are numeric datatypes (for example, smallint, int, float, money)	A datatype equal to the maximum precision of the two columns. For example, if a column in table A is of type int and the corresponding column in table B is of type float, then the datatype of the corresponding column of the result table is float, because float is more precise than int.
Both column descriptions specify NOT NULL	Specifies NOT NULL.

Restrictions

You cannot use the union operator in a subquery.
You cannot use the union operator with the for browse clause.
You cannot use the union operator on queries that select text or image data.


	Expand Search