union all vs union

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 my SQL query. 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 with out the overhead of sorting, merging and removing duplicates from the results. If you don’t care about duplicates or the tables are mutually exclusive you can use UNION ALL  to 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 .

Leave a Reply

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

Enter Captcha Here : *

Reload Image

This site uses Akismet to reduce spam. Learn how your comment data is processed.