FMS Home FMS Software Development Team Blog FMS Facebook Page FMS Twitter
Jump: Search:
 

News Features

Total Access Statistics 2013 ships! Add advanced data and statistical analysis to Microsoft Access!

Microsoft Access Statistical Analysis


Total Access Emailer 2013 ships! Easily send personalized emails with Access data to everyone!

Microsoft Access Email Blaster


Get our Latest News

Email NewsletterLatest Newsletter (Sign up)

FMS Development Team BlogBlog with us and subscribe to our RSS feed.

FMS Facebook Page Like our Facebook Page

FMS Twitter Follow us on Twitter

Union vs. Union All Query Syntax

Microsoft AccessUnion vs. Union All SQL SyntaxMicrosoft SQL Serverfor SQL Server and Microsoft Access

Provided by: FMS Development Team

Union Query Overview

The purpose of the SQL UNION and UNION ALL commands are to combine the results of two or more queries into a single result set consisting of all the rows belonging to all the queries in the union. The question becomes whether or not to use the ALL syntax.

The main difference between UNION ALL and UNION is that, UNION only selects distinct values, while UNION ALL selects all values (including duplicates).

The syntax for UNION {ALL} is as follows:

[SQL Statement 1]
UNION {ALL}
[SQL Statement 2]
[GROUP BY ...]

Sample Data

Use Authors table in SQL Server Pubs database or just use a simple table with these values (obviously simplified to just illustrate the point):

City State Zip
Nashville TN 37215
Lawrence KS 66044
Corvallis OR 97330

UNION ALL Example

This SQL statement combines two queries to retrieve records based on states. The two queries happen to both get records from Tennessee ('TN'):

SELECT City, State, Zip FROM Authors WHERE State IN ('KS', 'TN')
UNION ALL
SELECT City, State, Zip FROM Authors WHERE IN ('OR' 'TN')

Result of UNION ALL syntax:

City State Zip
Nashville TN 37215
Lawrence KS 66044
Nashville TN 37215
Corvallis OR 97330

Notice how this displays the two query results in the order they appear from the queries. The first two records come from the first SELECT statement, and the last two records from the second SELECT statement. The TN record appears twice, since both SELECT statements retrieve TN records.

Union Query SQL Example

Using the same SQL statements and combining them with a UNION command:

SELECT City, State, Zip FROM Authors WHERE State IN ('KS', 'TN')
UNION
SELECT City, State, Zip FROM Authors WHERE IN ('OR' 'TN')

Result of UNION Query

City State Zip
Corvallis OR 97330
Lawrence KS 66044
Nashville TN 37215

Notice how the TN record only appears once, even though both SELECT statements retrieve TN records. The UNION syntax automatically eliminates the duplicate records between the two SQL statements and sorts the results. In this example the Corvallis record appears first but is from the second SELECT statement.

A GROUP BY clause can be added at the end to sort the list.

More Advanced Union Queries

The example above is very simple for illustrative purposes, and can obviously be done without using a UNION query. More common uses of UNION queries include:

More than two SELECT statements

You can add SELECT statements with additional UNION syntax. You are not limited to just two.

More complex SELECT statements

Each SELECT statement can include multiple tables with different types of joins and filters. Each query could be referencing completely different sets of tables. For instance, you can combine the list of zip codes with customers who purchased a particular product in the past year with zip codes that have total sales exceeding a certain amount.

What's critical is the output field names are identical for each SELECT statement in the UNION query. The fields don't have to come from the same table, or even same field names since you can use the AS syntax to give the field a different name from its source.

Creating an Artificial Blank Row

You can create a SELECT statement that simulates a blank row, without actually having a blank record in your table. This is commonly used as the rowsource of combo boxes to give people a choice of None (""). For example:

SELECT "" as City, "" as State, "" as Zip FROM Authors
UNION
SELECT City, State, Zip FROM Authors WHERE IN ('OR' 'TN')

City State Zip
     
Corvallis OR 97330
Nashville TN 37215

Here's an additional example in our Microsoft Access Query Tips and Techniques paper.

Temporary Index Issues for UNION Queries

A UNION query, by definition, eliminates all duplicate rows (as opposed to UNION ALL) and is slower. To do this in SQL Server, it must build a temporary index on all the columns returned by both queries. If the index cannot be build for the queries, you will get a SQL error. In this situation, UNION ALL may be the solution if you don't have rights to do this. Alternatively, you may need to use a temporary table with INSERT queries to store the results of each query and use a standard SELECT query to group and get the unique values.

Additional Resource for Union Queries

Feedback

Contact Us  l   Web questions: Webmaster   l   Copyright FMS, Inc., Vienna, Virginia
Celebrating our 27th Year of Software Excellence