9. 创建高级联结

本节使用的表

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)

orders

postgres=# \d orders

                    Table "public.orders"
   Column   |     Type      | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
 order_num  | integer       |           | not null |
 order_date | date          |           | not null |
 cust_id    | character(10) |           | not null |
Indexes:
    "orders_pkey" PRIMARY KEY, btree (order_num)
Foreign-key constraints:
    "fk_orders_customers" FOREIGN KEY (cust_id) REFERENCES customers(cust_id)
Referenced by:
    TABLE "orderitems" CONSTRAINT "fk_orderitems_orders" FOREIGN KEY (order_num) REFERENCES orders(order_num)

SELECT
    *
FROM
    orders;

 order_num | order_date |  cust_id
-----------+------------+------------
     20005 | 2012-05-01 | 1000000001
     20006 | 2012-01-12 | 1000000003
     20007 | 2012-01-30 | 1000000004
     20008 | 2012-02-03 | 1000000005
     20009 | 2012-02-08 | 1000000001
(5 rows)

orderitems

postgres=# \d orderitems

                  Table "public.orderitems"
   Column   |     Type      | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
 order_num  | integer       |           | not null |
 order_item | integer       |           | not null |
 prod_id    | character(10) |           | not null |
 quantity   | integer       |           | not null |
 item_price | numeric(8,2)  |           | not null |
Indexes:
    "orderitems_pkey" PRIMARY KEY, btree (order_num, order_item)
Foreign-key constraints:
    "fk_orderitems_orders" FOREIGN KEY (order_num) REFERENCES orders(order_num)
    "fk_orderitems_products" FOREIGN KEY (prod_id) REFERENCES products(prod_id)

SELECT
    *
FROM
    orderitems;

 order_num | order_item |  prod_id   | quantity | item_price
-----------+------------+------------+----------+------------
     20005 |          1 | BR01       |      100 |       5.49
     20005 |          2 | BR03       |      100 |      10.99
     20006 |          1 | BR01       |       20 |       5.99
     20006 |          2 | BR02       |       10 |       8.99
     20006 |          3 | BR03       |       10 |      11.99
     20007 |          1 | BR03       |       50 |      11.49
     20007 |          2 | BNBG01     |      100 |       2.99
     20007 |          3 | BNBG02     |      100 |       2.99
     20007 |          4 | BNBG03     |      100 |       2.99
     20007 |          5 | RGAN01     |       50 |       4.49
     20008 |          1 | RGAN01     |        5 |       4.99
     20008 |          2 | BR03       |        5 |      11.99
     20008 |          3 | BNBG01     |       10 |       3.49
     20008 |          4 | BNBG02     |       10 |       3.49
     20008 |          5 | BNBG03     |       10 |       3.49
     20009 |          1 | BNBG01     |      250 |       2.49
     20009 |          2 | BNBG02     |      250 |       2.49
     20009 |          3 | BNBG03     |      250 |       2.49
(18 rows)

使用表别名

SELECT
    cust_name,
    cust_contact 
FROM
    customers AS C,
    orders AS O,
    orderitems AS OI 
WHERE
    C.cust_id = O.cust_id  
    AND OI.order_num = O.order_num  
    AND prod_id = 'RGAN01';

 cust_name                      | cust_contact
--------------------------------+----------------------------------------
 Fun4All                        | Denise L. Stephens
 The Toy Store                  | Kim Howard
(2 rows)

表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户端。

自然联结

无论何时对表进行联结，应该至少有一列不止出现在一个表中（被联结的列）。标准的联结返回所有数据，相同的列甚至多次出现。

自然联结排除多次出现，使每一列只返回一次。自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符，而对其他表的列使用明确的子集来完成。

SELECT
    C.*,
    O.order_num,
    O.order_date,
    OI.prod_id,
    OI.quantity  ,
    OI.item_price 
FROM
    customers AS C,
    orders AS O,
    orderitems AS OI 
WHERE
    C.cust_id = O.cust_id  
    AND OI.order_num = O.order_num  
    AND prod_id = 'RGAN01';

 cust_id   | cust_name      | cust_address        | cust_city | cust_state
   | cust_zip | cust_country | cust_contact       | cust_email
   | order_num | order_date | prod_id | quantity | item_price
