union和union all 的区别

使用union关键字时，可以给出多条select 语句，并将它们的结果合成单个结果集。合并时两个表对应的列数和数据类型必须相同，每个select 语句之间使用union或union all 关键字分隔，union 执行的时候删除重复的记录，所有返回的行都是唯一，使用union all 关键字的作用是不删除重复行也不对结果进行自动排序。

例如：

mysql> select s_id,f_name from fruits where s_id=101 union select s_id,f_name from fruits where f_price<10;
+------+------------+
| s_id | f_name     |
+------+------------+
|  101 | apple      |
|  101 | blackberry |
|  101 | cherry     |
|  103 | apricot    |
|  106 | ahfjwj     |
|  103 | cococut    |
|  102 | grape      |
+------+------------+
7 rows in set (0.00 sec)

mysql> select s_id,f_name from fruits where s_id=101 union all select s_id,f_name from fruits where f_price<10;
+------+------------+
| s_id | f_name     |
+------+------------+
|  101 | apple      |
|  101 | blackberry |
|  101 | cherry     |
|  101 | apple      |
|  103 | apricot    |
|  106 | ahfjwj     |
|  101 | cherry     |
|  103 | cococut    |
|  102 | grape      |
+------+------------+
9 rows in set (0.00 sec)

每个select集是这样的

mysql> select s_id,f_name from fruits where s_id=101;
+------+------------+
| s_id | f_name     |
+------+------------+
|  101 | apple      |
|  101 | blackberry |
|  101 | cherry     |
+------+------------+
3 rows in set (0.00 sec)

mysql> select s_id,f_name from fruits where f_price<10
    -> ;
+------+---------+
| s_id | f_name  |
+------+---------+
|  101 | apple   |
|  103 | apricot |
|  106 | ahfjwj  |
|  101 | cherry  |
|  103 | cococut |
|  102 | grape   |
+------+---------+
6 rows in set (0.00 sec)

很明显 union all 没有去重，查询的结果还有重复的行，

union和union all 的区别

推荐阅读更多精彩内容