The code attached demonstrates the issues clearly.
2018-06-12
1,054 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