| Lesson 6 || Unions |
||Construct Union Queries in SQL |
Construct Union Queries in SQL
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
between two (or more) select statements.
Union query rules
As with most things, there are a couple of rules that apply to using a union query:
SELECT statement involved in the union must return the same number of columns or expressions
- 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.
- Column names from the first
SELECT statement are the ones used in the resultset.
Example of a union query
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
WHERE Salary > 100000
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.
How to Use an alias
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.
A query that combines the results of multiple queries into a single result set.