The beginning of knowledge is the discovery of something we do not understand. [Frank Herbert]

Menu

Do you know the difference between the UNION and the UNION ALL part of a statement? In short, UNION does a sort-unique on both collections. UNION ALL just retrieves all rows, regardless of any double values.

Note that I am not getting duplicate values. Is this because of the distinct clause in both select statements? This clause is applied to each result set individually so the union operator must be doing a distinct as well. Let’s try removing the distinct clause from the select statements:

We get the same results as before. This should mean that the union operator performs a distinct on the entire result set. There is a way to circumvent this behavior. This is done by adding the all clause to the union operator. This tells the SQL-engine to just add both result sets regardless of any duplicates.

This way both sets of result are returned. Note that there is no sorting done either. The union operator apparently also performs sorting on the result set. The distinct clause makes sure the different sets of data give me the unique values. If I omit this clause then the result is like this:

Note that performing the distinct and sort operation on the result costs time, so if you are absolutely sure the result set for both parts of the union are mutually exclusive (that is, there will never be any duplicates in the sets) then use the union all clause to have the query perform (a lot) faster.

Another issue is that both select statements should retrieve the same number of columns. Also the datatypes of all the retrieved columns must be the same.

Post navigation

One thought on “Union vs Union All”

A few comments:
>Note that there is no sorting done either. The union operator apparently also performs sorting on the result set.
NO, NO. Never ever rely on any specific order without order by clause! This time it is just because Oracle is using sort unique opeartion to sort out distinct values, but as soon as you are using parralel operations, partitions and/or use hash unique operation to sort out distinct values it won’t be true anymore.
>Also the datatypes of all the retrieved columns must be the same.
Let’s say they have to be such that Oracle at least can do implicit conversion
[shameless plug] 🙂
And see more about sort operators in my article http://www.gplivna.eu/papers/sql_set_operators.htm
[/shameless plug]