union eliminates rows from half of query when other half is null

Question

Can someone explain why if I run the query below, rows are not added correctly from the top half of the union? I thought union only eliminated duplicates, not values? If I make it union all, it fixes it, I'm just not sure why it's needed? Thanks,jb

CREATE TABLE #c3 (idNbr int, idValue int, idP int);
insert into #c3 (idNbr, idValue, idP) select 1 , 1 , 1 ;
insert into #c3 (idNbr, idValue, idP) select 1 , 1 , 1 ;
insert into #c3 (idNbr, idValue, idP) select 1 , 1 , 1 ;

CREATE TABLE #c4 (idNbr int, idValue int, idP int);
insert into #c4 (idNbr, idValue, idP) select 1 , 1 , 2 ;

select rs.idNbr, sum(rs.id1Tot)idTot, sum(rs.id2Tot) idTot2 from (
    select idNbr, idvalue id1Tot, 0 id2Tot from #c3 where idP=1
     union  
    select idNbr, 0 idTot, idvalue id2Tot from #c4 where idP=1
    )rs  group by rs.idNbr

drop table #c3
drop table #c4

Answer 1

UNION puts all rows from two tables in one table and then removes duplicates from the result. It doesn't look only at the rows of the second table to find whether such row already existed in the first table.

In other words, when UNION eliminates duplicates it doesn't care from which table the rows came. The result of T1 UNION T2 is the same as T2 UNION T1.

In your example the first table of the UNION operator has three identical rows.

1, 1, 1
1, 1, 1
1, 1, 1

The second table of the UNION operator is empty.

UNION puts three and zero rows together:

1, 1, 1
1, 1, 1
1, 1, 1

and then eliminates duplicates from this result, which leaves only one row:

1, 1, 1

Then you calculate a summary off that one row.

---

UNION ALL doesn't eliminate anything, so if you use UNION ALL, its result is three rows

1, 1, 1
1, 1, 1
1, 1, 1

which then go into the summary.