Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cidr
Posting Yak Master
207 Posts |
 Posted - 2010-03-16 : 06:37:10
|
| Hi there,hope someone can help me with this.I know that UNION removes duplicates and UNION ALL effectively stacks the results from both sides. However, I've been working on a query that has a UNION. the top SELECT returns 87,000 rows by itself. The bottom returns 0 rows when run by itself. But when the UNION is added and the query run, my results are 78,000 rows. 9,000 less. I don't understand why this is the case as I thought UNION only removed the row that was identical to a row in the other SELECT statement.Can anyone shed some light as to why this might be?Many thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-16 : 06:39:44
|
| Nope. union takes distinct after combining both side results. so it may be that you've only 78,000 distinct values in your first select itself------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
cidr
Posting Yak Master
207 Posts |
Posted - 2010-03-16 : 06:51:55
|
| Hi,So, essentially the UNION removes all duplicates regardless of what tables the rows are from? i.e. removes dups even if the dups are from the same table?Thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-16 : 07:07:23
|
| yeah...exactly. ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
cidr
Posting Yak Master
207 Posts |
Posted - 2010-03-16 : 08:00:50
|
| Thanks visakh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-16 : 08:08:43
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-03-16 : 08:28:42
|
| Simple exampleselect 1union allselect 1union allselect 1union allselect 1union select nullMadhivananFailing to plan is Planning to fail |
|
|
|
jennifermilly
Starting Member
1 Post |
Posted - 2010-10-12 : 08:09:40
|
| Thanks for post. It’s really informative stuff.I really like to read.Hope to learn a lot and have a nice experience here! my best regards guys!jennifermilly |
|
|
|
|
|
|
UNION returning less rows?