How does the union operator work on empty result sets? I have three views, tmp1, tmp2 and tmp3. They all contain the same columns. I run the following queries: select * from tmp1 --returns 0 rows select * from tmp2 --returns 0 rows select * from tmp3 --returns 6 rows select * from tmp1 union all select * from tmp2 union all select * from tmp3 --returns 6 rows select * from tmp1 union select * from tmp2 union select * from tmp3 -- returns 18648 rows I'd be interested in knowing why and how the union operator returns more rows than the union all operator. Could the empty result sets mess up the union? Any input appreciated. Cheers, Pat

The main difference between UNION ALL and UNION is that, UNION only selects distinct values, while UNION ALL selects all values (including duplicates).Arthur My Blog

The main difference between UNION ALL and UNION is that, UNION only selects distinct values, while UNION ALL selects all values (including duplicates).Arthur My Blog

That doesn't really explain why the union returns more rows than the union all in my example.

Hi Pat- 18,648 = 6*3108. What do the results look like? Is it the same 6 rows repeated 3,108 times? What are the DDLs etc. Hard to answer based on the information given. Be that as it may, I tried doing the same thing you mentioned above and only got 6 rows with the UNION (only) operator. Bonediggler

Hi Pat- 18,648 = 6*3108. What do the results look like? Is it the same 6 rows repeated 3,108 times? What are the DDLs etc. Hard to answer based on the information given. Be that as it may, I tried doing the same thing you mentioned above and only got 6 rows with the UNION (only) operator. Bonediggler

pat select * from tmp2 --returns 6 rows and ... where is the select * from tmp3 --returns ??? rows Mixed tables up??Arthur My Blog

pat select * from tmp2 --returns 6 rows and ... where is the select * from tmp3 --returns ??? rows Mixed tables up??Arthur My Blog

Thanks for the input guys. I had two typos in the first post. Sorry. I am working with views, not tables. I updated my databases to get more data to work with. Currently the results show: select * from tmp1 -- 23 rows select * from tmp2 -- 0 rows select * from tmp3 -- 42 rows select * from tmp1 union all select * from tmp2 union all select * from tmp3 -- 65 rows select * from tmp1 union select * from tmp2 union select * from tmp3 -- 18771 Interestingly enough, when I copied the results from each view into temp tables I ended up with the consistent results. select * into #tmp1 from tmp1 select * into #tmp2 from tmp2 select * into #tmp3 from tmp3 select * from #tmp1 -- 23 rows select * from #tmp2 -- 0 rows select * from #tmp3 -- 42 rows select * from #tmp1 union all select * from #tmp2 union all select * from #tmp3 -- 65 rows select * from #tmp1 union select * from #tmp2 union select * from #tmp3 -- 65 rows I guess the conclusion is to not use set operators against views?! Are there any articles about this somewhere? Pat

Thanks for the input guys. I had two typos in the first post. Sorry. I am working with views, not tables. I updated my databases to get more data to work with. Currently the results show: select * from tmp1 -- 23 rows select * from tmp2 -- 0 rows select * from tmp3 -- 42 rows select * from tmp1 union all select * from tmp2 union all select * from tmp3 -- 65 rows select * from tmp1 union select * from tmp2 union select * from tmp3 -- 18771 Interestingly enough, when I copied the results from each view into temp tables I ended up with the consistent results. select * into #tmp1 from tmp1 select * into #tmp2 from tmp2 select * into #tmp3 from tmp3 select * from #tmp1 -- 23 rows select * from #tmp2 -- 0 rows select * from #tmp3 -- 42 rows select * from #tmp1 union all select * from #tmp2 union all select * from #tmp3 -- 65 rows select * from #tmp1 union select * from #tmp2 union select * from #tmp3 -- 65 rows I guess the conclusion is to not use set operators against views?! Are there any articles about this somewhere? Pat

pat, my take on this is that your view is somehow skewing the row counts. What is in the view?Arthur My Blog

pat, my take on this is that your view is somehow skewing the row counts. What is in the view?Arthur My Blog

I think I found the culprit. (The original views that I query were not created by me.) The base view queried uses rowid() in combination with another column to produce a unique identifier for each row this was then advertised as a "unique key" for the view. Although I am pretty certain this is the problem, it still puzzles me as to how the union all brings back fewer rows. I shall find a better unique identifier and hope it solves my problems. Thanks Pat

I think I found the culprit. (The original views that I query were not created by me.) The base view queried uses rowid() in combination with another column to produce a unique identifier for each row this was then advertised as a "unique key" for the view. Although I am pretty certain this is the problem, it still puzzles me as to how the union all brings back fewer rows. I shall find a better unique identifier and hope it solves my problems. Thanks Pat

Changed the key which made the situation a bit better (as in less incorrect records), although the unions still don't behave the way you expect them to. Rather than combining the original queries as a view, I separated out the queries and stored the results in temporary tables (like displayed in one of my previous posts) and performed my union on the temporary tables. The problem has been solved, although it really puzzles me and I would love to find more out about how unions on views actually work and why this happened.

Changed the key which made the situation a bit better (as in less incorrect records), although the unions still don't behave the way you expect them to. Rather than combining the original queries as a view, I separated out the queries and stored the results in temporary tables (like displayed in one of my previous posts) and performed my union on the temporary tables. The problem has been solved, although it really puzzles me and I would love to find more out about how unions on views actually work and why this happened.