Difference between UNION and UNION ALL

UNION : UNION removes duplicate records in output. UNION performs a DISTINCT on the result set, eliminating any duplicate rows

UNION ALL : UNION ALL does not remove duplicates records in output

Select * from city

sno	city
1	Delhi
2	Kanpur
3	Lucknow
4	Aligarh
5	Alur
6	Ambaliyasan
7	Anandnagaar

select * from State

sno	city	state
1	Lucknow	UP
2	Kanpur	UP
3	Anandnagaar	Assam
4	Ambaliyasan	Gujarat
5	Ambaji	Gujarat
6	Aligarh	UP
7	Delhi	Delhi
8	Alandha	Karnataka
9	Achhnera	UP

select city from City

UNION

select city from State

city

Achhnera

Alandha

Aligarh

Alur

Ambaji

Ambaliyasan

Anandnagaar

Delhi

Kanpur

Lucknow

select city from City

UNION ALL

select city from State

city

Delhi

Kanpur

Lucknow

Aligarh

Alur

Ambaliyasan

Anandnagaar

Lucknow

Kanpur

Anandnagaar

Ambaliyasan

Ambaji

Aligarh

Delhi

Alandha

Achhnera