MS-Access / Getting Started

---

UNION Query Operator

Produces a result table that contains the rows returned by both the first SELECT statement and the second SELECT statement.

Syntax

<select-statement>
UNION [ALL]
  <select-statement>
[ORDER BY {column-name | column-number
[ASC | DESC]},...]

Notes When you specify ALL, the database returns all rows in both logical tables. When you do not specify ALL, the database eliminates duplicate rows. The tables returned by each <select-statement> must contain an equal number of columns, and each column must have identical attributes.

You must not use the ORDER BY clause in the <select-statements> that are joined by query operators; however, you can include a single ORDER BY clause at the end of a statement that uses one or more query operators. This action will apply the specified order to the result of the entire statement. The database derives the column names of the output from the column names returned by the first <select-statement>. If you want to use column names in the ORDER BY clause, be sure to use names from the first query. You can also use the output column numbers to define ORDER BY criteria.

In a project file, you can include the ORDER BY clause at the end of the statement in a stored procedure, but you cannot include this clause in a view or in-line function. To sort a UNION in a view or in-line function, you must create a view on the query containing the UNION and then sort the view. You can also embed the UNION query in a FROM clause of a query and then sort the result.

You can combine multiple SELECT statements using UNION to obtain complex results. You can also use parentheses to influence the sequence in which the database applies the operators, as shown here:

SELECT...UNION (SELECT...UNION SELECT...)

Example

In a desktop database (.accdb), to create a mailing list for all companies and all contacts, sorted in ascending order by postal code, enter the following (qxmplSortedMailingList):

SELECT tblCompanies.CompanyName, tblCompanies.Address, tblCompanies.City,
  tblCompanies.StateOrProvince, tblCompanies.PostalCode
 FROM tblCompanies
UNION
SELECT [FirstName] & " " & ([MiddleInit]+". ") & [LastName] AS Contact,
  tblContacts.HomeAddress, tblContacts.HomeCity,
  tblContacts.HomeStateOrProvince, tblContacts.HomePostalCode
 FROM tblContacts
ORDER BY 5;

Note If you decide to use column names in the ORDER BY clause of a UNION query, the database derives the column names from the names returned by the first query. In this example, you could change the ORDER BY clause to read ORDER BY PostalCode.

To create the same mailing list in a view or in-line function in an SQL Server database, enter the following:

SELECT TOP 100 PERCENT CompanyName, Address, City,
  StateOrProvince, PostalCode
FROM
(SELECT tblCompanies.CompanyName, tblCompanies.Address, tblCompanies.City,
  tblCompanies.StateOrProvince, tblCompanies.PostalCode
 FROM tblCompanies
UNION
SELECT tblContacts.FirstName + ' ' +
  IsNull(tblContacts.MiddleInit + '. ', '') +
  tblContacts.LastName AS Contact,
  tblContacts.HomeAddress, tblContacts.HomeCity,
  tblContacts.HomeStateOrProvince, tblContacts.HomePostalCode
 FROM tblContacts) AS U
ORDER BY 5;

Notice that you must UNION the rows first and then select and sort them all. See also ORDER BY Clause and SELECT Statement in this tutorial.