12 联结表

1.联结

SQL最强大的功能之一就是能在数据查询的执行中联结(join)表。

(1)关系表

关系表的设计是把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相联系。

建立两个表：一个存储供应商信息，另一个存储产品信息。

Vendors表包含所有供应商信息，每个供应商占一行，具有唯一标识，称为主键(primary key)。

Products表只存储产品信息，除了存储供应商ID（Vendors表中的主键），不提供任何其他与存储供应商有关的信息。Vendors表的主键将Vendors表和Products表关联，利用存储供应商ID能从Vendors表中找出相应供应商的详细信息。

(2)为什么使用联结

联结是一种机制，用来在一条SELECT语句中关联表，因此称为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。

2.创建联结

指定要联结的表以及关联它们的方式即可创建联结。

输入：

SELECT vend_name, prod_name, prod_price

FROM Vendors,Products

WHERE Vendors.vend_id = Products.vend_id;

输出：

FROM子句列出了两个表：Vendors和Products，这两个表用WHERE子句正确地联结，WHERE子句指示DBMS匹配Vendors表中的vend_id和Products表中的vend_id。这里需要完全限定列名。

(1)WHERE子句的重要性

WHERE子句作为过滤条件，只包含那些匹配给定条件的行。没有WHERE子句，第一个表中的每一行将与第二个表中的每一行配对，而不管逻辑上是否能匹配到一起。

输入：

SELECT vend_name, prod_name, prod_price

FROM Vendors, Products;

输出：

返回的数据用每个供应商匹配了每个产品，包括供应商不正确的产品，相应的笛卡儿积不是我们想要的。

(2)内联结

等值联结(equijion)：基于两个表之间的相等测试，也称为内联结(inner join)。

输入：

SELECT vend_name, prod_name, prod_price

FROM Vendors INNER JOIN Products

ON Vendors.vend_id = Products.vend_id;

输出：

返回与前面例子完全相同的数据，但这里两个表之间的关系是以INNER JOIN指定的部分FROM子句。使用该语法时，联结条件用特定的ON子句而不是WHERE子句给出，但传递的实际条件相同。

(3)联结多个表

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 = 20007;

输出：

显示订单20007中的物品。订单物品存储在OrderItems表中，每个产品按其ID存储，它引用Products表中的产品。这些产品通过供应商ID联结到Vendors表中相应的供应商，供应商ID存储在每个产品的记录中。

更改11课中的例子

列出订购物品'RGAN01'的所有顾客

(1)检索包含物品RGAN01的所有订单的编号；

(2)检索具有前一步骤列出的订单编号的所有顾客的ID；

(3)检索前一步骤返回的所有顾客ID的顾客信息。

输入：

SELECT cust_name, cust_contact

FROM Customers

WHERE cust_id IN (SELECT cust_id

FROM Orders

WHERE order_num IN (SELECT order_num

FROM OrderItems

WHERE prod_id= 'RGAN01'));

输出：

下面使用联结进行相同查询，输入：

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= 'RGAN01';

输出：