Which is Faster between UNION and UNION ALL? Why?

UNION ALL is always FASTER…!!!

As we all know that, UNION and UNION ALL are the SET operators which combines/merges the multiple data sets data into a single data set. UNION eliminates the duplicates after combining the records. But UNION ALL ignores the duplicates.

UNION functionality:

  • Step 1: Merge all the records from all the data sets into a single data set.
  • Step 2: Filter DISTINCT records among them.

UNION ALL functionality:

  • Step 1: Merge all the records from all the data sets into a single data set.

UNION does an extra operation called DISTINCT which is very costly. But UNION ALL avoids filtering duplicate records from the data set. It saves lot of time when we are working with the huge data.

Warning: We can use UNION ALL only when we are pretty much sure that there will be no duplicates among all the records from all the data sets. If we are not sure about the duplicates in the data, we should go for UNION.

Happy Reading  :-) 

Leave a Reply

Your email address will not be published. Required fields are marked *