MySQL第五章 21、组合查询

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)

union可以使用多次，跨表，夸数据库，远程和本地