两个表匹配关键字_数据库知识总结——(六)联结表

本节用到的表结构如下：

vendors表

products表

orderitems表

一.关系表

来看一个例子，现在有一个产品表，表结构如上图所示。假如有由同一供应商生产的多种物品，在何处存储供应商信息呢？

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

products表只存储产品信息，它除了存储供应商ID外不存储其他供应商信息。vendors表的主键又叫作products的外键，外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。它将vendors表与products表关联，利用供应商ID能从vendors表中找出相应供应商的详细信息。

这样做的好处是：

（一）供应商信息不重复，从而不浪费时间和空间

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

（三）由于数据无重复，显然数据是一致的，使得处理数据更简单。

总而言之，关系数据可以有效地存储和方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好。

二.联结

正如上面所述，分解数据为多个表能更有效地存储，更方便地处理，并且具有更大的可伸缩性。如果该数据存储在多个表中，如何用单条select语句检索出数据？

答案是使用联结。联结是一种机制，用来在一条select中关联表，因此称之为联结。SQL最强大的功能之一是能在数据检索查询的执行中联结(join)表。

（1）创建联结

Select vend_name,prod_name,prod_price
From vendors,products
Where vendors.vend_id = products.vend_id
Order by vend_name,prod_name;

在上面的代码中，Select指定检索列，不过与之前的差别是其中的两个列在products表中，另一个在vendors表中。From指定了两个表，这两个表为select语句联结的两个表名。而Where子句指示MySQL匹配vendors中的vend_id和products中的vend_id，在这里要匹配的两个列使用完全限定列名进行指定。完全限定列名是指在引用的列可能出现二义性时，必须使用完全限定列名(用一个点分隔的表名和列名)。

在一条select语句中联结几个表时，相应的关系是在运行中构造的。在表定义中不存在能指示MySQL如何对表进行联结的东西。在联结两个表时，实际上是将第一个表中的每一行与第二个表中的每一行配对，where子句作为过滤条件，它只包含那些匹配给定条件的行。没有where子句，无论它们逻辑上是否可以配在一起，第一个表中的每个行都将与第二个表中的每个行配对。

上面所用的联结称为等值联结，它基于两个表之间的相等测试，也称为内部联结。也可以用不同的语法返回与前面例子完全相同的数据：

//From中两个表之间的关系通过INNER JOIN指定。
//在使用这种语法时，联结条件用特定的ON子句。

select vend_name,prod_name,prod_price 
from vendors INNER JOIN products
on vendors.vend_id = products.vend_id;

(2)联结多个表

//检索显示订单号为20005的物品信息
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 = 20005;

由于子查询并不总是执行复杂select操作最有效的方法，因此还可以用联结的查询来代替上一节的子查询。形式如下：

//检索订购商品TNT2的客户列表
Select cust_name,cust_contact  
From customers,orders,orderitems
Where customers.cust_id = orders.cust_id
  And orderitems.order_num = orders.order_num
  And prod_id = ‘TNT2’;

三.创建高级联结

本小节所使用的表结构信息如下：

customers表

orderitems表

orders表

（1）使用表别名

别名除了用于列名和计算字段外，SQL还允许给表名起别名。其原因是：

（一）缩短SQL语句

（二）允许在单条select语句中多次使用相同的表。

// From中3个表都有别名，customers建立c作为customers的别名，
// 但是表别名只在查询执行中使用，与列别名不同，表别名不返回到客户机。

Select cust_name,cust_contact
From customers AS c,order AS o, orderitems AS oi
Where c.cust_id = o.cust_id
  And oi.order_num = o.order_num
  And prod_id = ‘TNT2’;

(2)使用不同类型的联结

1.自联结

假设发现某商品(例如DTNTR)存在问题，因此想知道生产该物品的供应商生产的其它商品是否也存在问题。通过子查询可以实现这一检索:

Select prod_id,prod_name
From products
Where vend_id = (select vend_id
                 From products
                 Where prod_id = ‘DTNTR’);

// 内部的select检索返回生产ID为DTNTR商品供应商的vend_id,
// 该ID用于外部查询的where语句，检索出该供应商的所有物品。

还可以使用联结的相同查询检索：

Select p1.prod_id,p1.prod_name
From products as p1,products as p2
Where p1.vend_id = p2.vend_id
And p2.vend_id=’DTNTR’;

此查询中需要的两个表实际上是相同的表，因此products表在FROM子句中出现了两次。虽然这是完全合法的，但对products的引用具有二义性。为解决此问题，使用了表别名，products的第一次出现为别名p1，第二次出现为别名p2。自联结通常作为外部语句用来替代从相同表中检索数据时使用的子查询语句。

2.自然联结

无论何时对表进行联结，应该至少有一个列出现在不止一个表中。标准的联结返回所有数据，甚至相同的列多次出现。自然联结排除多次出现，使每个列只返回一次。通过对表使用通配符(SELECT *)，对所有其他表的列使用明确的子集完成。例如

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 = ‘FB’;

3.外部联结

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

内部联结与外部联结的区别：

//内部联结：检索所有顾客及其订单
Select customers.cust_id,orders.order_num
From customers inner join orders
On customers.cust_id = orders.cust_id;

//外部联结：检索所有顾客，包括那些没有订单的顾客。
Select customers.cust_id, orders.order_num
From customers left outer join orders
On customers.cust_id = orders.cust_id;

Select语句使用了关键字outer join来指定联结的类型，而且外部联结还包括没有关联行的行。在使用outer join语法时，必须使用right或left关键字指定包括其所有行的表（right指出outer join右边的表，left指出是outer join左边的表）。上面使用left outer join从from子句的左边表中选择所有行。

内部联结(上)与左外部联结(下)的输出

存在两种基本的外部联结形式：左外部联结和右外部联结。它们之间的唯一差别是所关联的表的顺序不同。上面是左外部联结，再来看一个右外部联结的例子。

Select customers.cust_id, orders.order_num
From customers right outer join orders
On customers.cust_id = orders.cust_id;

4.使用带聚集函数的联结

//检索所有客户及每个客户所下订单数
Select c.cust_name,c.cust_id,count(o.order_num) as num_ord
From customers as c inner join orders as o
On c.cust_id=o.cust_id
Group by c.cust_id;
// Select语句使用inner join将customers和orders互相关联。
// Group by按客户分组数据，count()函数调用对每个客户的订单计数，作为num_ord返回。


//该例使用左外部联结来包含所有客户，甚至包含那些没有下任何订单的客户。
Select c.cust_name,c.cust_id,count(o.order_num) as num_ord
From customers as c left outer join orders as o
On c.cust_id=o.cust_id
Group by c.cust_id;

上面使用聚合函数例子的结果

5.使用联结和联结条件

注意所使用的联结类型。一般使用内部联结，但使用外部联结也是有效的。

（一）保证使用正确的联结条件，否则将返回不正确的数据。

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

（三）在一个联结中可以包含多个表，甚至对于每个联结可以采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前，分别测试每个联结。这将使故障排除更为简单。

参考书籍：《MySQL必知必会》