union operator

Returns a single result set that combines the results of two or more queries. Duplicate rows are eliminated from the result set unless the all keyword is specified.

Syntax

select [top unsigned_integer] select_list
	[into clause] [from clause] [where clause]
	[group by clause] [having clause]
	[union [all]
	select [top unsigned_integer] select_list
	[from clause] [where clause]
	[group by clause] [having clause]]...
	[order by clause]
	[compute clause]

Parameters

Examples

Usage

  • Restrictions:
    • You cannot use the union operator in a a subquery

    • You cannot use the union operator with the for browse clause

    • You cannot use an identity function in a select into statement with the union operator.

  • The maximum number of subqueries within a single side of a union is 250.

  • 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. The rules for the datatypes and the corresponding column in the result table are:

    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 the SAP ASE server)

    Error returned by the SAP ASE 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.

See also convert in Reference Manual: Building Blocks.

Standards

ANSI SQL – Compliance level: Entry-level compliant

The following are Transact-SQL extensions:
  • The use of union in the select clause of an insert statement

  • Specifying new column headings in the order by clause of a select statement when the union operator is present in the select statement

Related reference
compute Clause
declare
group by and having Clauses
order by clause
select
where clause