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 |
|
Phibian
Starting Member
32 Posts |
 Posted - 2005-01-24 : 16:56:36
|
| I have two select statements, both of which have multiple inner joins (5 each, but not to the same tables).The first one returns 12 rows.The second one returns 0 rows. When joined as a union, they return 4 rows.How is this possible? Did I miss a memo? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-01-24 : 17:05:47
|
| A union does a distinct on the resultset.If you use union all you should get the 12.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Phibian
Starting Member
32 Posts |
Posted - 2005-01-24 : 17:12:29
|
| If the UNION does a DISTINCT on the result set, then that would explain why there are only 4 rows, because some of the rows returned are identical to the others. So my twelve becomes 4. I thought that unions just appended the results sets to each other (that's really what I want to do, rather than returning distinct groups of results)Suggestions? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2005-01-24 : 17:13:58
|
| He gave you the solution. Use UNION ALL instead of UNION. Look in SQL Server Books Online for more information about both.Tara |
|
|
|
Phibian
Starting Member
32 Posts |
Posted - 2005-01-24 : 17:19:41
|
| You are right - I can't read ;)Thanks! |
|
|
|
|
|
|
UNION result set is smaller than sum of parts