SQL UNION and UNION ALL Operators

The UNION and UNION ALL operators in SQL combine the result sets of two or more SELECT statements into a single result set. They're powerful tools for consolidating data from multiple queries.

UNION and UNION ALL: Definition and Usage

Both UNION and UNION ALL require that the SELECT statements being combined have the same number of columns, and that the corresponding columns have compatible data types. The order of columns must also match.

Key Differences

UNION: Removes duplicate rows from the combined result set. This means that only unique rows are included in the final output.
UNION ALL: Includes all rows from the combined result sets, including duplicates. This is generally faster than UNION because it skips the duplicate removal step.

Syntax


-- UNION (removes duplicates)
SELECT column1, column2, ... FROM table1
UNION
SELECT column1, column2, ... FROM table2;

-- UNION ALL (includes duplicates)
SELECT column1, column2, ... FROM table1
UNION ALL
SELECT column1, column2, ... FROM table2;

Example Databases

These examples use simplified versions of the 'Customers' and 'Suppliers' tables:

Customers Table

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

Suppliers Table

SupplierID	SupplierName	ContactName	Address	City	PostalCode	Country
1	Exotic Liquid	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

Examples

UNION (Distinct Cities)

This combines the 'City' column from both tables, removing duplicates.

Syntax


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

Output


City
-------
Ann Arbor    (Example output - order and contents depend on your data)
Berlin
...

UNION ALL (Cities with Duplicates)

This combines cities, keeping duplicates.

Syntax


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

Output


City
-------
Ann Arbor
Ann Arbor
Berlin
...

UNION with WHERE Clause

This shows a UNION with a WHERE clause to filter for German cities.

Syntax


SELECT City, Country FROM Customers
WHERE Country = 'Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country = 'Germany'
ORDER BY City;

Output


City    | Country
-------------
(German cities from Customers and Suppliers, duplicates removed)

UNION ALL with WHERE Clause

Similar to above, but this time keeping duplicate cities.

Syntax


SELECT City, Country FROM Customers
WHERE Country = 'Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country = 'Germany'
ORDER BY City;

Output


City    | Country
-------------
(German cities from Customers and Suppliers, duplicates included)

UNION with Alias

This example combines customers and suppliers, adding a 'Type' column to distinguish them.

Syntax


SELECT 'Customer' AS Type, ContactName, City, Country
FROM Customers
UNION
SELECT 'Supplier', ContactName, City, Country
FROM Suppliers;

Output


Type     | ContactName      | City        | Country
-------------------------------------------------
Customer | Maria Anders      | Berlin      | Germany
Customer | Ana Trujillo      | México D.F. | Mexico
...       | ...               | ...         | ...