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] queryn [ ... ]]

The UNION operation has these parts:

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 statement because they are ignored in any others. In the ORDER BY clause, refer to fields by what they are called in the first SELECT statement.

Notes

You can use a GROUP BY 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.

This example calls the EnumFields procedure, which you can find in the SELECT statement example.

Sub UnionX()

    Dim dbs As Database, rst As Recordset

    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")

    ' Retrieve the names and cities of all suppliers 
    ' and customers in Brazil.
    Set rst = dbs.OpenRecordset("SELECT CompanyName," _
        & " City FROM Suppliers" _
        & " WHERE Country = 'Brazil' UNION" _
        & " SELECT CompanyName, City FROM Customers" _
        & " WHERE Country = 'Brazil';")

    ' Populate the Recordset.
    rst.MoveLast
    
    ' Call EnumFields to print the contents of the 
    ' Recordset. Pass the Recordset object and desired
    ' field width.
    EnumFields rst, 12

    dbs.Close

End Sub

Example (Microsoft Access)

To try the following examples in Microsoft Access, first create a new query in the Northwind sample database. Close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste an individual example into the SQL window, and run the query.

The following 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';

The next example retrieves the names and cities of all suppliers and customers located in Brazil:

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;

The following 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;