UNION or UNION ALL

You may be familiar with the UNION operator in Oracle SQL, but are aware of the UNION ALL operator?

Perhaps you will be surprised to hear that more often than not when you write UNION, what you meant to use is UNION ALL.

The UNION operator is used to combine result sets from multiple SELECT statements into a single result set. However, it eliminates duplicates in process.

SQL> select 'ONE' col_val from dual
  2  union
  3  select 'ONE' from dual;


COL_VAL
-------
ONE


SQL> select 'ONE' col_val from dual
  2  union
  3  select 'TWO' from dual;

COL_VAL
-------
ONE
TWO

In order for the database to do this the database will need to join the two result sets, sort them, find the duplicates and eliminate them.

Hey wait a minute, sort the results? Yes, the most efficient way to eliminate duplicates is to order the results and then find the duplicate entries. To make matters worse, if you have a “large” amount of data, this sort operation may not be able to be performed in memory (which would be the most efficient approach).

UNION ALL on the other hand will combine the result sets as they are. It will return all rows.

SQL> select 'ONE' col_val from dual
  2  union all
  3  select 'ONE' from dual;


COL_VAL
-------
ONE
ONE

SQL> select 'ONE' col_val from dual
  2  union all
  3  select 'TWO' from dual;

COL_VAL
-------
ONE
TWO

There’s clearly a use case for both scenarios. However, I would submit to you, more often than not, what we really want is to combine all the rows.

Be aware, that when using UNION you’re asking the database to do more work, make sure that’s what you really want.

I know that if you search for UNION vs UNION ALL you’ll find a gazillion results. But hey, I keep seeing UNION misused, so maybe we need one more blog about it.

Go ahead, share...

Facebook

Twitter

Linkedin

Google

Tumblr

Buffer

Stumbleupon

Email