Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941

    Unanswered: UNION vs UNION ALL

    Just a pet peeve....

    From BOL:

    Code:
    By default, the UNION operator removes duplicate rows from the result set. 
    If you use ALL, all rows are included in the results and duplicates are not 
    removed.
    Why is it assumed that one would want the duplicates removed by default?
    Isn't that what SELECT DISINCT is for?
    Inspiration Through Fermentation

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    it is another way to remove dupes

    to see why it's good, try writing the sql for a UNION that has several SELECTs in it, assuming that you will need to remove dupes with DISTINCT from the result

    don't just toss DISTINCT into each SELECT, that won't work because the dupes could happen as one row comes from one SELECT and a dupe comes from a different SELECT

    so go ahead, try writing the sql yourself -- it's ugly

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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