ACC: Sort Order of Union Queries Affected by ALL Predicate (117164)


The information in this article applies to:

  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97
This article was previously published under Q117164
Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY

The data from a union query appears to be sorted automatically according to values in the first column selected even when the query does not have an explicit ORDER BY clause. This happens because, by default, union queries do not return duplicate records; they perform an implicit DISTINCT. To determine distinct rows of data, a union query sorts the data.

Duplicate records are not returned unless UNION ALL is used. When the ALL predicate is used, the union query's rows are returned unsorted unless an ORDER BY clause in included in the last SELECT statement.

MORE INFORMATION

If you want to see the data from all the SELECT statements in the UNION query including duplicates, keep the following tips in mind when you are designing your queries:
  • If any SELECT statement from the second through last does not have the ALL predicate with its UNION clause, the records will be sorted by default.
  • Any SELECT statement that has the ALL predicate and is not followed by another SELECT statement omitting the ALL predicate will have its rows appended to the records returned by the preceding SELECT statement, instead of sorted within the records of the preceding SELECT statement.
The following example demonstrates how to create a union query that returns a sorted recordset, based on the first tip above:
  1. Start Microsoft Access and open the sample database Northwind.mdb (or NWIND.MDB in version 2.0).
  2. Create a new query that is not based on any table.
  3. On the Query menu, click SQL Specific, and then click Union.
  4. Enter the following SQL statement in the Union Query window.

    NOTE: The blank lines in the following sample SQL statement have no effect on the query itself, but are meant to improve readability. Similarly, the column for the constants Employees1, Employees2, and Customers is included to identify each record's source. The Customers SELECT statement is restricted to contact names beginning with "K" in order to limit the number of records returned.

    SELECT [FirstName] & " " & [LastName] as Fullname, "Employees1" FROM Employees

    UNION ALL SELECT [FirstName] & " " & [LastName] , "Employees2" FROM Employees

    UNION SELECT [ContactName], "Customers" FROM Customers WHERE [ContactName] LIKE "K*";

    (NOTE: In Microsoft Access 2.0, [FirstName], [LastName], and [ContactName] should all be typed as two words, as follows: [First Name], [Last Name], [Contact Name].)
  5. On the Query menu, click Run.

    NOTE: The rows from all three SELECT statements are sorted in a single alphabetical sequence by Fullname. In this example, the SELECT statement without an ALL predicate in the UNION clause happens to be the last one. Notice also that duplicates are retained in these query results.
The next example returns an unsorted recordset, based on the second tip above:
  1. Reverse the second and third SELECT statements in the above example so that the SQL statement looks like the following statement.

    NOTE: The semicolon (;) should only appear at the end of the entire statement.

    SELECT [FirstName] & " " & [LastName] as Fullname, "Employees1" FROM Employees

    UNION SELECT [ContactName], "Customers" FROM Customers WHERE [ContactName] LIKE "K*"

    UNION ALL SELECT [FirstName] & " " & [LastName] , "Employees2" FROM Employees;
  2. On the Query menu, click Run.

    NOTE: The order of the records is changed. The two Customers rows are sorted within the records from the first SELECT (Employees1), and the rows from the last SELECT (Employees2) are appended, in their own sort order, to the rows of the first two SELECT statements.

REFERENCES

For more information about union queries, search the Help Index for "union queries," or ask the Microsoft Access 97 Office Assistant.
Modification Type:MajorLast Reviewed:5/6/2003
Keywords:kbinfo kbusage KB117164
©2002 Microsoft Corporation. All rights reserved.