MySQL第五章 21、组合查询-优快云博客

本文探讨了如何在MySQL中通过单个查询和联合查询返回类似结果，包括使用UNION和UNION ALL的区别，排序以及跨表查询的效率。特别关注了自动去重功能的开启与关闭对查询性能的影响。

1、单个查询从不同的表中返回类似的结果
单个表执行多个查询，按照单个查询结果返回

mysql> select vend_id, prod_id from products where prod_price <= 5;
+---------+---------+
| vend_id | prod_id |
+---------+---------+
|    1003 | FC      |
|    1002 | FU1     |
|    1003 | SLING   |
|    1003 | TNT1    |
+---------+---------+
4 rows in set (0.06 sec)

mysql> select vend_id, prod_id from products where vend_id in ('1001', '1005');
+---------+---------+
| vend_id | prod_id |
+---------+---------+
|    1001 | ANV01   |
|    1001 | ANV02   |
|    1001 | ANV03   |
|    1005 | JP1000  |
|    1005 | JP2000  |
+---------+---------+
5 rows in set (2.39 sec)

union查询，带有自动去重功能

mysql> select vend_id, prod_id from products where prod_price <= 5 union select vend_id, prod_id from products where vend_id in ('1001', '1005');
+---------+---------+
| vend_id | prod_id |
+---------+---------+
|    1003 | FC      |
|    1002 | FU1     |
|    1003 | SLING   |
|    1003 | TNT1    |
|    1001 | ANV01   |
|    1001 | ANV02   |
|    1001 | ANV03   |
|    1005 | JP1000  |
|    1005 | JP2000  |
+---------+---------+
9 rows in set (0.06 sec)

通过特殊开关，关闭自动去重功能 union all

mysql> select vend_id, prod_id from products where prod_price <= 5 union all select vend_id, prod_id from products where prod_price <= 5;
+---------+---------+
| vend_id | prod_id |
+---------+---------+
|    1003 | FC      |
|    1002 | FU1     |
|    1003 | SLING   |
|    1003 | TNT1    |
|    1003 | FC      |
|    1002 | FU1     |
|    1003 | SLING   |
|    1003 | TNT1    |
+---------+---------+
8 rows in set (0.65 sec)

排序

mysql> select vend_id, prod_id from products where prod_price <= 5 union select vend_id, prod_id from products where vend_id in ('1001', '1005') order by vend_id, prod_id;
+---------+---------+
| vend_id | prod_id |
+---------+---------+
|    1001 | ANV01   |
|    1001 | ANV02   |
|    1001 | ANV03   |
|    1002 | FU1     |
|    1003 | FC      |
|    1003 | SLING   |
|    1003 | TNT1    |
|    1005 | JP1000  |
|    1005 | JP2000  |
+---------+---------+
9 rows in set (0.08 sec)