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;
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
Coursera
Pros
  • Professional service
  • Flexible timetables
  • A variety of features to choose from
Main Features
  • Professional certificates of completion
  • University-level courses
  • Multiple Online degree programs
Datacamp
Pros
  • Great user experience
  • Offers quality content
  • Very transparent with their pricing
Main Features
  • Free certificates of completion
  • Focused on data science skills
  • Flexible learning timetable

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;

Latest Udacity Coupon Found:

Verified STAFF PICK

75% OFF COURSES

Udacity Black Friday Offer

The best time to save on Udacity courses is now - follow this coupon to access a 75% Udacity Black Friday discount & enjoy learning at a very low cost!

Expiration date: 28/02/2021
3176 People Used
Only 97 Left
Rating
5.0