[ 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-block : see Common elements in SQL syntax

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

UNION ALL concatenates the results of the two query blocks into a single (larger) result set. Each query block may be nested. UNION DISTINCT eliminates duplicate rows in the final result. Eliminating duplicates requires extra processing, so UNION ALL should be used instead of UNION where possible. UNION DISTINCT is identical to UNION.

The result sets of the two query-blocks must be UNION-compatible; they must each have the same number of items in their respective SELECT lists, and the types of each expression should be comparable. 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 column names displayed are the same column names that are displayed for the first query-block and these names are used to determine the expression names to be matched with the ORDER BY clause. An alternative way of customizing result set column names is to use a common table expression (the WITH clause).

Must have SELECT permission for each query-block.

None.

• EXCEPT statement
• INTERSECT statement
• SELECT statement

List all distinct surnames of employees and customers.

SELECT Surname
FROM Employees
UNION
SELECT Surname
FROM Customers;