Union vs Union All

Union and union all operations are used to combine two or more queries in a table.

These queries must have same number of columns and these columns must have similar properties when we compare column by column.

Syntax:

n

select field1, field2, . field_n
from tables
UNION [ ALL ]
select field1, field2, . field_n
from tables;

The basic difference between UNION and UNION ALL is union operation eliminates the duplicated rows from the result set but union all returns all rows after joining.

For example :

X                  Y                   UNION                UNION ALL

---              ---                  -------               ---------

A		   B                     A                      A

A		   B                     B                      A

B                  A  						B
  							        B

                                                                B

                                                                A

n

Let’s look at the validity of the similarities between columns for UNION [ALL] :

If component queries select character data, then the datatype of the return values are determined as follows:

• If both queries select values of datatype CHAR of equal length, then the returned values have datatype CHAR of that length. If the queries select values of CHAR with different lengths, then the returned value is VARCHAR2 with the length of the larger CHAR value.
• If either or both of the queries select values of datatype VARCHAR2, then the returned values have datatype VARCHAR2.

If component queries select numeric data, then the datatype of the return values is determined by numeric precedence:

• If any query selects values of type BINARY_DOUBLE, then the returned values have datatype BINARY_DOUBLE.
• If no query selects values of type BINARY_DOUBLE but any query selects values of type BINARY_FLOAT, then the returned values have datatype BINARY_FLOAT.
• If all queries select values of type NUMBER, then the returned values have datatype NUMBER.

In queries using set operators, Oracle does not perform implicit conversion across datatype groups. Therefore, if the corresponding expressions of component queries resolve to both character data and numeric data, Oracle returns an error.

Which one provides better performance ?

When you look at the autotrace result for both of them. On the execution plans, you can notice that that a union opration is already use an union all operation in execution.Shortly,

union = union all + sort operations

TEST RESULTS :autotrace for union vs union all

COMMENTS:

n

 

• nIf you can use UNION ALL, by all means use it over UNION to avoid a costly deduplication step, a step that is probably not even necessary most of the time.

• Always give a priority to union all!

• Use UNION ALL instead of UNION (wherever possible).

References:

nUNION VS UNION ALL

http://tonguc.wordpress.com

n

nOracle 10g Documentation Library SQL Reference

http://tahiti.oracle.com

About these ads