Solved

Union or Union All

Posted on 2009-06-30
7
224 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
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 142

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

932 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now