Difference between Union, Union All in Sql

Difference between Union and Union All

UNION The UNION command is used to select related information from two tables, much like the JOIN command. However, when using the UNION command all selected columns need to be of the same data type.

1) UNION removes duplicate records, UNION ALL does not
2) UNION is slower than UNION ALL since there is an additional tasks need to be performed to remove the duplicate rows i.e. UNION selects the distinct results
3) In order to remove duplicates the result set must be sorted, and this may have an impact on the performance of the UNION
4) UNION is used to select distinct values from two tables where as UNION ALL is used to select all values including duplicates from the tables

Consider the below tables

Employee

EmpId	EmpName
1	John
2	David
3	Peter
4	Eric

Customer

CustId	CustName
1	Cynthia
2	Thanh
3	Peter
4	Eric

UNION Query

Select EmpName from Employee
UNION
Select CustName from Customer

Output is - Have a look, it removed duplicate names Peter and Eric. These names appeared only once.

John

David

Peter

Eric

Cynthia

Thanh

UNION ALL Query

Select EmpName from Employee
UNION ALL
Select CustName from Customer

Output is - Have a look, it did not removed duplicate names Peter and Eric. These names appeared only twice

John

David

Peter

Eric

Cynthia

Thanh

Peter

Eric