Progress
SQL-89
Guide and Reference


UNION Statement

Combines two or more SELECT statements to create a complex query.

SYNTAX
{ SELECT-statement | ( subunion ) }
  { UNION [ ALL ] { SELECT-statement | ( subunion ) } } ...
  [ ORDERED BY sort-criteria ] 

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.

SYNTAX
    SELECT-statement UNION [ ALL ] SELECT-statement 

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.

SYNTAX
{ n | expression } [ ASC | DESC ]
  [ , n | , expression [ ASC | DESC ] ... 

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.

EXAMPLES

You 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.

r-union1.p
SELECT Name, State FROM Customer WHERE State = "MA"
 UNION
SELECT Rep-Name, Region FROM Salesrep WHERE Region = "East". 

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.

r-union2.p
SELECT Order-Num, Cust-Num, Order-Date, "Order" FROM Order
      WHERE Order-Date < 2/1/93
UNION ALL
SELECT Order-Num, Cust-Num, Ship-Date, "Ship" FROM Order
      WHERE Ship-Date < 2/1/93
UNION ALL
SELECT Order-Num, Cust-Num, Promise-Date, "Promise" FROM Order
      WHERE Promise-Date < 2/1/93
ORDER BY Order-Num1. 

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

SEE ALSO

SELECT Statement, UPDATE Statement


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095