sql_union

SQL UNION and UNION ALL Operators: Combining Result Sets

The SQL UNION and UNION ALL operators are used to combine the result sets of two or more SELECT statements into a single result set.

Understanding UNION and UNION ALL

The UNION operator combines result sets and removes duplicate rows. The UNION ALL operator also combines result sets but includes all rows, including duplicates.

Key Rules for Using UNION/UNION ALL:

1. Same Number of Columns: Each SELECT statement within the UNION or UNION ALL must have the same number of columns in its select list.
2. Compatible Data Types: The data types of corresponding columns in each SELECT statement must be compatible (e.g., you can union a VARCHAR with another VARCHAR, or an INT with another INT). The database will try to implicitly convert types if possible, but it’s best practice to ensure they match or are directly compatible.
3. Same Order of Columns: The columns in each SELECT statement must be in the same order.

SQL UNION Syntax

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2
ORDER BY column_name; -- Optional: Applies to the final combined result set

UNION by default selects only distinct values.

SQL UNION ALL Syntax

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2
ORDER BY column_name; -- Optional: Applies to the final combined result set

UNION ALL selects all values, including duplicates. It is generally faster than UNION because it doesn’t perform the extra step of removing duplicates.

Column Names: The column names in the final result set are usually taken from the first SELECT statement.

Demonstration Database: Northwind

We’ll use data from the “Customers” and “Suppliers” tables in the Northwind database.

Selection from “Customers”:

CustomerID
CustomerName
ContactName
Address
City
PostalCode
Country


1
Alfreds Futterkiste
Maria Anders
Obere Str. 57
Berlin
12209
Germany


2
Ana Trujillo Emparedados y helados
Ana Trujillo
Avda. de la Constitución 2222
México D.F.
05021
Mexico


3
Antonio Moreno Taquería
Antonio Moreno
Mataderos 2312
México D.F.
05023
Mexico

Selection from “Suppliers”:

SupplierID
SupplierName
ContactName
Address
City
PostalCode
Country


1
Exotic Liquids
Charlotte Cooper
49 Gilbert St.
London
EC1 4SD
UK


2
New Orleans Cajun Delights
Shelley Burke
P.O. Box 78934
New Orleans
70117
USA


3
Grandma Kelly's Homestead
Regina Murphy
707 Oxford Rd.
Ann Arbor
48104
USA

SQL UNION Example: Unique Cities

The following SQL statement selects all unique cities from both the “Customers” and “Suppliers” tables, ordered alphabetically.

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;

If a city appears in both tables (e.g., London), it will only be listed once in the result.

SQL UNION ALL Example: All Cities (Including Duplicates)

The following SQL statement uses UNION ALL to select all cities from both tables. If a city exists in both, or multiple times in one table (if we weren’t selecting distinct cities from each first), it would appear multiple times in the result.

SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;

SQL UNION ALL With WHERE Example: Combining Filtered Data

This example selects all German cities from “Customers” and all USA cities from “Suppliers”, combining them into one list. It also selects the country to differentiate.

SELECT City, Country, 'Customer' AS EntityType FROM Customers
WHERE Country = 'Germany'
UNION ALL
SELECT City, Country, 'Supplier' AS EntityType FROM Suppliers
WHERE Country = 'USA'
ORDER BY Country, City;

Practical Application: UNION and UNION ALL are useful for combining data from similar tables (e.g., ActiveEmployees and ArchivedEmployees), or for creating consolidated reports from different data sources that share a common structure.