SQL UNION

The SQL UNION Operator

The UNION operator merges the result-sets of multiple SELECT statements, ensuring that:

Each SELECT statement within the UNION has the same number of columns.
The columns share similar data types.
The columns in each SELECT statement are in the same order.

UNION Syntax

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION ALL Syntax

By default, the UNION operator retrieves only unique values. To include duplicate values, utilize UNION ALL:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Keep in mind: Typically, the column names in the result-set match those in the first SELECT statement.

Demo Database

During this tutorial, we’ll be utilizing the widely known Northwind sample database.

Presented below is a segment from the “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

Also provided is a selection from the “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

SQL UNION Example

This SQL statement retrieves the unique cities from both the “Customers” and the “Suppliers” tables.

Example

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

Keep in mind: When customers or suppliers share the same city, each city is listed only once due to the default behavior of UNION, which selects distinct values. To include duplicate values, employ UNION ALL.

SQL UNION ALL Example

This SQL statement retrieves the cities, including duplicate values, from both the “Customers” and the “Suppliers” table:

Example

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

SQL UNION With WHERE

This SQL statement retrieves the unique German cities from both the “Customers” and the “Suppliers” tables.

Example

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

SQL UNION ALL With WHERE

This SQL statement retrieves the German cities, including duplicate values, from both the “Customers” and the “Suppliers” tables.

Example

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

Another UNION Example

The following SQL statement provides a list of all customers and suppliers.

Example

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

Observe the “AS Type” notation above—it serves as an alias. SQL Aliases are employed to assign a temporary name to a table or a column. An alias persists only for the duration of the query. Here, we’ve established a temporary column named “Type,” which indicates whether the contact person is a “Customer” or a “Supplier.”