The code attached demonstrates the issues clearly.
2018-06-12
1,059 reads
The code attached demonstrates the issues clearly.
create table #t1 (aa varchar(1), i int)
insert into #t1 values ('a',1), ('a', 2), ('a', 3),('a', 1)
create table #t2 (aa varchar(1), i int)
insert into #t2 values ('b',1), ('b', 2), ('b', 3),('b', 1)
select * from #t1 -- =4 rows
select * from #t2 -- =4 rows
select * from #t1  
union all
select * from #t2 
-- 8 rows, as expected
select * from #t1  -- the union forces distinct within this select!
union 
select * from #t2 -- the union forces distinct within this select!
-- returns 6 rows, even though there are no rows which exist in both tables!
create table #t3 (aa varchar(1), i int)
insert into #t3 values ('c',1), ('c', 2), ('c', 3),('c', 1)
select * from #t3 -- =4 rows
select * from #t1 -- this has not become distinct
union all
select * from #t2 -- this has not become distinct
union all
select * from #t3 -- this has not become distinct
-- = 12 rows, as expected
select * from #t1  -- this has become distinct!
union all
select * from #t2 -- this has become distinct!
union
select * from #t3 -- this has become distinct!
-- = 9 rows
select * from #t1 -- this has become distinct!
union 
select * from #t2 -- this has become distinct!
union all
select * from #t3 -- this has not become distinct
-- = 10 rows