例解Union和Union All的区别_querydsl union all-优快云博客

本文链接：https://blog.youkuaiyun.com/maray/article/details/8848116

本文通过实例对比了SQL中Union和UnionAll的区别。Union用于合并两个SELECT语句的结果集，并自动过滤掉重复的记录；而UnionAll同样合并结果集，但保留所有记录，包括重复的数据。文章还解释了如何定义重复以及展示了不同情况下的具体应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#例解Union和Union All的区别

mysql> create table u1 (i int primary key, j int);
Query OK, 0 rows affected (0.27 sec)

mysql> create table u2 (i int primary key, j int);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into u1 values (1,1),(2,2);
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into u2 values (1,1),(2,2);
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> (select i,j from u1) union (select i,j from u2);
+---+------+
| i | j    |
+---+------+
| 1 |    1 |
| 2 |    2 |
+---+------+
2 rows in set (0.05 sec)

mysql> (select i,j from u1) union all (select i,j from u2);
+---+------+
| i | j    |
+---+------+
| 1 |    1 |
| 2 |    2 |
| 1 |    1 |
| 2 |    2 |
+---+------+
4 rows in set (0.00 sec)

Union All不会去重复行，Union会去重复行。

”重“的定义为：整行数据都相等。rowkey相等，其它列值不等，不算重复行。例如：

mysql> insert into u1 values (3,1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into u2 values (3,3);
Query OK, 1 row affected (0.02 sec)

mysql> (select i,j from u1) union all (select i,j from u2);
+---+------+
| i | j    |
+---+------+
| 1 |    1 |
| 2 |    2 |
| 3 |    1 |
| 1 |    1 |
| 2 |    2 |
| 3 |    3 |
+---+------+
6 rows in set (0.00 sec)

mysql> (select i,j from u1) union (select i,j from u2);
+---+------+
| i | j    |
+---+------+
| 1 |    1 |
| 2 |    2 |
| 3 |    1 |
| 3 |    3 |
+---+------+
4 rows in set (0.00 sec)