Construct Union Queries in SQL

A union^[1] query combines the results of multiple queries into a single result set. Union queries are usually needed when the result you want cannot be obtained from a single query or a subquery, but the results still need to end up in one combined result set. They are simple to construct: just place the keyword UNION between two or more SELECT statements.

A few rules apply to every union query:

1. Each SELECT statement involved must return the same number of columns, in the same order.
2. Every column in a given position across all the SELECT statements must either share the same datatype, or a datatype SQL Server can implicitly convert. An integer and a money datatype can be converted; an integer and a VARBINARY(MAX) cannot, since they represent fundamentally different kinds of data.
3. The column names in the result set come from the first SELECT statement only; later statements' column names are ignored even if they differ.
4. ORDER BY can only appear once, at the very end of the entire union, sorting the final combined result. It cannot follow an individual SELECT statement partway through the union.

By default, UNION removes duplicate rows from the combined result set. If you want to keep duplicates instead, use UNION ALL, which is also generally faster, since SQL Server does not have to do the extra work of comparing rows to find and remove duplicates.

Consider two tables, Employees_US and Employees_Canada:

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 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');

A union query combines the employee information from both tables into a single result set:

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

This returns all four employees, with no duplicates to remove in this case:

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

Using UNION ALL instead would return the same four rows here, since there are no duplicates between the two tables to begin with; the difference only shows up when the same row actually appears in both source queries.

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

The example above combines two tables with identical structure, which keeps the rules simple but is not the most common real-world use of UNION. A more realistic case is combining data from genuinely different kinds of records that need to be compared side by side. In the example below, a union query collects pay information for both contractors and employees, identifying everyone 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

The second SELECT statement includes an arithmetic expression because contractors are typically paid hourly, while employees are paid an annual salary. Multiplying HourlyRate by 2000, roughly the number of work hours in a year, puts both pay structures on the same footing so they can be compared.

Notice that the first SELECT statement aliases the EmployeeID column as ID, using the aliasing technique covered in the previous lesson. This matters because of rule 3 above: the result set's column name comes from the first SELECT statement, and the result set is going to contain both employee and contractor records. Naming that column EmployeeID would be misleading once contractor IDs start showing up in it, so the alias ID is used instead, a name general enough to accommodate both.

The result of this query is a single list of IDs, drawn from two structurally different tables, for everyone earning more than $100,000 a year.

A short recap before the next lesson:

• A union query combines the results of two or more SELECT statements into one result set, using the keyword UNION between them.
• UNION removes duplicate rows by default; UNION ALL keeps every row, duplicates included, and is generally faster since it skips the duplicate-removal work.
• Every SELECT statement in the union must return the same number of columns, in compatible datatypes, and in the same order; the result set's column names always come from the first SELECT statement.
• A union query is just as useful for combining structurally different sources, such as employees and contractors, as it is for combining identically structured tables, provided the columns being compared line up in type and position.

In the next lesson, you will learn about additional keywords you can use in your queries.

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