Code has been added to clipboard!

Using the SQL UNION Operator

Reading time 2 min
Published Oct 10, 2019
Updated Oct 10, 2019

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.

Syntax of SQL UNION Operator

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;
Theory is great, but we recommend digging deeper!

UNION ALL: Syntax

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

Demo Database

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

SQL UNION: Examples

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;