Construct Union Queries in SQL

A union^[1] query performs the action that the name suggests. It combines the results, or union, of multiple queries into a single resultset. Union queries are usually needed when the result of a query cannot be obtained by using a single query or a subquery, but the results need to be contained within a single resultset. Union queries are extremely simple to implement, as they use only the keyword UNION between two (or more) select statements.

As with most things, there are a couple of rules that apply to using a union query:

1. Each SELECT statement involved in the union must return the same number of columns or expressions
   
2. Every column that is returned as a single resultset must either have the same datatype or a datatype that SQL Server 7 can convert. For example, an integer and money datatype can be converted, but an integer and an image cannot. They are two totally different types of data.
3. Column names from the first SELECT statement are the ones used in the resultset.

In the example below, a union query is used to collect salary information for contractors and employees, and identify who is being paid over $100,000 per year.

SELECT EmployeeID AS ID
FROM Employees
WHERE Salary > 100000
UNION
SELECT ContractorID
FROM Contractors
WHERE HourlyRate*2000 > 100000

Note that the second SELECT statement includes an arithmetic expression because contractors are usually paid hourly, while employees are paid yearly. By multiplying HourlyRate by 2000, which is the approximate number of work hours in a year, the two types of wages can be compared.

Also note that the first SELECT statement includes an alias for the EmployeeID column. This is because the resultset will contain both Employees and Contractors. We know from the rules above that the first select statement in a union query determines the column name in the resultset. We do not want SQL Server to name this column EmployeeID, because it will contain both employees and contractors. The alias renames the column “ID”, accommodating both data types
The results of this sample UNION query is a list of IDs that are receiving more than $100000 per year.
In the next lesson, you will learn about additional keywords that you can use in your queries.

[1]Union: A query that combines the results of multiple queries into a single result set.