6. MICROSOFT ACCESS QUERIES – ADVANCED
6.10: SQL Union and Union All
The Union and Union All operators merge the results of two or more queries that are given as SQL Select statements. With MS Access you must switch to SQL View to use Union/Union All
-
-
- UNION removes duplicates and sorts the results
- UNION ALL returns all values (includes duplicates) without sorting
- The output fields must be identical (number and type) for each SELECT.
-
The syntax for UNION of two Select’s:
|
Union |
Union all |
|
SQL Select Statement1 |
SQL Select Statement1 |
|
UNION |
UNION ALL |
|
SQL Select Statement2 ; |
SQL Select Statement2 ; |
| Figure 6.44: Union | and Union All syntax |
Any number of Select statements can be UNIONed. A requirement for using UNION is that the queries are union-compatible – the queries must retrieve the same number of fields, and fields in the same position across the multiple Select clauses must be of matching types.
Example
Consider the Employee table in the Company database. To list all names (first and last) in a single column construct two queries: one to list the first names of employees and one to list the last names of employees.
These two queries can be combined to produce a single list of names. Now, in SQL view type and run:
|
Union (sorted with no duplicates) |
|
Select firstname from Employee UNION Select lastname from Employee ; |
| Figure 6.45: Union |
Exercises
1) Create a query to produce a list of cities in the Orders database. The Employees table and the Customers tables have a city field.
2) Modify the example in Figure 6.45 so that duplicates are eliminated.
