Using the SQL UNION Operator

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

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

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

GET 25% OFF

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

UP TO 70% OFF

Pros

• A wide range of learning programs
• University-level courses
• Easy to navigate
• Verified certificates
• Free learning track available

Main Features

• University-level courses
• Suitable for enterprises
• Verified certificates of completion

FREE COURSES

UNION ALL: Syntax

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:

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

SQL UNION: Examples

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

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:

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":

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":

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:

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