Solved

Union or Union All

Posted on 2009-06-30
7
241 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
Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

 
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

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…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

729 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