The UNION operation: combining queries

User's Guide
PART 1. Working with Databases
CHAPTER 5. Summarizing, Grouping, and Sorting Query Results

The UNION operation: combining queries

The UNION operator combines the results of two or more queries into a single result set.

By default, the UNION operator removes duplicate rows from the result set. If you use the ALL option, duplicates are not removed. The columns in the result set have the same names as the columns in the first table referenced. Any number of union operators may be used. For example:

x UNION y UNION z

By default, a statement containing multiple UNION operators is evaluated from left to right. Parentheses may be used to specify the order of evaluation.

For example, the following two expressions are not equivalent:

x UNION ALL (y UNION z)
(x UNION ALL y) UNION z

In the first expression, duplicates are eliminated in the UNION between y and z. In the UNION between that set and x, duplicates are not eliminated. In the second expression, duplicates are included in the union between x and y, but are then eliminated in the subsequent union with z; ALL does not affect the result of this statement.

Guidelines for UNION queries

The following are guidelines to observe when you use union statements:

Same number of items in the select list All select lists in the union statement must have the same number of expressions (such as column names, arithmetic expressions, and aggregate functions). The following statement is invalid because the first select list is longer than the second:
```
-- This is an example of an invalid statement
SELECT stor_id, city, state
FROM stores 
UNION 
SELECT stor_id, city
FROM stores_east
```
Data types must match Corresponding columns in all tables, or any subset of columns used in the individual queries, must be of the same data type, or an implicit data conversion must be possible between the two data types, or an explicit conversion should be supplied.

For example, a UNION is not possible between a column of the CHAR data type and one of the INT data type, unless an explicit conversion is supplied. However, a union is possible between a column of the MONEY data type and one of the INT data type.
Column ordering You must place corresponding columns in the individual queries of a UNION statement in the same order, because UNION compares the columns one to one in the order given in the individual queries.
Multiple unions You can string several UNION operations together, as in the following example:
```
SELECT city AS Cities
FROM contact
UNION 
SELECT city
FROM customer
UNION 
SELECT city
FROM employee
```
Only one ORDER BY clause is permitted, at the end of the statement.
Column headings The column names in the table resulting from a UNION are taken from the first individual query in the statement. If you want to define a new column heading for the result set, you must do so in the first query, as in the following example:
```
SELECT city AS Cities
FROM contact
UNION 
SELECT city
FROM customer
```
In the following query, the column heading remains as city.
```
SELECT city
FROM contact
UNION 
SELECT city AS Cities
FROM customer
```
You can use a single ORDER BY clause at the end of the list of queries, but you must use integers rather than column names in this clause, as in the following example:
```
SELECT Cities = city
FROM contact
UNION 
SELECT city
FROM customer
ORDER BY 1 
```