union和union all 的区别

来源：互联网发布：mac桌面编辑：程序博客网时间：2024/05/22 02:02

使用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 没有去重，查询的结果还有重复的行，

0 0