Difference between UNION and UNION ALL

This is a very common question in SQL. I tried collecting more information and here are the differences.

UNION
UNION statement populates data from selected columns by removing row to row duplicate records in the selected result set.

UNION ALL
UNION ALL statement populates all data without removing any duplicates.

Example depicted below

CREATE TABLE T1(ID int, Name varchar(50), City varchar(50), country varchar(50))

INSERT INTO T1 Values (1, 'Bheem' , 'Bangalore', 'India')
INSERT INTO T1 Values (2, 'Krishna' ,'Delhi', 'India')
INSERT INTO T1 Values (3, 'Bheem' , 'Chennai', 'India')
INSERT INTO T1 Values (4, 'Kalia' , 'shanghai', 'China')
INSERT INTO T1 Values (5, 'Chutki' , 'FloridaCity', 'USA')
INSERT INTO T1 Values (6, 'Bheem' , 'Chennai', 'India')

select * from T1

ID	Name	City	Country
1	Bheem	Bangalore	India
2	Krishna	Delhi	India
3	Bheem	Chennai	India
4	Kalia	shanghai	China
5	Chutki	FloridaCity	USA
3	Bheem	Chennai	India

UNION

select Name,city,Country from T1 where City = 'Bangalore'
UNION
select Name,city,Country from T1 where City = 'Chennai'

Name	City	Country
Bheem	Bangalore	India
Bheem	Chennai	India

UNION ALL

select Name,city,Country from T1 where City = 'Bangalore'
UNION
select Name,city,Country from T1 where City = 'Chennai'

Name	City	Country
Bheem	Bangalore	India
Bheem	Chennai	India
Bheem	Chennai	India

Note

• UNION and UNION ALL statements executes between two or more SELECT queries.
•  Both can be used only with SELECT statement.

• All queries executed with UNION and UNION ALL must be specified with same number of columns with same datatype.

Note

• Make sure the specified columns in all the queries must be in same order. Because both UNION and UNION ALL statements checks for same number of columns and same datatype. This will not check the order of columns specified as in below example