#union #query, #microsoft #access, #ms #access, #union, #union #all, #syntax, #query, #queries, #duplicates, #sample, #data
Union vs. Union All Query Syntax
for SQL Server and Microsoft Access
Union Query Overview
The purpose of the SQL UNION and UNIONALL 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
[SQL Statement 1]
[SQL Statement 2]
[GROUP BY. ]
Use Authors table in SQL Server Pubs database or just use a simple table with these values (obviously simplified to just illustrate the point):
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 may include multiple tables with different types of joins and filters. Each of query could reference completely different sets of tables. For instance, you can combine the list of zip codes with customers who purchased a particular product recently 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.
Group By, Having and Order By Clauses
GROUP BY or HAVING clauses can be added in each subquery (SELECT statement).
An ORDER BY clause can be added after the last subquery to sort all the results.
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
SELECT City, State, Zip FROM Authors WHERE IN (‘OR’ ‘TN’)
Temporary Index Issues for UNION Queries
A UNION query, by definition, eliminates all duplicate rows (as opposed to UNIONALL ) 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 built for the queries, a SQL error occurs. 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
Total Visual Agent 2016
Automate Microsoft Access Database Chores!
Total Access Emailer 2016
Send Personalized Emails from Microsoft Access!
Get our Latest News
Source Code Library
125,000+ Lines of
Royalty-Free Module Code
SQL Server Upsizing
Connect with Us
Celebrating our 30th Year of Software Excellence
MS Access Products
Access/Office/VBA and VB6 Products