Combine the results of several select queries by using a union query

When you want to review all of the data that is returned by several similar select queries together, as a combined set, use a union query.

This article shows you how to create a union query from two or more existing select queries and also explains how to write a union query by using Structured Query Language (SQL).

To complete the examples in this article, you should have a basic understanding of how to create and run select queries. For more information about creating select queries, see the See Also section.

In this article


Overview

What does a union query do?

A union query combines the result sets of several similar select queries.

For example, suppose that you have one table that stores information about customers, another table that stores information about suppliers, and no relationship exists between the two tables. Suppose that both tables have fields that store contact information, and you want to look at all of the contact information from both tables at the same time.

You could create a select query (select query: A query that asks a question about the data stored in your tables and returns a result set in the form of a datasheet, without changing the data.) for each table to retrieve only those fields that contain contact information, but the information that is returned would still be in two, separate places. To combine the results of two or more select queries into one result set, you can use a union query.

Union query requirements

  • The select queries that you combine in a union query must have the same number of output fields, in the same order, and with the same or compatible data types. When you run a union query, data from each set of corresponding fields is combined into one output field, so that the query output has the same number of fields as each of the select statements.

     Note   For the purposes of a union query, the Number and Text data types are compatible.

  • A union query is SQL-specific, and therefore must be written directly in SQL. You switch to SQL View to write SQL-specific queries, including union queries.

    Switch to SQL view

  • Right-click the query in the Navigation Pane, and then click SQL View.

     Tip   Every query can be expressed by using SQL statements. You can always switch to SQL view to see the SQL statement behind a query. It is a good practice to look at your queries in SQL view to familiarize yourself with SQL and to improve your understanding of how queries work. In some cases, you may also find SQL view useful for troubleshooting a query that does not return the results that you expect.

SQL syntax of a union query

In a union query, each select query (also called a select statement) has a SELECT clause and a FROM clause, and can also have a WHERE clause. A SELECT clause lists fields that contain data that you want to retrieve; a FROM clause lists the tables that contain those fields; and, a WHERE clause lists criteria for those fields. You combine the select statements in a union query by using the UNION keyword.

The basic SQL syntax for a union query that combines two select queries is as follows:

SELECT field_1[, field_2,…]
FROM table_1[, table_2,…]
UNION [ALL]
SELECT field_a[, field_b,...]
FROM table_a[, table_b,…];

For example, suppose that you have a table named Products and another table named Services. Both tables have fields that contain the name of the product or service, the price, warranty or guarantee availability, and whether you offer the product or service exclusively. Although the Products table stores warranty information, and the Services table stores guarantee information, the basic information is the same (whether a particular product or service comes with a promise of quality). You can use a union query, such as the following one, to combine the four fields from the two tables:

SELECT name, price, warranty_available, exclusive_offer
FROM Products
UNION ALL
SELECT name, price, guarantee_available, exclusive_offer
FROM Services;

Let us examine the preceding syntax example line by line.

  • SELECT name, price, warranty_available, exclusive_offer    This is a SELECT clause, which you use to start a select query. SELECT is followed by a list of identifiers that indicates the fields from which you want to retrieve data. A SELECT clause must always list at least one field. This SELECT clause lists the field identifiers name, price, warranty_available, and exclusive_offer.
  • FROM Products    This is a FROM clause. A FROM clause follows a SELECT clause, and together they form a basic select statement. FROM is followed by a list of identifiers that indicates which tables contain the fields that are listed in the SELECT clause. A FROM clause must always list at least one table. This FROM clause lists the table identifier Products.
  • UNION ALL    This is the UNION keyword, and the optional ALL keyword. UNION indicates that the results of the SELECT statement that precedes UNION will be combined with the results of the SELECT statement that follows UNION.

When you use the ALL keyword, duplicate rows are not removed from the combined set that is produced by the union. This can dramatically improve the performance of the query, because Access does not have to check the results for duplicate rows. You should use the ALL keyword if any of the following conditions is true:

  • You are certain that the select queries will not produce any duplicate rows.
  • It does not matter if your results have duplicate rows.
  • You want to see duplicate rows.

