第12课 SQL入门之联结表
这一课会介绍什么是联结，为什么使用联结，如何编写使用联结的SELECT语句。
12.1 联结
SQL最强大的功能之一就是能在数据查询的执行中联结（join）表。联结是利用SQL的SELECT能执行的最重要的操作，很好地理解联结及其语法是学习SQL的极为重要的部分。
在能够有效地使用联结前，必须了解关系表以及关系数据库设计的一些基础知识。下面的介绍并不能涵盖这一主题的所有内容，但作为入门已经够了。
12.1.1 关系表
理解关系表，最好是来看个例子。
有一个包含产品目录的数据库表，其中每类物品占一行。对于每一种物品，要存储的信息包括产品描述、价格，以及生产该产品的供应商。
现在有同一供应商生产的多种物品，那么在何处存储供应商名、地址、联系方法等供应商信息呢？将这些数据与产品信息分开存储的理由是：
- 同一供应商生产的每个产品，其供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间；
- 如果供应商信息发生变化，例如供应商迁址或电话号码变动，只需修改一次即可；
- 如果有重复数据（即每种产品都存储供应商信息），则很难保证每次输入该数据的方式都相同。不一致的数据在报表中就很难利用。
关键是，相同的数据出现多次决不是一件好事，这是关系数据库设计的基础。关系表的设计就是要把信息分解成多个表，一类数据一个表。各表通过某些共同的值互相关联（所以才叫关系数据库）。
在这个例子中可建立两个表：一个存储供应商信息，另一个存储产品信息。Vendors表包含所有供应商信息，每个供应商占一行，具有唯一的标识。此标识称为主键（primary key），可以是供应商ID或任何其他唯一值。
Products表只存储产品信息，除了存储供应商ID（Vendors表的主键）外，它不存储其他有关供应商的信息。Vendors表的主键将Vendors表与Products表关联，利用供应商ID能从Vendors表中找出相应供应商的详细信息。
这样做的好处是： - 供应商信息不重复，不会浪费时间和空间；
- 如果供应商信息变动，可以只更新Vendors表中的单个记录，相关表中的数据不用改动；
- 由于数据不重复，数据显然是一致的，使得处理数据和生成报表更简单。
总之，关系数据可以有效地存储，方便地处理。因此，关系数据库的可伸缩性远比非关系数据库要好。
可伸缩（scale）
能够适应不断增加的工作量而不失败。设计良好的数据库或应用程序称为可伸缩性好（scale well）。
12.1.2 为什么使用联结
如前所述，将数据分解为多个表能更有效地存储，更方便地处理，并且可伸缩性更好。但这些好处是有代价的。
如果数据存储在多个表中，怎样用一条SELECT语句就检索出数据呢？
答案是使用联结。简单说，联结是一种机制，用来在一条SELECT语句中关联表，因此称为联结。使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行。
说明：使用交互式DBMS工具
重要的是，要理解联结不是物理实体。换句话说，它在实际的数据库表中并不存在。DBMS会根据需要建立联结，它在查询执行期间一直存在。
许多DBMS提供图形界面，用来交互式地定义表关系。这些工具极其有助于维护引用完整性。在使用关系表时，仅在关系列中插入合法数据是非常重要的。回到这里的例子，如果Products表中存储了无效的供应商ID，则相应的产品不可访问，因为它们没有关联到某个供应商。为避免这种情况发生，可指示数据库只允许在Products表的供应商ID列中出现合法值（即出现在Vendors表中的供应商）。引用完整性表示DBMS强制实施数据完整性规则。这些规则一般由提供了界面的DBMS管理。
12.2 创建联结
创建联结非常简单，指定要联结的所有表以及关联它们的方式即可。请看下面的例子：
输入▼
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
输出▼
| vend_name | prod_name | prod_price |
|---|---|---|
| Doll House Inc. | Fish bean bag toy | 3.4900 |
| Doll House Inc. | Bird bean bag toy | 3.4900 |
| Doll House Inc. | Rabbit bean bag toy | 3.4900 |
| Bears R Us | 8 inch teddy bear | 5.9900 |
| Bears R Us | 12 inch teddy bear | 8.9900 |
| Bears R Us | 18 inch teddy bear | 11.9900 |
| Doll House Inc. | Raggedy Ann | 4.9900 |
| Fun and Games | King doll | 9.4900 |
| Fun and Games | Queen doll | 9.4900 |
分析▼
我们来看这段代码。SELECT语句与前面所有语句一样指定要检索的列。这里最大的差别是所指定的两列（prod_name和prod_price）在一个表中，而第三列（vend_name）在另一个表中。
现在来看FROM子句。与以前的SELECT语句不一样，这条语句的FROM子句列出了两个表：Vendors和Products。它们就是这条SELECT语句联结的两个表的名字。这两个表用WHERE子句正确地联结，WHERE子句指示DBMS将Vendors表中的vend_id与Products表中的vend_id匹配起来。
可以看到，要匹配的两列指定为Vendors.vend_id和Products.vend_id。这里需要这种完全限定列名，如果只给出vend_id，DBMS就不知道指的是哪一个（每个表中有一个）。从前面的输出可以看到，一条SELECT语句返回了两个不同表中的数据。
警告：完全限定列名
就像前一课提到的，在引用的列可能出现歧义时，必须使用完全限定列名（用一个句点分隔表名和列名）。如果引用一个没有用表名限制的具有歧义的列名，大多数DBMS会返回错误。
12.2.1 WHERE子句的重要性
使用WHERE子句建立联结关系似乎有点奇怪，但实际上是有个很充分的理由的。要记住，在一条SELECT语句中联结几个表时，相应的关系是在运行中构造的。在数据库表的定义中没有指示DBMS如何对表进行联结的内容。你必须自己做这件事情。在联结两个表时，实际要做的是将第一个表中的每一行与第二个表中的每一行配对。WHERE子句作为过滤条件，只包含那些匹配给定条件（这里是联结条件）的行。没有WHERE子句，第一个表中的每一行将与第二个表中的每一行配对，而不管它们逻辑上是否能配在一起。
笛卡儿积（cartesian product）
由没有联结条件的表关系返回的结果为笛卡儿积。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
理解这一点，请看下面的SELECT语句及其输出：
输入▼
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products;
输出▼
| vend_name | prod_name | prod_price |
|---|---|---|
| Bears R Us | 8 inch teddy bear | 5.99 |
| Bears R Us | 12 inch teddy bear | 8.99 |
| Bears R Us | 18 inch teddy bear | 11.99 |
| Bears R Us | Fish bean bag toy | 3.49 |
| Bears R Us | Bird bean bag toy | 3.49 |
| Bears R Us | Rabbit bean bag toy | 3.49 |
| Bears R Us | Raggedy Ann | 4.99 |
| Bears R Us | King doll | 9.49 |
| Bears R Us | Queen doll | 9.49 |
| Bear Emporium | 8 inch teddy bear | 5.99 |
| Bear Emporium | 12 inch teddy bear | 8.99 |
| Bear Emporium | 18 inch teddy bear | 11.99 |
| Bear Emporium | Fish bean bag toy | 3.49 |
| Bear Emporium | Bird bean bag toy | 3.49 |
| Bear Emporium | Rabbit bean bag toy | 3.49 |
| Bear Emporium | Raggedy Ann | 4.99 |
| Bear Emporium | King doll | 9.49 |
| Bear Emporium | Queen doll | 9.49 |
| Doll House Inc. | 8 inch teddy bear | 5.99 |
| Doll House Inc. | 12 inch teddy bear | 8.99 |
| Doll House Inc. | 18 inch teddy bear | 11.99 |
| Doll House Inc. | Fish bean bag toy | 3.49 |
| Doll House Inc. | Bird bean bag toy | 3.49 |
| Doll House Inc. | Rabbit bean bag toy | 3.49 |
| Doll House Inc. | Raggedy Ann | 4.99 |
| Doll House Inc. | King doll | 9.49 |
| Doll House Inc. | Queen doll | 9.49 |
| Furball Inc. | 8 inch teddy bear | 5.99 |
| Furball Inc. | 12 inch teddy bear | 8.99 |
| Furball Inc. | 18 inch teddy bear | 11.99 |
| Furball Inc. | Fish bean bag toy | 3.49 |
| Furball Inc. | Bird bean bag toy | 3.49 |
| Furball Inc. | Rabbit bean bag toy | 3.49 |
| Furball Inc. | Raggedy Ann | 4.99 |
| Furball Inc. | King doll | 9.49 |
| Furball Inc. | Queen doll | 9.49 |
| Fun and Games | 8 inch teddy bear | 5.99 |
| Fun and Games | 12 inch teddy bear | 8.99 |
| Fun and Games | 18 inch teddy bear | 11.99 |
| Fun and Games | Fish bean bag toy | 3.49 |
| Fun and Games | Bird bean bag toy | 3.49 |
| Fun and Games | Rabbit bean bag toy | 3.49 |
| Fun and Games | Raggedy Ann | 4.99 |
| Fun and Games | King doll | 9.49 |
| Fun and Games | Queen doll | 9.49 |
| Jouets et ours | 8 inch teddy bear | 5.99 |
| Jouets et ours | 12 inch teddy bear | 8.99 |
| Jouets et ours | 18 inch teddy bear | 11.99 |
| Jouets et ours | Fish bean bag toy | 3.49 |
| Jouets et ours | Bird bean bag toy | 3.49 |
| Jouets et ours | Rabbit bean bag toy | 3.49 |
| Jouets et ours | Raggedy Ann | 4.99 |
| Jouets et ours | King doll | 9.49 |
| Jouets et ours | Queen doll | 9.49 |
分析▼
从上面的输出可以看到，相应的笛卡儿积不是我们想要的。这里返回的数据用每个供应商匹配了每个产品，包括了供应商不正确的产品（即使供应商根本就没有产品）。
警告：不要忘了WHERE子句
要保证所有联结都有WHERE子句，否则DBMS将返回比想要的数据多得多的数据。同理，要保证WHERE子句的正确性。不正确的过滤条件会导致DBMS返回不正确的数据。
提示：叉联结
有时，返回笛卡儿积的联结，也称叉联结（cross join）。
12.2.2 内联结
目前为止使用的联结称为等值联结（equijoin），它基于两个表之间的相等测试。这种联结也称为内联结（inner join）。其实，可以对这种联结使用稍微不同的语法，明确指定联结的类型。下面的SELECT语句返回与前面例子完全相同的数据：
输入▼
SELECT vend_name, prod_name, prod_price
FROM Vendors
INNER JOIN Products ON Vendors.vend_id = Products.vend_id;
分析▼
此语句中的SELECT与前面的SELECT语句相同，但FROM子句不同。这里，两个表之间的关系是以INNER JOIN指定的部分FROM子句。在使用这种语法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。
至于选用哪种语法，请参阅具体的DBMS文档。
说明：“正确的”语法
ANSI SQL规范首选INNER JOIN语法，之前使用的是简单的等值语法。其实，SQL语言纯正论者是用鄙视的眼光看待简单语法的。这就是说，DBMS的确支持简单格式和标准格式，我建议你要理解这两种格式，具体使用就看你用哪个更顺手了。
12.2.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;
输出▼
| prod_name | vend_name | prod_price | quantity |
|---|---|---|---|
| 18 inch teddy bear | Bears R Us | 11.9900 | 50 |
| Fish bean bag toy | Doll House Inc. | 3.4900 | 100 |
| Bird bean bag toy | Doll House Inc. | 3.4900 | 100 |
| Rabbit bean bag toy | Doll House Inc. | 3.4900 | 100 |
| Raggedy Ann | Doll House Inc. | 4.9900 | 50 |
分析▼
这个例子显示订单20007中的物品。订单物品存储在OrderItems表中。每个产品按其产品ID存储，它引用Products表中的产品。这些产品通过供应商ID联结到Vendors表中相应的供应商，供应商ID存储在每个产品的记录中。这里的FROM子句列出三个表，WHERE子句定义这两个联结条件，而第三个联结条件用来过滤出订单20007中的物品。
警告：性能考虑
DBMS在运行时关联指定的每个表，以处理联结。这种处理可能非常耗费资源，因此应该注意，不要联结不必要的表。联结的表越多，性能下降越厉害。
警告：联结中表的最大数目
虽然SQL本身不限制每个联结约束中表的数目，但实际上许多DBMS都有限制。请参阅具体的DBMS文档以了解其限制。
现在回顾一下第11课中的例子，如下的SELECT语句返回订购产品RGAN01的顾客列表：
输入▼
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'
)
);
如第11课所述，子查询并不总是执行复杂SELECT操作的最有效方法，下面是使用联结的相同查询：
输入▼
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';
输出▼
| cust_name | cust_contact |
|---|---|
| Fun4All | Denise L. Stephens |
| The Toy Store | Kim Howard |
分析▼
如第11课所述，这个查询中的返回数据需要使用3个表。但在这里，我们没有在嵌套子查询中使用它们，而是使用了两个联结来连接表。这里有三个WHERE子句条件。前两个关联联结中的表，后一个过滤产品RGAN01的数据。
提示：多做实验
可以看到，执行任一给定的SQL操作一般不止一种方法。很少有绝对正确或绝对错误的方法。性能可能会受操作类型、所使用的DBMS、表中数据量、是否存在索引或键等条件的影响。因此，有必要试验不同的选择机制，找出最适合具体情况的方法。
上一篇：第11课 SQL入门之使用子查询
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。 如若内容造成侵权/违法违规/事实不符，请联系我的编程经验分享网邮箱：veading@qq.com进行投诉反馈，一经查实，立即删除！