读书笔记--SQL必知必会14--组合查询

14.1 组合查询

复合查询（compound query）或并（union），SQL允许执行多个查询（多条SELECT语句），并将结果作为一个查询结果集返回。
应用场景：

• 在一个查询中从不同的表返回结构数据
• 对一个表执行多个查询，按一个查询返回数据

多数情况下，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询。

14.2 创建组合查询

利用UNION操作符来组合多条SELECT语句。
对于较复杂的过滤条件，或者从多个表中检索数据的情景，使用UNION可能会使处理更简单。

14.2.1 使用UNION

MariaDB [sqlbzbh]> SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL', 'IN', 'MI');
+---------------+--------------+-----------------------+
| cust_name     | cust_contact | cust_email            |
+---------------+--------------+-----------------------+
| Village Toys  | John Smith   | sales@villagetoys.com |
| Fun4All       | Jim Jones    | jjones@fun4all.com    |
| The Toy Store | Kim Howard   | NULL                  |
+---------------+--------------+-----------------------+
3 rows in set (0.00 sec)

MariaDB [sqlbzbh]> 
MariaDB [sqlbzbh]> SELECT cust_name, cust_contact,cust_email FROM Customers WHERE cust_name = 'Fun4All';
+-----------+--------------------+-----------------------+
| cust_name | cust_contact       | cust_email            |
+-----------+--------------------+-----------------------+
| Fun4All   | Jim Jones          | jjones@fun4all.com    |
| Fun4All   | Denise L. Stephens | dstephens@fun4all.com |
+-----------+--------------------+-----------------------+
2 rows in set (0.00 sec)

MariaDB [sqlbzbh]> 
MariaDB [sqlbzbh]> SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL', 'IN', 'MI')
    -> UNION
    -> SELECT cust_name, cust_contact,cust_email FROM Customers WHERE cust_name = 'Fun4All';
+---------------+--------------------+-----------------------+
| cust_name     | cust_contact       | cust_email            |
+---------------+--------------------+-----------------------+
| Village Toys  | John Smith         | sales@villagetoys.com |
| Fun4All       | Jim Jones          | jjones@fun4all.com    |
| The Toy Store | Kim Howard         | NULL                  |
| Fun4All       | Denise L. Stephens | dstephens@fun4all.com |
+---------------+--------------------+-----------------------+
4 rows in set (0.00 sec)

MariaDB [sqlbzbh]> 
MariaDB [sqlbzbh]> SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL', 'IN', 'MI') OR cust_name = 'Fun4All';
+---------------+--------------------+-----------------------+
| cust_name     | cust_contact       | cust_email            |
+---------------+--------------------+-----------------------+
| Village Toys  | John Smith         | sales@villagetoys.com |
| Fun4All       | Jim Jones          | jjones@fun4all.com    |
| Fun4All       | Denise L. Stephens | dstephens@fun4all.com |
| The Toy Store | Kim Howard         | NULL                  |
+---------------+--------------------+-----------------------+
4 rows in set (0.00 sec)

MariaDB [sqlbzbh]> 

14.2.2 UNION规则

• 必须由两条或两条以上的SELECT语句组成，之间用关键字UNION分隔
• 每个查询必须包含相同的列、表达式或聚集函数（次序可以不同）
• 列数据类型可以不同，但必须兼容

14.2.3 包含或取消重复的行

UNION默认自动从查询结果集中去除了重复的行。
如果想要返回所有匹配的行，可使用UNION ALL，不能使用WHERE。

MariaDB [sqlbzbh]> SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL', 'IN', 'MI')
    -> UNION ALL
    -> SELECT cust_name, cust_contact,cust_email FROM Customers WHERE cust_name = 'Fun4All';
+---------------+--------------------+-----------------------+
| cust_name     | cust_contact       | cust_email            |
+---------------+--------------------+-----------------------+
| Village Toys  | John Smith         | sales@villagetoys.com |
| Fun4All       | Jim Jones          | jjones@fun4all.com    |
| The Toy Store | Kim Howard         | NULL                  |
| Fun4All       | Jim Jones          | jjones@fun4all.com    |
| Fun4All       | Denise L. Stephens | dstephens@fun4all.com |
+---------------+--------------------+-----------------------+
5 rows in set (0.00 sec)

MariaDB [sqlbzbh]> 

14.2.4 对组合查询结果排序

使用UNION组合查询时，只能使用一条ORDER BY子句，而且必须位于最后一条SELECT语句之后。
ORDER BY子句排序所有SELECT语句返回的所有结果。

MariaDB [sqlbzbh]> SELECT cust_name, cust_contact, cust_email FROM Customers WHERE cust_state IN ('IL', 'IN', 'MI')
    -> UNION
    -> SELECT cust_name, cust_contact,cust_email FROM Customers WHERE cust_name = 'Fun4All'
    -> ORDER BY cust_name, cust_contact;
+---------------+--------------------+-----------------------+
| cust_name     | cust_contact       | cust_email            |
+---------------+--------------------+-----------------------+
| Fun4All       | Denise L. Stephens | dstephens@fun4all.com |
| Fun4All       | Jim Jones          | jjones@fun4all.com    |
| The Toy Store | Kim Howard         | NULL                  |
| Village Toys  | John Smith         | sales@villagetoys.com |
+---------------+--------------------+-----------------------+
4 rows in set (0.00 sec)

MariaDB [sqlbzbh]>