数据库操作：联结表join

假如有一个包含产品目录的数据库表，其中每种类别的物品占一行。对于每种物品要存储的信息包含产品描述和价格，以及生产该产品的供应商信息。

现在，假如有由同一供应商生产的多种物品，那么在何处存储供应商信息（如供应商名、地址、联系方法等）呢？

因为同一供应商生产的每个产品的供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间。

如果供应商信息改变，只需改动一次即可。

如果有重复数据，很难保证每次输入该数据的方式都相同。

关键是，相同数据出现多次决不是一件好事，此因素是关系数据库设计的基础。关系表的设计就是要保证把信息分解成多个表，一类数据一个表。各表通过某些常用的值互相关联。

在这个例子中，可以建立两个表，一个存储供应商信息，另一个存储产品信息。vendors表包含所有供应商信息，每个供应商占一行，每个供应商具有唯一的标识。此标识称为主键（primary key），可以是供应商ID或任何其他唯一值。

products表存储产品信息，除了存储供应商ID（vendors表的主键）外不存储其他供应商信息。

vendors表的主键又叫做products表的外键，它将两表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息。

外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

好处如下：

1.供应商信息不重复，从而不浪费时间和空间；

2.如果供应商信息变动，可以只更新vendors表中的单个记录，相关表中的数据不用改动；

3.由于数据的无重复，显然数据是一致的，这使得处理数据更简单。

正如所述，分解数据为多个表能更有效地存储，更方便地处理，并且具有更大的可伸缩性。

 

如果数据存储在多个表中，怎么用单条SELECT语句检索出数据？

答案是使用联结。简单地说，联结是一种机制，用来在一条SELECT语句中关联表。

 

SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id=products.vend_id
ORDER BY vend_name,prod_name;

prod_price和prod_name在一个表中，而vend_name在另一个表中。WHERE子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id。可以看到匹配的两个列以vendors.vend_id和products.vend_id指定。这里需要这种完全限定列名，如果只给出vend_id，则不知道指的是哪一个。

联结两个表时，实际上做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件，它只包含那些匹配给定条件（这里是联结条件）的行。

没有WHERE子句，第一个表中的每一行将于与第二个表中的每个行配对，而不管它们逻辑上是否可以配在一起。

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

SELECT vend_name,prod_name,prod_price
FROM vendors,products
ORDER BY vend_name,prod_name;

目前为止所用的联结称为等值联结，它基于两个表之间的相等测试。这种联结也称为内部联结。

下面的SELECT语句返回与前面例子完全相同的数据。

SELECT vend_name,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id=products.vend_id；

这里，两个表之间的关系是FROM子句的组成部分，以INNER JOIN制订。联结条件用ON子句而不是WHERE子句。传递给ON的实际条件与传递给WHERE的相同。

 

SQL对一条SELECT语句中可以联结的表的数目没有限制。

SELECT prod_name,vend_name,prod_price,quantity
FROM orderitems,products,vendors
WHERE products.vend_id=vendors.vend_id
AND orderitems.prod_id=products.prod_id
AND order_num=2005;

此例子显示编号为2005的订单中的物品。