Union Queries

A union query merges the contents of two tables that have similar field structures. It's useful in situations in which you need to display potentially unrelated records from multiple record sources in a single result set.

Later in this chapter, we describe a way to store old orders in a table of their own, called tblOrderArchive. Because of the way this archiving system is set up, the records are physically located in two separate tables. This approach might be useful for efficiency, as it's usually faster to query a small table than a large one. But at some point you may want to view all the current records and the archived records in a single, unified result set. A union query lets you do so.

Suppose that you need to view the old records in tblOrderArchive in the same result set as the new records in tblOrder. The union query you write to accomplish that is

 SELECT *  FROM tblOrder UNION SELECT * FROM tblOrderArchive 

The result set of this query combines old and new orders in a single result set. The output looks exactly like the original table before it was archived.

By default, union queries don't return duplicate records (that is, records with the exact same field contents from each of the two tables). Displaying duplicate records might be useful if your record archiving system didn't delete records after it copied them to the archive table and you wanted to display some sort of before-and-after comparison.

You can force a union query to intentionally display duplicate records by adding the ALL keyword, however, as in

 SELECT *  FROM tblOrder UNION ALL SELECT * FROM tblOrderArchive