UNION Operation

Description

Creates a union query, which combines the results of two or more independent queries or tables.

Syntax

[TABLE] query1 UNION [ALL] [TABLE] query2
[UNION [ALL] [TABLE] query1–n [ ... ]]

The UNION operation has this part.

Part	Description

query1–n	A SELECT statement, the name of a stored query, or the name of a stored table preceded by the TABLE keyword.

Remarks

You can merge the results of two or more queries, tables, and SELECT statements, in any combination, in a single UNION operation. The following example merges an existing table named New Accounts and a SELECT statement:

TABLE [New Accounts] UNION ALL
SELECT *
FROM Customers
WHERE OrderAmount > 1000;

By default, no duplicate records are returned when you use a UNION operation; however, you can include the ALL predicate to ensure that all records are returned. This also makes the query run faster.

All queries in a UNION operation must request the same number of fields; however, the fields don't have to be of the same size or data type.

Use aliases only in the first SELECT clause because they are ignored in any others. In the ORDER BY clause, refer to fields by what they are called in the first SELECT clause.

Notes

You can use a GROUP BY and/or HAVING clause in each query argument to group the returned data.
You can use an ORDER BY clause at the end of the last query argument to display the returned data in a specified order.

See Also

ALL, DISTINCT, DISTINCTROW, TOP Predicates; GROUP BY Clause; HAVING Clause; INNER JOIN Operation; LEFT JOIN, RIGHT JOIN Operations; ORDER BY Clause; SELECT Statement; SQL Subqueries; WHERE Clause.

Specifics (Microsoft Access)

In Microsoft Access, the arguments for the UNION operation (query1, query2,...queryn) can be a SELECT statement, the name of a stored Microsoft Access query, or the name of a stored Microsoft Access table preceded by the TABLE reserved word.

You can view a union query only in SQL view, not in the query design grid.

Example

This example retrieves the names and cities of all suppliers and customers in Brazil.

SELECT CompanyName, City FROM Suppliers WHERE Country = "Brazil" UNION SELECT CompanyName, City FROM Customers WHERE Country = "Brazil";

This example retrieves the names and cities of all suppliers and customers located in Brazil, using constants, an alias, and ordering by an alias.

SELECT CompanyName, City, "Supplier" AS Source FROM Suppliers 
WHERE Country = "Brazil" UNION SELECT CompanyName, City, "Customer" 
FROM Customers WHERE Country = "Brazil" ORDER BY City, Source;

This example retrieves the names and cities of all suppliers and customers in Brazil and the last names and cities of all employees in South America.

SELECT CompanyName, City FROM Suppliers WHERE Country = "Brazil" UNION SELECT CompanyName, City FROM Customers WHERE Country = "Brazil" UNION SELECT LastName, City FROM Employees WHERE Region = "South America";

This example retrieves the names and IDs of all suppliers and customers. This union assumes that there are the same number of columns in each table.

TABLE Customers UNION TABLE Suppliers;