Construct Union Queries in SQL

A union query combines the results, or union, of multiple queries into a single result set. 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 result set.
Here’s an example of a UNION query in SQL Server 2022 that combines results from two tables:

1. Employees_US and
2. Employees_Canada.

Tables Structure

CREATE TABLE Employees_US (
    EmployeeID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Country NVARCHAR(50)
);

CREATE TABLE Employees_Canada (
    EmployeeID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Country NVARCHAR(50)
);

-- Insert sample data
INSERT INTO Employees_US VALUES (1, 'John', 'Doe', 'USA');
INSERT INTO Employees_US VALUES (2, 'Jane', 'Smith', 'USA');

INSERT INTO Employees_Canada VALUES (3, 'Alice', 'Brown', 'Canada');
INSERT INTO Employees_Canada VALUES (4, 'Bob', 'Johnson', 'Canada');

Union Query Example
This query combines the employee information from both `Employees_US` and `Employees_Canada` into a single result set:

SELECT EmployeeID, FirstName, LastName, Country
FROM Employees_US
UNION
SELECT EmployeeID, FirstName, LastName, Country
FROM Employees_Canada;

Explanation

1. UNION vs UNION ALL:
   • UNION removes duplicate rows from the result set.
   • If you want to include duplicate rows, use UNION ALL.
2. Output of the Query:
   • The combined results of the Employees_US and Employees_Canada tables, without duplicates:

     EmployeeID  FirstName   LastName    Country
     ----------------------------------------
     1           John        Doe         USA
     2           Jane        Smith       USA
     3           Alice       Brown       Canada
     4           Bob         Johnson     Canada
             

Example with `UNION ALL`
If you want to include all rows even if they are duplicates:

SELECT EmployeeID, FirstName, LastName, Country
FROM Employees_US
UNION ALL
SELECT EmployeeID, FirstName, LastName, Country
FROM Employees_Canada;

This will return all rows, including duplicates if they exist.

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.
Union Query Rules
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.