OPERATION:  UNION
[TABLE] query1
UNION [ALL]
[TABLE] query2
[UNION [ALL]
[TABLE] queryn [ ... ]]
[GROUP BY grouplist, [...]]
 
You use the UNION operation to merge the results from any combination of two or more queries, or SELECT statements, or tables, into a single table. The following example merges two queries that selected jigs and reels respectively from a Tunes table, and tags the second onto the end of the first, thus listing jigs and reels together:
 
TABLE Jigs UNION ALL TABLE Reels;
 
All queries in a UNION operation must request the same number of fields, though they don't have to be of the same size or data type. By default, the UNION operation only returns unique records, but by using the ALL predicate you ensure that all records are returned. This also makes the query run faster.
 
The next example takes certain selected fields (as opposed to table, which takes all of them) from the Customers table and joins them onto the end of the same number of selected fields from the Suppliers table:
 
SELECT Name, City FROM Suppliers
WHERE Country = 'Mexico'
UNION SELECT Name, City FROM Customers
WHERE Country = 'Mexico';
 
You can use an ORDER BY clause at the end of the last query to specify the order of the returned data:
 
SELECT Name, City, 'Supplier' AS Source
FROM Suppliers
WHERE Country = 'Canada'
UNION SELECT Name, City, 'Customer'
FROM Customers
WHERE Country = 'Canada'
ORDER BY City, Source;

Copyright 1999-2001 by Infinite Software Solutions, Inc. All rights reserved.
Trademark Information