🚨 Get Your Free NFT Certificate Mint by Completing the Web3 Exam! START NOW

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;
DataCamp
Pros
  • Easy to use with a learn-by-doing approach
  • Offers quality content
  • Gamified in-browser coding experience
  • The price matches the quality
  • Suitable for learners ranging from beginner to advanced
Main Features
  • Free certificates of completion
  • Focused on data science skills
  • Flexible learning timetable
Udacity
Pros
  • Simplistic design (no unnecessary information)
  • High-quality courses (even the free ones)
  • Variety of features
Main Features
  • Nanodegree programs
  • Suitable for enterprises
  • Paid Certificates of completion
Udemy
Pros
  • Easy to navigate
  • No technical issues
  • Seems to care about its users
Main Features
  • Huge variety of courses
  • 30-day refund policy
  • Free certificates of completion

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;