Difference between Union, Union All in Sql - Java @ Desk

Saturday, November 30, 2013

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







No comments:

Post a Comment