SQL UNION operator

Union operators are used to combine the result sets of two or more SELECT statements without returning any duplicate rows.

  • Each SELECT statement in union must have the same number of columns
  • These columns must also have similar data types
  • The columns in each SELECT statement must also be in the same order
  • Each SELECT statement must have the same number of column expressions
  • However, the length of each SELECT statement does not have to be the same

SQL UNION syntax 1 

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

Note: By default, the UNION operator selects a different value. If duplicate values are allowed, use UNION ALL.

SQL UNION syntax 2 

SELECT column_name(s) FROM table1
[WHERE condition]

UNION
SELECT column_name(s) FROM table2
[WHERE condition];

A given condition can be any given expression based on your needs.

SQL UNION ALL syntax 1

The UNION All operator is used to combine the results of two SELECT statements, including duplicate lines.

The same rules that apply to the UNION clause will apply to the UNION All operator. 

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

Note: The column name in the UNION result set is always equal to the column name in the first SELECT statement in UNION.

SQL UNION ALL syntax 2 

SELECT column_name(s) FROM table1
[WHERE condition]
UNION ALL
SELECT column_name(s) FROM table2
[WHERE condition];


Demonstrate the database

In this tutorial, we'll use the famous Northwind sample database.

Here's the data 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

Data selected from the Suppliers table:

SupplierID SupplierName ContactName Address City PostalCode Country
1 Exotic Liquid Charlotte Cooper 49 Gilbert St. Londona 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 instance

The following SQL statement selects all the different cities (with only different values) from the "Customers" and "Suppliers" tables:

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

Note: Union cannot be used to list all cities in both tables. I f some customers and suppliers are from the same city, each city will be included in only one list. U NION will only select different values. Use UNION ALL to select a duplicate value!


SQL UNION ALL instance

The following SQL statement uses UNION ALL to select all cities (also duplicate values) from the "Customers" and "Suppliers" tables:

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

SQL UNION ALL with WHERE

The following SQL statement uses UNIONALL to select all German cities (also duplicate values) from the "Customers" and "Suppliers" tables:

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