Union or Union all

[pascal01]

Hi,
Can some please explain the difference between the UNION and UNION ALL used in a select statement?
I've tried looking it up but can not seem to find a good description with some examples. Thanks.

[slimdave]

UNION ALL just combines the result sets of the two-or-more queries ... UNION does the same but then performs a DISTINCT on the result set.

So ...

Select ... From ...
Union
Select ... From ...

... is the same as ...

Select Distinct ...
From
   (
   Select ... From ...
   Union All
   Select ... From ...
   )

[raghud]

In other words UNION ALL returns with duplicates but UNION gives the output with out duplicates. Some people use UNION to eliminate duplicates if do not want use group by and having clause.

[DaPi]

. . . and because of the extra work in evaluating DISTINCT, it's worth thinking carefully which you actually NEED.

(I'm currently going through and correcting a bunch of views that have unnecessary DISTINCT's - used by over-cautious developers who weren't working on full size tables! Some nice performance improvements.)

[Cookies]

hell, I've seen code with UNIONs and inside
the UNION is a distinct clause! wtf?

(select distinct a from tableA)
union
(select distinct a from tableB)

[DaPi]

I wonder if the CBO is smart enough to reduce that to the minimum level of "distinctness"? No time to try now . . . .