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

SQL Anywhere 11.0.1 » UltraLite - Database Management and Reference » UltraLite SQL Reference » UltraLite SQL statements


UltraLite UNION statement

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

[ UNION [ ALL | DISTINCT ] select-statement-without-ordering ]...
[ ORDER BY [ number [ ASC | DESC ] , ... ]

The results of several SELECT statements can be combined into a larger result using UNION. Each SELECT statement must have the same number of expressions in their respective select list, and cannot contain an ORDER BY clause.

The results of UNION ALL are the combined results of the unioned SELECT statements. Specify UNION or UNION DISTINCT to get results without duplicate rows; however, note that removing duplicate rows adds to the total execution time for the statement. Specify UNION ALL to allow duplicate rows.

When attempting to combine corresponding expressions that are of different data types, UltraLite attempts find a data type in which to represent the combined values. If this is not possible, the union operation fails and an error is returned (for example "Cannot convert 'Surname' to a numeric").

The column names displayed in the results are column names (or aliases) used for the first SELECT statement.

The ORDER BY clause uses integers to establish the ordering, where the integer indicates the query expression(s) on which to sort the results.

See also

The following example lists all distinct surnames found in the Employees and Customers tables, combined.

SELECT Surname FROM Employees
SELECT Surname FROM Customers;