union 及 union all的区别

今天要查多张表count数据，表结构相同，要汇总结果。

 

我最初的写法是

 

select count(*) ,sum(c) from (
select 1+2 as c union
select 1+2 as c union
select 1+2 as c union
select 1+2 as c) as a

 

 

结果

 

count(*),   c
1,              3

 

结果明显不对，我找了好半天最终找到原因，union会消除重复结果

 

改进后sql

 

select count(*) ,sum(c) from (
select 1+2 as c union all
select 1+2 as c union all
select 1+2 as c union all
select 1+2 as c) as a

 

结果

count(*),   c
4,              12

 

union all 不会消除重复