第12课 连接表（Joining Tables）
本课学习什么是连接？为什么会使用连接？以及如何创建SELECT声明来使用连接？
12.1 理解连接（Understanding Joins）
SQL的一个最强大的特性是在数据检索查询的同时连接表。连接是你可以采用SQL SELECT做的最重要的操作之一，很好的理解连接和连接句法是学习SQL时及其重要的部分。
在你可以有效使用连接之前，你必须理解关系表及关系数据库设计的基础。下列所述决没有完整覆盖主题，但这应该足以让你起跑。
12.1.1 理解关系表（Understanding Relational Tables）
最好的理解关系数据表的方式是看一个实例。
假如你有一个数据库表包含产品目录，每个产品目录条目在它自己的行里。你要保存的每条这类信息会包含产品描述和价格，以及生产产品的公司供应商信息一起。
现在假设你有多个目录项是由相同供应商生产的。你会把供应商信息（比如供应商名、地址、联系信息）保存在什么地方？你不想将这些数据与产品保存在一起有多个原因：
1、因为每个产品的供应商信息相同的， 供应商管理系统重复每个产品的这类信息，会浪费时间以及存储空间。
2、如果供应商信息改变（例如，如果供应商搬家或者它的分区电话号码改变了），你必须要更新每一个供应商信息。
3、当数据是重复的（亦即，供应商信息用于每个产品），极大可能不会每次数据输入都完全相同。不一致的数据在报告中使用极其困难。
这里的关键是相同数据有多次发生并不是一件好事，这个原则是关系数据库设计的基础。设计关系表来将信息分成多个表，每个数据类型一个。表通过公有值彼此建立联系。
在我们的例子中，创建了两张表，一张用于供应商信息，一张用于产品信息。Vendors表包含所有的供应商信息，每个供应商一个表行（one table row per vender），跟每个供应商的唯一标示符一起。这个值，称为主键，可能是供应商ID，或任何其他的唯一值。
Products表仅存储产品信息，除了供应商ID（Vendors表的主键）外，无供应商特定信息。这个键值将Vendors表和Products表关联起来，使用供应商ID使你能够使用Vendors表来找到适当供应商的详细资料。
这对你有什么作用？看下面：
1、供应商信息是永不重复的，这样时间和空间不会浪费。
2、如果供应商信息改变，你可以更新单个记录，即Vendors表中的一项。关联表中的数据不会改变。
3、因为没有数据是重复的，数据使用明显一致，形成数据报告和操作更加简单。
底线是关联数据可以高效存储和易于操作。因此，关系数据库规模（Scale）远好于非关系数据库。
Scale
Able to handle an increasing load without failing. A well-designed database or application is said to scale well.
12.1.2 为什么使用连接（Joins）？
刚才已经解释，将数据分成多张表使得存储更加有效，更容易操作，更大的可扩展性。但这些好处是有代价的。
如果数据存储在多张表中，你怎么通过单个SELECT声明查询数据呢？
答案是使用关联。简言之，关联是一种用于关联在一个SELECT声明中的表的机制（名字关联的由来）。使用特殊的句法，多张表可以关联起来，这样就可以返回单组输出，关联立即把每张表里的正确的行联系起来。
Note：使用交互式的DBMS工具
要理解关联并非是一个物理实体-换句话说，它并不存在于实际的数据库中。一个关联由DBMS根据需要创建的，并在查询执行期间存在。
许多DBMS提供图形化接口可用于交互式地定义表关系。这些工具在帮助维持引用完整性上是非常有价值的。当使用关联表，只有有效的数据插入到关联列，这一点很重要。回到这个例子，如果一个无效的供应商ID保存在Products表中，那些产品将无法访问，因为他们不会与任何供应商有关系。为了阻止这些发生，数据库可以指定Products表的vendor ID列中仅允许（出现）有效值（Vendors表中提供的值） 。
引用完整性(Referential integrity)意味着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声明，这里的SELECT声明在FROM语句中列出了两张表，Vendors和Products。这是两张在这个SELECT声明中关联的表的名字。这两张表正确地关联一个WHERE语句，指示DBMS匹配Vendors表中的vend_id和Products表中的vend_id。
你注意到，列采用Vendors.vend_id和Products.vend_id来确定。这种完全限定（fully qualified）的列名在此是必需的，因为如果你仅仅指定vend_id，DBMS不能区分你正在查询哪个vend_id列。（这里有两个，每个表一个）。在之前的输出中你可以看到，单条SELECT声明返回来自两张不同表的数据。
Caution：完全限定的列名称（Fully Qualifying Column Names)
As noted in the previous lesson, you must use the fully qualified column name (table and column separated by a period) whenever there is a possible ambiguity about which column you are referring to. Most DBMSs will return an error message if you refer to an ambiguous
column name without fully qualifying it with a table name.
12.2.1 WHERE语句的重要性
使用WHERE语句来设置关联关系看起来可能比较奇怪，但实际上，这么做有一个非常好的理由。记住，当表在一个SELECT声明中关联的时候，关系立即就构建起来了。数据库表定义中什么也没有可以用来指定DBMS如何关联数据库表。WHERE语句充当一个仅包含行的过滤器，这些行匹配特定过滤条件-关联条件。没有WHERE语句，第一张表中的每一行将与第二张表中的每一行成对，而不管他们是否逻辑上可以在一起。
笛卡尔积乘积（Cartesian Product）
The results returned by a table relationship without a join condition. The number of rows retrieved will be the number of rows in the first table multiplied by the umber of rows in the second table.
为了理解这个，看下面的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
分析：
在前面的输出中你可以看到，笛卡尔积乘积很少是你想要的。这里返回的数据 匹配每个供应商的每个产品，包括错误供应商的产品（甚至即使供应商完全没有产品）。
Cause：不要忘记WHERE语句
确保你的所有关联（joins）有WHERE语句，DBMS将返回比你想要的更多的数据。一个错误的过滤条件会导致DBMS返回不正确的数据。
Tip：交叉关联（Cross Joins）
有时候你将看到把返回一个笛卡尔积关联类型称为交叉关联。
12.2.2 内部连接（Inner Joints）
到目前为止，你用的关联称为同等连接（equijoin）--一种两张表之间基于相等性的测试。这种连接也称为内部连接。事实上，对于这些连接，你可能使用稍微不同的句法，来明确指定连接类型。下面的SELECT声明返回与前面的例子几乎相同的数据：
输入：
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;
分析：
此处两张表之间的关系作为FROM语句的一部分称为INNER JOIN。（Here the relationship between the two tables is part of the FROM clause specified as INNER JOIN）。当使用这种句法，连接条件是使用特殊ON语句执行的，而不是WHERE语句。传给ON的实际条件传给WHERE的相同。
Note： The “Right” Syntax
Per the ANSI SQL specification, use of the INNER JOIN syntax is preferred over the simple equijoins syntax used previously. Indeed, SQL purists tend to look upon the simple syntax with disdain. That being said, DBMSs do indeed support both the simpler and the standard formats, so my recommendation is that you take the time to understand
both formats, but use whichever you feel more comfortable with.
12.2.3 连接多张表（Joining Multiple Tables）、
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
Caution：性能考虑
DBMSs process joins at run-time relating each table as specified. This process can become very resource intensive so be careful not to join tables unnecessarily. The more tables you join the more performance will degrade.
Caution：一个关联中表的最大数目
While it is true that SQL itself has no maximum number of tables per join restriction, many DBMSs do indeed have restrictions. Refer to your DBMS documentation to determine what restrictions there are, if any.
输入：
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';
输出：
cust_name cust_contact
Fun4All Denise L. Stephens
The Toy Store Kim Howard
Tip: It Pays to Experiment
As you can see, there is often more than one way to perform any given
SQL operation. And there is rarely a definitive right or wrong way.
Performance can be affected by the type of operation, the DBMS being
used, the amount of data in the tables, whether or not indexes and keys
are present, and a whole slew of other criteria. Therefore, it is often
worth experimenting with different selection mechanisms to find the one
that works best for you.
