Progress
SQL-89
Guide and Reference
UNION Statement
Combines two or more SELECT statements to create a complex query.
SELECT-statement
An SQL SELECT statement that retrieves one or more rows from one or more tables. The SELECT statements within a UNION cannot use the INTO option. Any formatting options (such as LABEL or FORMAT) you specify in the SELECT statement are ignored. You cannot use the frame options, STREAM or EXPORT, in a SELECT statement within a UNION statement. For more information, see the SELECT StatementSELECT_Statement reference entry.
subunion
Two SQL SELECT statements joined by the UNION keyword. A subunion might optionally use the ALL keyword. The syntax of subunion is as follows.
ALL
Specifies that the union of two SELECT statements includes any duplicate rows. If you do not specify ALL, then only one copy of each row is returned in the union.
ORDER BY sort-criteria
Sorts the query results by the values in one or more columns. The syntax of sort-criteria is as follows.
In this syntax, n represents an integer that specifies the position of a column or expression in the select list. Use this option to sort on arithmetic expressions included in the select list. Each expression represents a column name or other value by which to sort the results. This can be a calculated expression involving one or more table columns.
EXAMPLESYou can use the UNION statement to retrieve information from two or more tables into a single results list. For example, the following procedure takes information from the Customer and Salesrep tables.
You can also use the UNION statement to combine two or more queries into the same table. The following procedure combines three queries into the result table.
This procedure returns information about any orders with any date (order date, ship date, or promise date) before February 1, 1993. Because the ALL keyword combines each of the SELECT statements, if a single order has more than one date before February 1, 1993, then it appears more than once in the result list. The fourth column of output contains a string constant that indicates which type of date is shown in the third column. The result list is sorted by order-num because of the ORDER BY clause.
NOTES
- The columns you specify in each SELECT statement must be compatible. Each SELECT statement must refer to the same number of columns, and the nth column of each list must be assignment-compatible with the nth column of the other lists.
- The columns in the output of a UNION statement take the labels of the field specified in the first SELECT statement.
SEE ALSO
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |