UNION Operation

Syntax

select-without-order-by
   … UNION [ ALL ] select-without-order-by
   … [ UNION [ ALL ] select-without-order-by ]…
   … [ ORDER BY integer [ ASC | DESC ] [, …] ]

Parameters

All – the results of UNION ALL are the combined results of the component SELECT statements. The results of UNION are the same as UNION ALL, except that duplicate rows are eliminated. Eliminating duplicates requires extra processing, so UNION ALL should be used instead of UNION where possible.

ORDER BY – only integers are allowed in the order by list. These integers specify the position of the columns to be sorted.

Examples

Example 1 – list all distinct surnames of employees and customers:
```
SELECT Surname
FROM Employees
UNION
SELECT Surname
FROM Customers
```

Usage

The results of several SELECT statements can be combined into a larger result using a UNION clause. The component SELECT statements must each have the same number of items in the select list, and cannot contain an ORDER BY clause. See FROM Clause.

If corresponding items in two select lists have different data types, SAP Sybase IQ chooses a data type for the corresponding column in the result, and automatically converts the columns in each component SELECT statement appropriately.

The column names displayed are the same column names that display for the first SELECT statement.

Note: When SELECT statements include constant values and UNION ALL views but omit the FROM clause, use iq_dummy to avoid errors. See FROM Clause for details.

Standards

SQL—ISO/ANSI SQL compliant.
Sybase—Supported by Adaptive Server Enterprise, which also supports a COMPUTE clause.

Permissions

Requires SELECT privilege for each component of the SELECT statements.