SQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements.

Each SELECT statement within the UNION must have the same number of columns in the result sets with similar data types.

Syntax

SELECT column1, column2, ...
FROM table1
UNION
SELECT column1, column2, ...
FROM table2;

Example: UNION

SELECT city, country
FROM customers
UNION
SELECT city, country
FROM suppliers
ORDER BY city;

Sample Tables

Consider the following customers table:

| id | city         | country    |
|----|--------------|------------|
| 1  | New York     | USA        |
| 2  | Los Angeles  | USA        |
| 3  | London       | UK         |

And the following suppliers table:

| id | city         | country    |
|----|--------------|------------|
| 1  | San Francisco| USA        |
| 2  | London       | UK         |
| 3  | Berlin       | Germany    |

Result of UNION

| city         | country    |
|--------------|------------|
| Berlin       | Germany    |
| London       | UK         |
| Los Angeles  | USA        |
| New York     | USA        |
| San Francisco| USA        |

All unique cities and countries from both tables are returned and ordered by city.

Example: UNION ALL

The UNION ALL operator is used to combine the result-set of two or more SELECT statements, including duplicate values.

SELECT city, country
FROM customers
UNION ALL
SELECT city, country
FROM suppliers
ORDER BY city;

Result of UNION ALL

| city         | country    |
|--------------|------------|
| Berlin       | Germany    |
| London       | UK         |
| London       | UK         |
| Los Angeles  | USA        |
| New York     | USA        |
| San Francisco| USA        |

All cities and countries from both tables, including duplicates, are returned and ordered by city.