UNION Queries

You often have to treat the results of one or more queries, or the contents of one or more tables, as if they were in the same table. You may want to create a list of names and addresses that combines entries from both the Customers and the Suppliers tables. Microsoft Jet supports the UNION statement, which combines the results of two or more SELECT queries.

For example, the combined Customer and Supplier list can be created with the following query:

SELECT 
	Customers.CompanyName, 
	Customers.Address, 
	Customers.City, 
	Customers.Region, 
	Customers.PostalCode
FROM Customers
UNION
SELECT 
	Suppliers.CompanyName, 
	Suppliers.Address, 
	Suppliers.City, 
	Suppliers.Region, 
	Suppliers.PostalCode
FROM Suppliers;

The individual SELECT queries are combined by the UNION statement. Each SELECT statement can be as complex as necessary, and can include multiple-table joins. Each SELECT statement can also use GROUP BY to sum values. To specify the order in which the results of all the individual SELECT statements are returned, you can add an optional ORDER BY clause after the last SELECT statement.

The number of fields in each SELECT statement must match, but the names of the fields can differ. The data types of the matching fields don’t have to match exactly. For example, an integer field can be united with a floating-point field.

When the output of the entire UNION query is referred to, the field names for the first SELECT statement are used.

Usually, Microsoft Jet hides records that contain entirely duplicated data in a UNION query. If you want to include duplicate records in the output, use the UNION ALL clause instead of UNION.

If you’re joining two tables or queries that have the same structure, you can use a special form of the UNION statement with which you can specify the tables you want to combine, rather than listing the fields you want to select. For example, instead of listing the fields as in the earlier example that combined values from the Customers and Suppliers tables, you could create one query called SelectCustomerNameAndAddress

SELECT 
	Customers.CompanyName, 
	Customers.Address, 
	Customers.City, 
	Customers.Region, 
	Customers.PostalCode
FROM Customers;

and another called SelectSupplierNameAndAddress:

SELECT 
	Suppliers.CompanyName, 
	Suppliers.Address, 
	Suppliers.City, 
	Suppliers.Region, 
	Suppliers.PostalCode
FROM Suppliers;

The following UNION query combines the two queries above without explicitly specifying the names of the fields:

TABLE SelectSupplierNameAndAddress
UNION
TABLE SelectCustomerNameAndAddress;

You can’t explicitly include Memo or Long Binary (OLE Object) fields in the output SELECT statement of a UNION query. For example, the following query results in an error:

SELECT Table1.Memo1
FROM Table1
UNION 
SELECT Table2.Memo2
FROM Table2;

If it’s sufficient for your purposes to include only the first 255 characters of the Memo field in the output, one alternative is to select an expression based on the Memo field:

SELECT Left(Table1.Memo1,255) AS FirstPartOfMemo
FROM Table1
UNION SELECT Left(Table2.Memo2,255) AS FirstPartOfMemo
FROM Table2;

Note The results of a UNION query are never updatable.