Queries using set
operations
A UNION query brings
together in one result set data from two or more unrelated
tables or queries that have identical structure (same number of columns
with
same data types occurring in the same order; not necessarily same
column
headers).
A
UNION
query cannot be built in the graphical query interface in Access.
Example
Customers and Suppliers
by City:
SELECT City,
CompanyName, ContactName, "Customers" AS [Relationship]
FROM Customers
UNION SELECT
City, CompanyName, ContactName, "Suppliers"
FROM Suppliers
ORDER BY City,
CompanyName;
- Variable number of
SELECT statements linked by the key
word
- Columns must be named
(important if they were calculated)
- Optional additional
column or columns to add information
or to make table structures match
- No duplicates unless
UNION ALL is specified (not obvious
from this example)
- If the result set is to
be sorted, only one ORDER BY
clause at the end