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)