DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: "union" and "union all"

  1. #1
    Join Date
    Sep 2000
    Posts
    20
    can anyone tell me what's the difference between these?
    i can tell "union all" is a bit faster... but what is the real difference?

  2. #2
    Join Date
    Nov 2001
    Location
    Central U.S.
    Posts
    35
    UNION sorts the data from the combined result sets and removes any duplicates between them, ensuring that only distinct records are returned.

    UNION ALL does no such 'cleanup', and, therefore, is much faster, since it simply UNION's all data records and returns the combined result.
    David D. Fitzjarrell
    Oracle Certified DBA

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    UNION will return the unique rows, UNION ALL will return all the rows. For example:
    Code:
    SQL> select * from a;
    
    A
    ----------
    A
    B
    C
    
    SQL> select * from b
      2  ;
    
    B
    ----------
    B
    C
    D
    
    SQL> select a from a
      2  union 
      3  select b from b;
    
    A
    ----------
    A
    B
    C
    D
    
    SQL> select a from a
      2  union all
      3  select b from b;
    
    A
    ----------
    A
    B
    C
    B
    C
    D
    
    6 rows selected.
    Jeff Hunter

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