Technical Article

Union Forcing Distinct

,

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

Rate

1.67 (3)

You rated this post out of 5. Change rating

Share

Share

Rate

1.67 (3)

You rated this post out of 5. Change rating