Union or Union All

Which query should I use if I don't want duplicates:
  Union or Union All
Who is Participating?
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
> if I don't want duplicates:
it depends on what you define a "duplicate".

union will do a distinct, but only over ALL the columns returned by the query.
if your "duplicates" are in a single (or part of the ) column(s), neither will work, but you will need to change the query eventually completely.
Union All
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
Union wont return the duplicates

Union all returns everything
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

pssandhuConnect With a Mentor Commented:
Oops... my bad, I though you wanted duplicates
shru_0409Connect With a Mentor Commented:
The UNION clause forces all rows returned by each portion of the UNION to be sorted and merged and duplicates to be filtered before the first row is returned to the calling module.

A UNION ALL simply returns all rows including duplicates and does not have to perform any sort, merge, or filter.
Just so you can see the difference, see the attached. Notice how the union results are ordered and the union all results are just stacked.
schmir1Author Commented:
Thanks for all your help.  I think I have a good understanding of Union now.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.