-----------+----------------+---------------------+-----------+-----------
---+----------+--------------+--------------------+-----------------------
---+-----------+------------+---------+----------+--------------------------------

 1000000004 | Fun4All       | 829 Riverside Drive | Phoenix   | AZ
   | 88888    | USA          | Denise L. Stephens | dstephens@fun4all.com
   | 20007     | 2012-01-30 | RGAN01  |       50 |       4.49
 1000000005 | The Toy Store | 4545 53rd Street    | Chicago   | IL
   | 54545    | USA          | Kim Howard         |                        
   | 20008     | 2012-02-03 | RGAN01  |        5 |       4.99
(2 rows)

在这个例子中，通配符只对第一个表使用。所有其他列明确列出，所以没有重复的列被检索出来。

事实上，我们迄今为止建立的每个内联结都是自然联结，很可能永远都不会用到不是自然联结的内联结。

自联结

假如要找出与Jim Jones同一公司的所有顾客，那么首先要求找出Jim Jones工作的公司，然后找出在该公司工作的顾客。

子查询的方法

SELECT
    cust_id,
    cust_name,
    cust_contact 
FROM
    customers 
WHERE
    cust_name = (
        SELECT
            cust_name  
        FROM
            customers  
        WHERE
            cust_contact = 'Jim Jones' 
    );
                   
  cust_id   | cust_name     |  cust_contact
------------+---------------+------------------------------------------
 1000000003 | Fun4All       | Jim Jones
 1000000004 | Fun4All       | Denise L. Stephens
(2 rows)

自联结的方法

SELECT
    C1.cust_id,
    C1.cust_name,
    C1.cust_contact
FROM
    customers AS C1,
    customers AS C2
WHERE
    C1.cust_name = C2.cust_name
    AND C2.cust_contact = 'Jim Jones';
  
  cust_id   |                     cust_name                      |                    cust_contact
------------+----------------------------------------------------+----------------------------------------------------
 1000000003 | Fun4All                                            | Jim Jones
 1000000004 | Fun4All                                            | Denise L. Stephens
(2 rows)

SELECT语句使用C1前缀明确给出所需列的全名，如果不这样，DBMS将返回错误，因为名为cust_id等的列各有两个。DBMS不知道要的哪一列（即使它们其实是同一列）。

自联结通常作为外部语句，用来替代从相同表中检索数据的子查询语句。许多DBMS处理联结远比处理子查询快。应该试一下两种方法，以确定哪一种的性能更好。

外联结

许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行，这种联结成为外联结。

例如要检索所有顾客及其订单（包括没有订单的顾客）：

SELECT
    customers.cust_id,
    orders.order_num 
FROM
    customers LEFT JOIN orders 
    ON customers.cust_id = orders.cust_id;

  cust_id   | order_num
------------+-----------
 1000000001 |     20005
 1000000003 |     20006
 1000000004 |     20007
 1000000005 |     20008
 1000000001 |     20009
 1000000002 |
(6 rows)

在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是右边的表，而LEFT指出的是左边的表）。

SELECT
    customers.cust_id,
    orders.order_num
FROM
    customers RIGHT OUTER JOIN orders
    ON customers.cust_id = orders.cust_id;

  cust_id   | order_num
------------+-----------
 1000000001 |     20005
 1000000003 |     20006
 1000000004 |     20007
 1000000005 |     20008
 1000000001 |     20009
(5 rows)

还存在另一种外联结，就是全外联结FULL OUTER JOIN。它检索两个表中的所有行并关联那些可以关联的行，还包括不关联的行。

使用带聚集函数的联结

检索所有顾客及每个顾客所下的订单数：

SELECT
    customers.cust_id,
    COUNT(order_num) AS num_ord 
FROM
    customers INNER JOIN orders
    ON customers.cust_id = orders.cust_id 
GROUP BY
    customers.cust_id;

  cust_id   | num_ord
------------+---------
 1000000001 |       2
 1000000003 |       1
 1000000005 |       1
 1000000004 |       1
(4 rows)

SELECT
    customers.cust_id,
    COUNT(order_num) AS num_ord
FROM
    customers LEFT OUTER JOIN orders
    ON customers.cust_id = orders.cust_id
GROUP BY
    customers.cust_id;

  cust_id   | num_ord
------------+---------
 1000000002 |       0
 1000000001 |       2
 1000000003 |       1
 1000000005 |       1
 1000000004 |       1
(5 rows)