MySQL UNION和UNION ALL:合并查询结果(附带实例)
在 MySQL 中,利用 UNION 关键字可以联合多条 SELECT 语句,并将它们的结果组合成单个结果集。
合并时,两张表对应的列数和数据类型必须相同。各个 SELECT 语句之间使用 UNION 或 UNION ALL 关键字分隔:
基本语法格式如下:
为了演示的需要,需要创建数据表 fruits,SQL 语句如下:
【实例 1】查询所有价格小于 9 的水果的信息,查询 s_id 等于 101 和 103 的所有水果的信息,使用 UNION 连接查询结果,SQL 语句如下:
如前所述,UNION 将多个 SELECT 语句的结果组合成一个结果集合。可以分开查看每个 SELECT 语句的结果:
SELECT s_id, f_name, f_price FROM fruits WHERE s_id IN(101,103);
使用 UNION ALL 会包含重复的行。在【实例 1】中,分开查询时,两个返回结果中有相同的记录,UNION 从查询结果集中自动去除了重复的行。如果要返回所有匹配行,而不进行删除,可以使用 UNION ALL。
【实例 2】查询所有价格小于 9 的水果的信息,查询 s_id 等于 101 和 103 的所有水果的信息,使用 UNION ALL 连接查询结果,SQL语句如下:
使用 UNION ALL 时不会删除重复行,并且执行时需要的资源少。因此,当知道有重复行但是想保留这些行、确定查询结果中不会有重复数据或者不需要去掉重复数据的时候,应当使用 UNION ALL 以提高查询效率。
合并时,两张表对应的列数和数据类型必须相同。各个 SELECT 语句之间使用 UNION 或 UNION ALL 关键字分隔:
- 不带 ALL 关键字的 UNION,执行时会删除重复的记录,所有返回行都是唯一的;
- 使用关键字 ALL 的作用是不删除重复行,也不对结果进行自动排序。
基本语法格式如下:
SELECT column,... FROM table1 UNION [ALL] SELECT column,... FROM table2
为了演示的需要,需要创建数据表 fruits,SQL 语句如下:
CREATE TABLE fruits ( f_id char(10) NOT NULL, s_id INT NOT NULL, f_name char(255) NOT NULL, f_price decimal(8,2) NOT NULL, PRIMARY KEY(f_id) );插入如下数据:
mysql> INSERT INTO fruits (f_id, s_id, f_name, f_price) VALUES('a1', 101,'apple',5.2), ('b1',101,'blackberry', 10.2), ('bs1',102,'orange', 11.2), ('bs2',105,'melon',8.2), ('t1',102,'banana', 10.3), ('t2',102,'grape', 5.3), ('o2',103,'coconut', 9.2), ('c0',101,'cherry', 3.2), ('a2',103, 'apricot',2.2), ('l2',104,'lemon', 6.4), ('b2',104,'berry', 7.6), ('m1',106,'mango', 15.7), ('m2',105,'xbabay', 2.6), ('t4',107,'xbababa', 3.6), ('m3',105,'xxtt', 11.6), ('b5',107,'xxxx', 3.6);
【实例 1】查询所有价格小于 9 的水果的信息,查询 s_id 等于 101 和 103 的所有水果的信息,使用 UNION 连接查询结果,SQL 语句如下:
SELECT s_id, f_name, f_price FROM fruits WHERE f_price < 9.0 UNION SELECT s_id, f_name, f_price FROM fruits WHERE s_id IN(101,103);查询结果如下:
+--------+-----------+--------+ | s_id | f_name | f_price| +--------+-----------+--------+ | 101 | apple | 5.20 | | 103 | apricot | 2.20 | | 104 | berry | 7.60 | | 107 | xxxx | 3.60 | | 105 | melon | 8.20 | | 101 | cherry | 3.20 | | 104 | lemon | 6.40 | | 105 | xbabay | 2.60 | | 102 | grape | 5.30 | | 107 | xbababa | 3.60 | | 101 | blackberry| 10.20 | | 103 | coconut | 9.20 | +--------+-----------+--------+
如前所述,UNION 将多个 SELECT 语句的结果组合成一个结果集合。可以分开查看每个 SELECT 语句的结果:
SELECT s_id, f_name, f_price FROM fruits WHERE f_price < 9.0; +--------+----------+--------+ | s_id | f_name | f_price| +--------+----------+--------+ | 101 | apple | 5.20 | | 103 | apricot | 2.20 | | 104 | berry | 7.60 | | 107 | xxxx | 3.60 | | 105 | melon | 8.20 | | 101 | cherry | 3.20 | | 104 | lemon | 6.40 | | 105 | xbabay | 2.60 | | 102 | grape | 5.30 | | 107 | xbababa | 3.60 | +--------+----------+--------+
SELECT s_id, f_name, f_price FROM fruits WHERE s_id IN(101,103);
+--------+------------+--------+ | s_id | f_name | f_price| +--------+------------+--------+ | 101 | apple | 5.20 | | 103 | apricot | 2.20 | | 101 | blackberry | 10.20 | | 101 | cherry | 3.20 | | 103 | coconut | 9.20 | +--------+------------+--------+由分开查询的结果可以看到,第一条 SELECT 语句查询价格小于 9 的水果,第二条 SELECT 语句查询 s_id 为 101 和 103 的水果供应商提供的水果。使用 UNION 将两条 SELECT 语句分隔开,执行完毕之后把输出结果组合成单个的结果集,并删除重复的记录。
使用 UNION ALL 会包含重复的行。在【实例 1】中,分开查询时,两个返回结果中有相同的记录,UNION 从查询结果集中自动去除了重复的行。如果要返回所有匹配行,而不进行删除,可以使用 UNION ALL。
【实例 2】查询所有价格小于 9 的水果的信息,查询 s_id 等于 101 和 103 的所有水果的信息,使用 UNION ALL 连接查询结果,SQL语句如下:
SELECT s_id, f_name, f_price FROM fruits WHERE f_price < 9.0 UNION ALL SELECT s_id, f_name, f_price FROM fruits WHERE s_id IN(101,103);查询结果如下:
+--------+------------+--------+ | s_id | f_name | f_price| +--------+------------+--------+ | 101 | apple | 5.20 | | 103 | apricot | 2.20 | | 104 | berry | 7.60 | | 107 | xxxx | 3.60 | | 105 | melon | 8.20 | | 101 | cherry | 3.20 | | 104 | lemon | 6.40 | | 105 | xbabay | 2.60 | | 102 | grape | 5.30 | | 107 | xbababa | 3.60 | | 101 | apple | 5.20 | | 103 | apricot | 2.20 | | 101 | blackberry | 10.20 | | 101 | cherry | 3.20 | | 103 | coconut | 9.20 | +--------+------------+--------+由结果可以看到,这里总的记录数等于两条 SELECT 语句返回的记录数之和,连接查询结果并没有去除重复的行。
使用 UNION ALL 时不会删除重复行,并且执行时需要的资源少。因此,当知道有重复行但是想保留这些行、确定查询结果中不会有重复数据或者不需要去掉重复数据的时候,应当使用 UNION ALL 以提高查询效率。