Union or Union all
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Union or Union all

  1. #1
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    218

    Union or Union all

    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.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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 ...
    Code:
    Select ... From ...
    Union
    Select ... From ...
    ... is the same as ...
    Code:
    Select Distinct ...
    From
       (
       Select ... From ...
       Union All
       Select ... From ...
       )
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Aug 2001
    Posts
    267
    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.
    Raghu

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    . . . 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.)

  5. #5
    Join Date
    Oct 2002
    Posts
    182
    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)
    - Cookies

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    I wonder if the CBO is smart enough to reduce that to the minimum level of "distinctness"? No time to try now . . . .

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width