UNION ALL vs UNION Oracle PL/SQL

UNION ALL vs UNION Oracle PL/SQL

You might be wondering should I use UNION or UNION ALL in a SQL query. What is the performance difference in UNION ALL vs UNION? This should shed some light on which one is appropriate to use in your SQL query.

UNION

In Oracle the UNION clause delays returning any rows until each portion of the UNION is sorted and merged and duplicates filtered out.

UNION ALL

A UNION ALL  simply returns all rows without the overhead of sorting, merging and removing duplicates from the results. Typically if you don’t care about duplicates or the tables are mutually exclusive you can use UNION ALL  to make a dramatic increase performance of the query.

If performance is more important than the duplicates than UNION ALL  will speed up the query, otherwise stick with UNION .