In this example, we use the ALL keyword because we do not expect duplicate rows to be returned, and there is no reason not to list the duplicates, if they exist.

  • SELECT name, price, guarantee_available, exclusive_offer    This is the second SELECT clause, which starts the second SELECT statement in the union query. When you write a union query, the fields in the SELECT statements must correspond, meaning that the SELECT statements must have the same number of fields, the fields that share common data must appear in the same order in the clause, and the fields that share common data must have the same or compatible data types, as shown in the example. The fields must correspond so that they can be combined in the query output.

 Note   The names of the fields in the output of a union query are drawn from the first SELECT clause. Hence, in the example, data from the field "warranty_available" and from the field "guarantee_available" is named "warranty_available" in the query output.

  • FROM Services    This is the second FROM clause, which completes the second SELECT statement in the union query. Unlike the fields in the SELECT clauses, there are no restrictions on the tables in the FROM clauses of a union query. You can create a union query that uses the same tables in each FROM clause. You can have different numbers of tables in the FROM clauses. In our example, the FROM clauses each has only one table.

Top of Page Top of Page

Create a union query

There are two basic methods to create a union query:

In most cases, you should create the select queries first, and then combine them in a union query. Design view provides an easy-to-use graphical user interface for creating select queries, and you can copy and paste the SQL statements for these queries into a union query.

However, if you are comfortable writing SQL statements, or if you want to gain more SQL writing experience, you may prefer to create the union query directly in SQL view.

What do you want to do?


Create the select queries in Design view first, and then combine them

In this method, you create each select query by using Design view, and then you combine the select queries by using SQL view.

Create each select query in Design view

 Note    Although you can include multiple tables or queries in a select query, this procedure assumes that each of your select queries includes data from only one table.

  1. On the Create tab, in the Queries group, click Query Design.
  2. In the Show Table dialog box, double-click the table that has the fields that you want to include. The table is added to the query design window.
  3. Close the Show Table dialog box.
  4. In the query design window, double-click each of the fields that you want to include.
    As you select fields, make sure that you add the same number of fields, in the same order, that you add to the other select queries. Pay careful attention to the data types of the fields, and make sure that they have compatible data types with fields in the same position in the other queries that you are combining. For example, if your first select query has five fields, the first of which contains date/time data, make sure that each of the other select queries that you are combining also has five fields, the first of which contains date/time data, and so on.
  5. Optionally, add criteria to your fields by typing the appropriate expressions (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.) in the Criteria row of the field grid.
  6. After you have finished adding fields and field criteria, you should run the select query and review its output. On the Design tab, in the Results group, click Run.
  1. Switch the query to Design view.
  2. Save the select query, and leave it open.
  3. Repeat this procedure for each of the select queries that you want to combine.

Combine the select queries in SQL view

  1. On the Create tab, in the Queries group, click Query Design.
    A new query opens in Design view.
  2. Close the Show Table dialog box.
  3. On the Design tab, in the Query group, click Union.

The query design window is hidden, and the SQL view object tab is displayed. At this point, the SQL view object tab is empty.

  1. Click the tab for the first select query that you want to combine in the union query.
  2. On the Home tab, in the Views group, click View, and then click SQL View.
    The SQL statement for the select query is displayed.
  3. Copy the SQL statement for the select query.
  4. Click the tab for the union query that you started to create at step 1 of this procedure.
  5. Paste the SQL statement for the select query into the SQL view object tab of the union query.
  6. Delete the semicolon (;) that is at the end of the select query SQL statement.
  7. Press ENTER to move the cursor down one line, and then type UNION on the new line.
    Optionally, type a space, followed by the ALL keyword, and then press ENTER again.
  8. Click the tab for the next select query that you want to combine in the union query.
  9. Repeat steps 5 through 11 of this procedure until you have copied and pasted all of the SQL statements for the select queries into the SQL view window of the union query. Do not delete the semicolon or type anything following the SQL statement for the last select query.
  10. On the Design tab, in the Results group, click Run.
    The results of your union query appear in Datasheet view (Datasheet view: A view that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.).

Create the union query directly in SQL view

The following procedure guides you through the steps of creating a basic union query in SQL view.

  1. On the Create tab, in the Queries group, click Query Design.
    A new query opens in Design view.
  2. Close the Show Table dialog box.
  3. On the Design tab, in the Query group, click Union.

