SQL UNION Operator

Introduction

The SQL UNION operator combines the result-set of two or more SELECT statements.
To use UNION operator, each SELECT statement within the UNION should have the same number of columns of similar data types and the column in each SELECT statement should be in same order.
The UNION operator selects only distinct values but ALL keyword is used with UNION to allow duplicate values.

Syntax:

SELECT column_name FROM table1
UNION
SELECT column_name FROM table2;

Syntax with ALL keyword:

SELECT column_name FROM table1
UNION ALL
SELECT column_name FROM table2;

Example : Consider the following tables.

Table1 : 'Professors'

Prof_ID	Prof_Name	City	Country
1	Thomas	Hamilton	New Zealand
2	Albert	Perth	Australia
3	Bush	Sydney	Australia
4	Clinton	Sydney	Australia
5	Fleming	Hamilton	New Zealand

Table2 : 'Students'

Stud_ID	Stud_Name	City	Country
1	Mark	London	England
2	Alex	Paris	France
3	Bob	Sydney	Australia
4	Jaya	Delhi	India
5	Surendra	Baroda	India

1. Write a query to select different cities from table1 and table2.

SELECT City FROM Professors
UNION
SELECT City FROM Students;

The result is shown in the following table.

2. Write a query to select cities by using ALL keyword.

SELECT City FROM Professors
UNION ALL
SELECT City FROM Students;

The result is shown in the following table.