Why is a UNION ALL faster than a UNION?

UNION ALL doesnot worry about duplicate records,but UNION consider the duplicate records or eliminate duplicate records.UNION ALL display all the records.UNION display the distinct records.so that UNION ALL is faster than
UNION

Here,we come to one conclusion,UNION achive the DATA REDUNANCY

Thanks
Prasath.K

Hi there,

UNION : eliminates duplicates
UNION ALL: retains duplicates

Suppose I have five SQL SELECT statements connected by UNION/UNION ALL, how many times should I specify UNION to eliminate the duplicate rows?

__________________
S.VinothkumaR
Behind me is infinite power,
Before me is Endless Possibility,
Around me is Boundless Opportunity,
Why should I fear!

The union operation, you will recall, brings two sets of data together. It will *NOT* however produce duplicate or redundant rows. To perform this feat of magic, a SORT operation is done on both tables. This is obviously computationally intensive, and uses significant memory as well.

A UNION ALL conversely just dumps collection of both sets together in random order, not worrying about duplicates.

__________________
Krishnakumar.S
Beware of Everything -that is un true; stick to the Truth shall succeed slowly but steadily

UNION ALL will be faster b'coz UNION looks for the duplicate values and eliminates it where as UNION ALL will list all.

__________________
-Murali..