Learn SQL Union Operator: Union vs Union All Explained

SQL UNION Operator: Main Tips

The UNION operator default selection is distinct values. Use the ALL keyword with UNION to allow duplicate values.
The column names in the result-set of a UNION are usually equal to the column names in the first SELECT statement in the UNION.
Within UNION each SELECT statement must have the same columns number.
The columns must have analogous data types.
In each SELECT statement, the columns must be in the same order.

Combines the result-set of two or more SELECT statements

Example

SELECT demo_column_name(s) FROM demo_table1
UNION
SELECT demo_column_name(s) FROM demo_table2;

Example

SELECT demo_column_name(s) FROM demo_table1
UNION ALL
SELECT demo_column_name(s) FROM demo_table2;

Note: UNION ALL allows selecting duplicate values

This is demo example from the "Developers" table in the database:

ID	Name	City	Country

1	Tom Kurkutis	New York	USA
2	Ana Fernandez	London	UK
3	Antonio Indigo	Paris	France
4	Aarav Kaelin	Delhi	India
5	Andrew Tumota	Miami	USA
6	Basma Zlata	Miami	USA

And the demo example from the "Clients" table in the database:

ID	Name	Contact	Address	City	Postal_Code	Country
1	Ben Choplinks	Ben Choplink	Obeesre Str. 51	Rome	11207	Italy
2	Donald Rich	Donald Richario	Avda. de la Confgfstitución 4122	Tallin	17021	Estonia
3	Lilly Smilkins	Lilly Smilkin	Matadsderos 2312	Eguero	14023	Mexico
4	Brandinina	Tom Hitchins	110 Hanegover Sq.	London	WB2 2DP	UK
5	Carizmos	Christiano Kerrys	Berguvsesvägen 9	Luleå	S-968 43	Sweden

In this example, we select all the different ( no duplicate values ) cities from the "Developers" and the "Clients" tables:

Example

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

In this example, we use UNION ALL to select all (duplicate values also) cities from the "Developers" and "Clients" tables:

Example

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

In this example, we select all the different USA cities (only distinct values) from "Developers" and "Clients":

Example

SELECT City, Country FROM Developers
WHERE Country='USA'
UNION
SELECT City, Country FROM Customers
WHERE Country='USA'
ORDER BY City;

In this example, we selects all German cities (duplicate values also) from "Developers" and "Clients":

Example

SELECT City, Country FROM Developers
WHERE Country='USA'
UNION ALL
SELECT City, Country FROM Customers
WHERE Country='USA'
ORDER BY City;

In this example, we list all developers and clients:

Example

SELECT 'Developer' As Type, Name, City, Country
FROM Developers
UNION
SELECT 'Client', Name, City, Country
FROM Customers;