Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Statements

TRUNCATE TABLE statement Next Page

UNION statement


Use this statement to combine the results of two or more select statements.

Syntax

[ WITH temporary-views ] query-block
UNION [ ALL | DISTINCT ] query-block
[ ORDER BY [ integer | select-list-expression-name ] [ ASC | DESC ], ... ]
[ FOR XML xml-mode ]
[ OPTION( query-hint, ... ) ]

query-hint :
MATERIALIZED VIEW OPTIMIZATION option-value
| FORCE OPTIMIZATION
| option-name = option-value

option-name : identifier

option-value : hostvar (indicator allowed), string, identifier, or number

Parameters
Note

You cannot use the FOR, FOR XML, WITH, or OPTION clause in the query-block.

OPTION clause    This clause provides hints as to how to process the query. The following query hints are supported:

Remarks

The results of several query blocks can be combined into a larger result using UNION. Each query-block must have the same number of items in the select list.

The results of UNION ALL are the combined results of the query blocks. 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. UNION DISTINCT is identical to UNION.

If corresponding items in two select lists have different data types, SQL Anywhere chooses a data type for the corresponding column in the result and automatically convert the columns in each query-block appropriately.

The first query block of the UNION is used to determine the names to be matched with the ORDER BY clause.

The column names displayed are the same column names that are displayed for the first query-block. An alternative way of customizing result set column names is to use the WITH clause on the query-block.

Permissions

Must have SELECT permission for each query-block.

Side effects

None.

See also
Standards and compatibility
Example

List all distinct surnames of employees and customers.

SELECT Surname
FROM Employees
UNION
SELECT Surname
FROM Customers;