UNION 和 UNION ALL的区别，一个例子就看明白

最新推荐文章于 2024-09-02 19:45:47 发布

原创最新推荐文章于 2024-09-02 19:45:47 发布 · 2.6k 阅读

8 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

10 篇文章

订阅专栏

本文通过具体的SQL示例对比了UNION ALL与UNION操作的区别，UNION ALL保留所有记录，包括重复项，而UNION则移除重复记录。示例展示了这两种操作在求和聚合函数上的应用。

【UNION ALL】

select a,b,sum(sm) AS s1, SUM(qm) AS s2 from 
(
 select 'a' AS a, 'b' AS b, 2 AS sm, 200 AS qm 
UNION ALL
 select 'a' AS a, 'b' AS b, 2 AS sm, 200 AS qm
)r
group by a,b

ab4400

【UNION】

select a,b,sum(sm) AS s1, SUM(qm) AS s2 from 
(
 select 'a' AS a, 'b' AS b, 2 AS sm, 200 AS qm 
UNION 
 select 'a' AS a, 'b' AS b, 2 AS sm, 200 AS qm
)r
group by a,b

ab2200

总结：UNION ALL全部列出，UNION会去掉重复的。