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 2000 by Infinite Software Solutions, Inc.
Trademark Information
|