Union Query: Part 2

More Union Queries
Last time in Union Queries: Part 1, I discussed simple Union queries and some of their uses. This time I want to talk about some advanced topics, starting with Union All.

Union Vs. Union All

As you remember from last time, a Union query takes the result of one query and "appends" it to another. At the same time, it removes duplicates and orders the records on the first field in the field list.

The Union All does the same thing as the Union, but without removing the duplicates or sorting the resultset, so:

SELECT * FROM TableA
UNION ALL
SELECT * FROM TableB

Would return:

CustomerID CreditLimit
---------- -----------
1001       $500
1010       $350
1017       $375
1020       $425
1017       $375
1008       $600

The order is not determined by the base tables. The result has no order, so the records could be returned in any order.

Sorting Union Queries

So what if you want to sort your Union All or want a different sort order for your Union? Like any Select query, you can add an Order By clause to the end.

SELECT CustomerID, CreditLimit
FROM TableA
UNION ALL
SELECT CustomerID, CreditLimit
FROM TableB
ORDER BY CustomerID;

Will return:

CustomerID CreditLimit
---------- -----------
1001       $500
1008       $600
1010       $350
1020       $425
1017       $375
1017       $375

The Order By applies to the entire result. If the column names are different between queries (which, you'll remember, I said was allowable), the column names from the *first* query must be used. Order By clauses in any of the other component queries will be ignored. Only the last one will be applied.

Restricting Rows

Like any other Select query, you can restrict rows from the resultset with a Where clause. Unlike the Order By, however, each component query can have its own criteria. If you put a Where clause on the last query, it will apply to ONLY the results of the last query. So this:

SELECT CustomerID, CreditLimit
FROM TableA
UNION
SELECT CustomerID, CreditLimit
FROM TableB
WHERE CreditLimit > 500

Produces this:

CustomerID CreditLimit
---------- -----------
1001       $500
1008       $600
1010       $350
1017       $375

Since 1010 and 1017 are in TableA, they can be less than 500, whereas 1020 is in TableB, so it is restricted. If you want a Where to apply to the entire resultset, you have to put it in each query.

SELECT CustomerID, CreditLimit
FROM TableA
WHERE CreditLimit > 500
UNION
SELECT CustomerID, CreditLimit
FROM TableB
WHERE CreditLimit > 500

Tables With Unequal Numbers Of Columns

As I said, the individual queries don't have to have the same column names, nor do they have to be the same datatype, but the *number* of columns in the field list must be equal.

What if your tables have unequal columns? In that case, you need to create false columns with an alias.

So suppose you have two tables which hold much the same data, but have slightly different structures.*

TABLE A
CustomerID CreditLimit
---------- -----------
1001       $500
1010       $350
1017       $375

TABLE B
CustomerNum Credit Active
---------- ------ ------
1008       $600   Y
1017       $375   Y
1020       $425   Y

*Please Note: I am not advocating this as a good database design. In a properly designed database, you would never have two tables which stored the same type of data. However, you may find yourself in this situation if you are fixing a poorly designed database or if you are merging separate databases.

If I don't know the value of "Active" for TableA, I can leave the value NULL.

SELECT CustomerID, CreditLimit, NULL AS Active
FROM TableA
UNION
SELECT CustomerNum, Credit, Active
FROM TableB;

CustomerID CreditLimit Active
---------- ----------- ------
1001       $500
1008       $600        Y
1010       $350
1017       $375        Y
1020       $425        Y

However, if I assume all of the customers in TableA are active

SELECT CustomerID, CreditLimit, "Y" AS Active
FROM TableA
UNION
SELECT CustomerNum, Credit, Active
FROM TableB;

CustomerID CreditLimit Active
---------- ----------- ------
1001       $500        Y
1008       $600        Y
1010       $350        Y
1017       $375        Y
1020       $425        Y

Because Union Queries cannot be viewed in the Query Builder, they are often over looked by Access novices, but they are a powerful tool to have in your SQL arsenal.

.

Union Query: Part 1

Simple Union Query

Introduction

In Microsoft Access, a Union Query is an SQL Specific query, which means it can only be written in SQL. It cannot be created or edited in the Access Query Builder. Novices often confuse UNIONS with JOINS.

Joins (see What is a Join: Part 1 and subsequent posts) combine two (or more) tables row-wise, that is, the results of matching information will be displayed on a single row.

Unions, on the other hand, combine tables column-wise, that is, the results of one SQL statement will be "appended" to the results of another as additional rows. In essence, a Union sticks the results of one query on to the bottom of another.

The structure of a Union Query is very simple. It is two or more queries with the UNION keyword in between. For instance:

SELECT Field1, Field2 FROM Table1
UNION
SELECT FieldA, FieldB FROM Table2

As you can see from the example, the field names do not need to be the same. The column name in the result will come from the first query. The fields don't even need to be the same datatype. In the query above, Field1 could be an integer, Field2 a date/time, FieldA a text field, and FieldB a currency field.

The only real requirement is that there must be an equal number of columns in each of the component SQL statements. If there are different numbers of fields in the field lists, an error will occur.

One important thing to remember about the Union is that it is non-updateable (for more on this see: (This Recordset Is Not Updateable. Why?). As a result, Unions are useful for displaying data, but not for entering or editing it.

Union Example

One of the most common uses for a Union query is to consolidate tables.
For example, imagine a situation where you have similar datasets from 2 different sources, and you want to consolidate/synchronize/merge them:

TABLE A

CustomerID CreditLimit
---------- -----------
1001       $500
1010       $350
1017       $375

TABLE B

CustomerID CreditLimit
---------- -----------
1008       $600
1017       $375
1020       $425

This query:

SELECT CustomerID, CreditLimit FROM TableA
UNION
SELECT CustomerID, CreditLimit FROM TableB

Will produce this result:

CustomerID CreditLimit
---------- -----------
1001       $500
1008       $600
1010       $350
1017       $375
1020       $425

The Union combines the two tables, while at the same time removing the duplicate records. The Union also sorts the recordset on the first column, in this case CustomerID.

Since TableA and TableB have the same number of fields, I could also have done this:

SELECT * FROM TableA
UNION
SELECT * FROM TableB

To actually consolidate the tables into a single table, save the query (say, CustomerUnion), then use it as the table in a Make-Table query.

SELECT * INTO TableC FROM CustomerUnion;

Or use the Union Query in the "From" clause of the Make-Table:

SELECT * INTO TableC
FROM
(SELECT * FROM TableA
UNION
SELECT * FROM TableB)
AS CustomerUnion;

(I'll discuss using a query in the From clause in more detail in a later post.)

Other Uses

There are many applications for the Union Query. One is in the Access implementation of a Full Outer Join (which I will address in a later post). But one very common use is in the Row Source of Combo Boxes.

Suppose I have a combo box to filter records on a form by Customer Name.

My Row Source for the combo could look like this:

SELECT CustomerID, CustomerName FROM Customer;

But suppose I want the combo to have an option to choose all records.

I can use a Union query in the Row Source to add artificial records to the drop down list:

SELECT "*" as CustomerID, "All" as CustomerName FROM Customer
UNION
SELECT CustomerID, CustomerName FROM Customer;

This will produce a result of

CustomerID CustomerName
---------- -----------
*          All
1010       Carlson
1017       Smith

With CustomerID as the bound column in the combo box, I can use a query like:

SELECT * FROM TableA
WHERE CustomerID Like '" & Combo1 & "'"

to filter my form.

Next time, I'll look at some advanced topics with Union Queries.

.