Contents Index TRUNCATE TABLE statement UNLOAD statement

ASA SQL Reference
  SQL Statements

UNION operation


Description 

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

Syntax 

select-statement
  UNION [ ALL | DISTINCT ] select-statement
UNION [ ALL | DISTINCT ] select-statement ] ...
ORDER BY integer [ ASC | DESC ], ... ]

Usage 

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

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. UNION DISTINCT is identical to UNION.

If corresponding items in two select lists have different data types, Adaptive Server Anywhere will choose a data type for the corresponding column in the result and automatically convert the columns in each component SELECT statement appropriately.

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

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

Permissions 

Must have SELECT permission for each of the component SELECT statements.

Side effects 

None.

See also 

SELECT statement

Standards and compatibility 
Example 

List all distinct surnames of employees and customers.

SELECT emp_lname
FROM Employee
UNION
SELECT lname
FROM Customer

Contents Index TRUNCATE TABLE statement UNLOAD statement