The query design window is hidden, and the SQL view object tab is displayed. At this point, the SQL view object tab is empty.

  1. In the SQL View object tab, type SELECT, followed by a list of the fields from the first table or set of tables that you want to include in the query. Use commas to separate the field names. After you finish typing the list of field names, press ENTER.
  2. Type FROM, followed by the names of the tables that contain the fields listed in the preceding SELECT clause, and then press ENTER.
  3. To specify a criterion for a field from one of these tables, type WHERE, followed by the field name, a comparison operator — usually, an equal sign (=), and the criterion. You can add additional criteria to the end of the WHERE clause by using the AND and OR keywords. If you have specified criteria, press ENTER.
  4. Type UNION. If you do not want the query to remove duplicate rows from the output, type a space, followed by the ALL keyword, and then press ENTER.
  5. Type SELECT, followed by a list of the fields from the next table or set of tables that you want to include in the query. These fields must correspond to the fields that you included in the first SELECT clause, and these fields must be in the same order. Use commas to separate the field names. After you finish typing the field names, press ENTER.
  6. Type FROM, followed by the name of the next table or set of tables that you want to include in the query, and then press ENTER.
  7. To specify a criterion for a field from one of these tables, type WHERE, followed by the field name, a comparison operator — usually, an equal sign (=), and the criterion. You can add additional criteria to the end of the WHERE clause by using the AND keyword. If you have specified criteria, press ENTER.
  8. To include additional select statements in the union query, repeat steps 7 through 10.
  9. Type a semicolon (;) to indicate the end of your query.
  10. On the Design tab, in the Results group, click Run.
    The results of your union query appear in Datasheet view (Datasheet view: A view that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.).

Top of Page Top of Page

Tips for using union queries

  • If you want to be able to distinguish which rows came from which table, you can add a text string as a field in each of your select statements. For example, if one select statement is retrieving fields from a table named Products, and another select statements is retrieving fields from a table named Services, you can add the string "Product" as a field at the end of the first statement, and "Service" at the end of the second statement. You can also assign a field alias (for example, "type") to the strings by using the AS keyword, as shown in the following example:
SELECT field1, field2, ... "Product" AS type
SELECT field1, field2, ... "Service" AS type

The query output would include a field named "type" that showed whether the row came from the Products table or the Services table.

  • Each UNION keyword combines the SELECT statements that immediately precede and follow it. If you use the ALL keyword with some of the UNION keywords in your query, but not with others, the results will include duplicate rows from the pairs of SELECT statements that are combined by using UNION ALL, but will not include duplicate rows from the SELECT statements that are combined by using UNION without the ALL keyword.
  • Although the number, data types, and order of the fields in the select queries that you combine in a union query must correspond, you can use expressions, such as calculations or subqueries, to make them correspond. For example, you can match a two-character year field with a four-character year field by using the Right function to extract the last two digits of the four-character year.
  • If you want to use the output of a union query to create a new table, you can use the union query as input for a new select query, and then use that select query as the basis of a make-table query (make-table query: A query (SQL statement) that creates a new table and then creates records (rows) in that table by copying records from an existing table or query result.):
    1. Create and save the union query.
    2. On the Create tab, in the Queries group, click Query Design.
    3. In the Show Table dialog box, click the Queries tab.
    4. Double-click your union query, and then close the Show Table dialog box.
      •  Note   If a Security warning appears in the Message Bar, action queries, such as make-table queries, may be disabled. To enable action queries to run, click Enable Content on the Message Bar.

    5. In the query design grid, on your union query's object tab, double-click the asterisk (*) to use all of the union query's fields to make a new table.
    6. On the Design tab, in the Query Type group, click Make Table.
  1. In the Make Table dialog box, type a name for the new table, optionally specify a different database in which to create the table, and then click OK.
  2. On the Design tab, in the Results group, click Run.

To use a union query to perform a full outer join:

  1. Create a query that has a left outer join on the field that you want use for a full outer join.
  2. On the Home tab, in the Views group, click View, and then click SQL View.
  3. Press CTRL+C to copy the SQL code.
  4. Delete the semicolon at the end of the FROM clause, and then press ENTER.
  5. Type UNION, and then press ENTER.

 Note   Do not use the ALL keyword when you use a union query to perform a full outer join.

  1. Press CTRL+V to paste the SQL code that you copied in step 3.
  2. In the code that you pasted, change LEFT JOIN to RIGHT JOIN.
  3. Delete the semicolon at the end of the second FROM clause, and then press ENTER.
  4. Add a WHERE clause that specifies that the value of the join field is NULL in the first table listed in the FROM clause (the left table).

For example, if the FROM clause is:

FROM Products RIGHT JOIN [Order Details] 
 ON Products.ID = [Order Details].[Product ID]

You would add the following WHERE clause:

WHERE Products.ID IS NULL
  1. Type a semicolon (;) at the end of the WHERE clause to indicate the end of the union query.
  2. On the Design tab, in the Results group, click Run.
    The results of your union query appear in Datasheet view (Datasheet view: A view that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.).

Top of Page Top of Page

 
 
Applies to:
Access 2010