七、联结表

1.关系表概述

背景： 假如有由同一供应商生产的多种物品，应当分为多个表存储：

• 因为同一供应商生产的每个产品的供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间。
• 如果供应商信息改变（例如，供应商搬家或电话号码变动），只需改动一次即可。
• 如果有重复数据（即每种产品都存储供应商信息），很难保证每次输入该数据的方式都相同。不一致的数据在报表中很难利用。

分为两个表存储：

(1) product表存储商品信息和供应商ID。关于供应商的信息除了ID外不在product表中存储其它的。

(2) vendors 表包含所有供应商信息，每个供应商占一行，每个供应商具有唯一的标识。此标识称为主键。在这里是供应商ID。

现在我们就可以通过product表中的商品获得供应商ID， 然后再vendors表中找到供应商信息。

select vend_name,prod_name from vendors,products where vendors.vend_id = products.vend_id;

我们通过一个where语句来联结两个表。这里我们的id都用完全限定，即表名.列名，否则会出现二义性，不知道是哪个表的字段。

where的重要性：

如上图所述，我们进行联结的时候是将第一个表中的每一行与第二个表中的每一行配对。where进行过滤。

笛卡尔积：

由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。

上述基于两个表之间的相等测试。这种联结也称为内部联结。

2. 内联结。

select vend_name,prod_name,prod_price 
from vendors 
inner join products 
on vendors.vend_id = products.vend_id;

使用 inner join table_name on conditions来联结table_name这个表，限制条件是conditions. 使用on来代替where子句。

也可以联结多个表：

select prod_name,vend_name,prod_price,quantity  
from orderitems,vendors,products 
where products.vend_id = vendors.vend_id 
and ordeeritems.prod_id = products.prod_id  
and order_num = 20005;

过滤出订单编号为20005的数据。

联结的表越多，性能下降越厉害。

3. 自联结。

首先说一下表别名。

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_.order_num 
and prod_id = 'TNT2';

如此更便捷。

tips：应该注意，表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户机。

使用自联结的时候要用到表别名。例子：在一个商品表中，一个商品ID为'DTNTR'的商品有问题，现在要找到它对应生产厂商所生产的所有商品。

我们分两步来检索:

(1) 通过商品ID在表中检索出厂家ID。
(2) 通过厂家ID检索出所有这个厂家的商品。

如果使用子查询是这样的：

select prod_name,prod_id from products
where vend_id = 
(select vend_id from products 
where prod_id = 'DTNTR');

但是我们可以使用自联结:

mysql> select p1.prod_name, p2.prod_id from products as p1, products as p2
-> where p1.prod_id = 'DTNTR' 
-> and p2.vend_id = p1.vend_id;

注意我们使用的是p1.prod_name而不是prod_name。

4.外联结

当我们使用内联结时，找到的都是等值匹配中存在的数据。若我们要查找用户的订单数量，没有订单的也要显示。

使用内联结：

mysql> select customers.cust_id,orders.order_num
-> from customers inner join orders
-> on customers.cust_id = orders.cust_id;

输出结果如下：

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
5 rows in set (0.00 sec)

很明显我们没有把订单数量为空的cust_id找出来。

这个时候使用外联结：

mysql> select customers.cust_id,orders.order_num
-> from customers left outer join orders
-> on customers.cust_id = orders.cust_id;

输出如下：

+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10002 |      NULL |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+
6 rows in set (0.00 sec)

可以看到我们把order_num为空的一起检索出来了。

现在学习一下外联结的语法。

• 关键字：OUTER JOIN. outer join来指定外联结，外联结不仅包含有关系的行，还包括没有关系的行。
• 关键字：LEFT或者RIGHT. RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT指出的是 OUTER JOIN 左边的表。也就是从哪个表检索没有关系的行。

5. 聚集函数和联结表的结合使用

以cust_id为分组，检索customers和orders表中cust_id相同的数据(包括名字，id，和总数)。

mysql> select customers.cust_name,customers.cust_id,count(orders.order_num) as num_ord
-> from customers inner join orders
-> on customers.cust_id = orders.cust_id
-> group by customers.cust_id;

结果为：

+----------------+---------+---------+
| cust_name      | cust_id | num_ord |
+----------------+---------+---------+
| Coyote Inc.    |   10001 |       2 |
| Wascals        |   10003 |       1 |
| Yosemite Place |   10004 |       1 |
| E Fudd         |   10005 |       1 |
+----------------+---------+---------+
4 rows in set (0.00 sec)

6. 注意点。

应该总是提供联结条件，否则会得出笛卡儿积。