select-statement-without-ordering
[ 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.

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

• SELECT statement [UltraLite] [UltraLiteJ]

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

SELECT Surname FROM Employees
UNION
SELECT Surname FROM Customers;