What is the difference between UNION and UNION ALL

Question

What is the difference between UNION and UNION ALL.

Answer 1

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.

from http://zengin.wordpress.com/2007/07/31/union-vs-union-all/

Answer 2

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

Answer 3

not sure that it matters which database

UNION and UNION ALL should work on all sql servers.

You should avoid of unnecessary UNIONs they are huge performance leak. As a rule of thumb use UNION ALL if you are not sure which to use.

Answer 4

UNION does not support BLOB (or CLOB) column types, UNION ALL does.

Answer 5

UNION removes duplicates, whereas UNION ALL does not.

In order to remove duplicates the result set must be sorted, and this may have an impact on the performance of the UNION, depending on the volume of data being sorted, and the settings of various RDBMS parameters ( For Oracle PGA_AGGREGATE_TARGET with WORKAREA_SIZE_POLICY=AUTO or SORT_AREA_SIZE and SOR_AREA_RETAINED_SIZE if WORKAREA_SIZE_POLICY=MANUAL ).

Basically, the sort is faster if it can be carried out in memory, but the same caveat about the volume of data applies.

Of course, if you need data returned without duplicates then you must use UNION, depending on the source of your data.

I would have commented on the first post to qualify the "is much less performant" comment, but have insufficient reputation (points) to do so.

Answer 6

union is used to select distinct values from two tables where as union all is used to select all values including duplicates from the tables