10. 组合查询

本节使用的表

customers

postgres=# \d customers

                    Table "public.customers"
    Column    |      Type      | Collation | Nullable | Default
--------------+----------------+-----------+----------+---------
 cust_id      | character(10)  |           | not null |
 cust_name    | character(50)  |           | not null |
 cust_address | character(50)  |           |          |
 cust_city    | character(50)  |           |          |
 cust_state   | character(5)   |           |          |
 cust_zip     | character(10)  |           |          |
 cust_country | character(50)  |           |          |
 cust_contact | character(50)  |           |          |
 cust_email   | character(255) |           |          |
Indexes:
    "customers_pkey" PRIMARY KEY, btree (cust_id)
Referenced by:
    TABLE "orders" CONSTRAINT "fk_orders_customers" FOREIGN KEY (cust_id) REFERENCES customers(cust_id)

SELECT
    *
FROM
    customers;

 cust_id    | cust_name     | cust_address         | cust_city | cust_state | cust_zip | cust_country | cust_contact
   | cust_email
------------+---------------+----------------------+-----------+------------+----------+--------------+--------------------
---+----------------------------
 1000000001 | Village Toys  | 200 Maple Lane       | Detroit   | MI         | 44444    | USA          | John Smith
   | sales@villagetoys.com
 1000000002 | Kids Place    | 333 South Lake Drive | Columbus  | OH         | 43333    | USA          | Michelle Green
   |
 1000000003 | Fun4All       | 1 Sunny Place        | Muncie    | IN         | 42222    | USA          | Jim Jones
   | jjones@fun4all.com
 1000000004 | Fun4All       | 829 Riverside Drive  | Phoenix   | AZ         | 88888    | USA          | Denise L. Stephens
   | dstephens@fun4all.com
 1000000005 | The Toy Store | 4545 53rd Street     | Chicago   | IL         | 54545    | USA          | Kim Howard
   |
(5 rows)

定义

多数SQL查询只包含从一个或多个表中返回数据的单条SELECT语句。但是，SQL也允许执行多个查询，并将结果作为一个查询结果集返回。这些组合查询通常称为并（union）或复合查询（compound query）。

主要有两种情况需要使用组合查询：

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

多数情况下，组合相同表的两个查询所完成的工作与具有多个WHERE子句条件的一个查询所完成的工作相同。换句话说，任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询。

创建组合查询

需要IL、IN、MI等美国几个州的所有顾客的报表，还想包括不管位于哪个州的所有的Fun4All。

单条语句

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    |
(3 rows)

SELECT
    cust_name,
    cust_contact,
    cust_email
FROM
    customers
WHERE
    cust_name = 'Fun4All';

 cust_name     | cust_contact       | cust_email
---------------+--------------------+------------------------------------------
 Fun4All       | Jim Jone           | jjones@fun4all.com
 Fun4All       | Denise L. Stephens | dstephens@fun4all.com
(2 rows)

多个WHERE

SELECT
    cust_name,
    cust_contact,
    cust_email 
FROM
    customers 
WHERE
    cust_state IN ('IL', 'IN', 'MI')  
    OR cust_name = 'Fun4All';

UNION

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
---------------+--------------------+-----------------------------------------
 Fun4All       | Jim Jones          | jjones@fun4all.com
 The Toy Store | Kim Howard         |
 Fun4All       | Denise L. Stephens | dstephens@fun4all.com
 Village Toys  | John Smith         | sales@villagetoys.com
(4 rows)

包含重复的行

UNION从查询结果中自动去除了重复的行，这是默认的行为。换句话说，它的行为与一条SELECT语句中使用多个WHERE子句条件一样。

可以使用UNION ALL返回所有的匹配行。

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
---------------+--------------------+-----------------------------------------
 Fun4All       | Jim Jones          | jjones@fun4all.com
 The Toy Store | Kim Howard         |
 Fun4All       | Jim Jones          | jjones@fun4all.com
 Fun4All       | Denise L. Stephens | dstephens@fun4all.com
 Village Toys  | John Smith         | sales@villagetoys.com
(5 rows)

UNION ALL为UNION的一种形式，它完成WHERE子句完成不了的工作。

对组合查询结果排序

在用UNION组合查询时，只能使用一条ORDER BY子句，它必须位于最后一条SELECT语句之后。对于结果集，不存在用一种方式排序一部分，而又用另一种方式排序另一部分的情况。

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         |
 Village Toys  | John Smith         | sales@villagetoys.com
(4 rows)

在最后一条SELECT语句后使用了ORDER BY子句。虽然ORDER BY子句似乎只是最后一条SELECT语句的组成部分，但实际上DBMS将用它来排序所有SELECT语句返回的所有结果。

UNION规则

进行组合时需要注意UNION规则：

• UNION必须由两条或以上的SELECT语句组成；
• 每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）；
• 列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型。