Solved

Union or Union All

Posted on 2009-06-30
7
237 Views
Last Modified: 2012-05-07
Which query should I use if I don't want duplicates:
  Union or Union All
0
Comment
Question by:schmir1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 17

Expert Comment

by:pssandhu
ID: 24746172
Union All
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 100 total points
ID: 24746174
Union wont return the duplicates

Union all returns everything
0
 
LVL 17

Assisted Solution

by:pssandhu
pssandhu earned 100 total points
ID: 24746188
Oops... my bad, I though you wanted duplicates
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
ID: 24746333
> 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.
0
 
LVL 14

Assisted Solution

by:shru_0409
shru_0409 earned 100 total points
ID: 24746361
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.
0
 
LVL 32

Expert Comment

by:awking00
ID: 24748095
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.
union-queries.txt
0
 

Author Closing Comment

by:schmir1
ID: 31598378
Thanks for all your help.  I think I have a good understanding of Union now.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question