Solved

Union or Union All

Posted on 2009-06-30
7
222 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
Comment Utility
Union All
0
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 100 total points
Comment Utility
Union wont return the duplicates

Union all returns everything
0
 
LVL 17

Assisted Solution

by:pssandhu
pssandhu earned 100 total points
Comment Utility
Oops... my bad, I though you wanted duplicates
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 200 total points
Comment Utility
> 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
Comment Utility
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 31

Expert Comment

by:awking00
Comment Utility
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
Comment Utility
Thanks for all your help.  I think I have a good understanding of Union now.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

10 Experts available now in Live!

Get 1:1 Help Now