Progress
SQL-89
Guide and Reference


Combining SELECT Statements (UNION)

You can use the UNION statement to combine the results of two or more SELECT statements. This allows you to create more complex queries than with just one SELECT statement.

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

NOTE

You can use the UNION statement to retrieve information from two or more tables and place it into a single retrieval set. The following example combines information from the Customer and Salesrep tables.

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 example combines three queries against the order table.

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

This example 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, 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.


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