《SQL必知必会》第十二课 联结表 在数据查询的执行中联结（join）表

第十二课 联结表

联结：

[1] SQL 最强大的功能之一就是能在数据查询的执行中联结（join）表

[2]联结是利用SQL 的SELECT 能执行的最重要的操作

[3]最常用的联结: 等值联结（也称为内联结）

关系表以及关系数据库设计的一些基础知识:

1.关系表：
例：
（1）有一个包含产品目录的数据库表，其中每类物品占一行。对于每一种物品，要存储的信息包括产品描述、价格，以及生产该产品的供应商（Products 表只存储产品信息）
（2）现在有同一供应商生产的多种物品，那么在何处存储供应商名、地址、联系方法等供应商信息（Vendors 表包含所有供应商信息）

将这些供应商信息数据与产品信息分开存储的理由：
（1）同一供应商生产的每个产品，其供应商信息都是相同的，对每个产品重复此信息既浪费时间又浪费存储空间；
（2）如果供应商信息发生变化，例如供应商迁址或电话号码变动，只需修改一次即可；
（3）如果有重复数据（即每种产品都存储供应商信息），则很难保证每次输入该数据的方式都相同。不一致的数据在报表中就很难利用

#相同的数据出现多次决不是一件好事，这是关系数据库设计的
基础
#关系表的设计就是要把信息分解成多个表，一类数据一个表
#各表通过某些共同的值互相关联（所以才叫关系数据库）

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

#Vendors 表包含所有供应商信息，每个供应商占一行，具有唯一的标识。此标识称为主键（primary key），可以是供应商ID 或任何其他唯一值。
#Products 表只存储产品信息，除了存储供应商ID（Vendors 表的主键）外，它不存储其他有关供应商的信息
#Vendors 表的主键将Vendors 表与Products 表关联，利用供应商ID 能从Vendors 表中找出相应供应商的详细信息。

好处：
[1]供应商信息不重复，不会浪费时间和空间
[2]如果供应商信息变动，可以只更新Vendors 表中的单个记录，相关表中的数据不用改动；
[3]由于数据不重复，数据显然是一致的，使得处理数据和生成报表更简单

#关系数据可以有效存储，方便处理
#关系数据库的可伸缩性远比非关系数据库要好

注意1：
可伸缩性：
[1]能够适应不断增加的工作量而不失败
[2]设计良好的数据库或应用程序称为可伸缩性好（scale well）

[3]将数据分解为多个表能更有效地存储，更方便地处理，并且可伸缩性更好，但有代价，即如果数据存储在多个表中，怎样用一条SELECT 语句检索数据

[4]答案：使用联结

#联结是一种机制，用来在一条SELECT 语句中关联表，因此称为联结
#使用特殊的语法，可以联结多个表返回一组输出，联结在运行时关联表中正确的行
#联结不是物理实体，它在实际的数据库表中并不存在。
#DBMS 会根据需要建立联结，它在查询执行期间一直存在
#许多DBMS 提供图形界面，用来交互式地定义表关系

创建联结：

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

Products 表：

Vendors 表：

【1】SELECT vend_name, prod_name, prod_price FROM vendors, products WHERE vendors.vend_id = products.vend_id;';
#一条SELECT 语句返回了两个不同表中的数据
#SELECT 语句与前面所有语句一样指定要检索的列
#最大的差别是所指定的两列（prod_name 和prod_price）在一个表中，而第三列（vend_name）在另一个表中
#FROM子句列出了两个表：Vendors 和Products，是这条SELECT 语句联结的两个表的名字
#两个表用WHERE 子句正确地联结，WHERE 子句指示DBMS 将Vendors 表中的vend_id 与Products 表中的vend_id 匹配
#要匹配的两列指定为Vendors.vend_id 和Products.vend_id
#需要这种完全限定列名，如果只给出vend_id，DBMS 就不知道指的是哪一个（每个表中有一个）

注意2：
完全限定列名：
[1]在引用的列可能出现歧义时，必须使用完全限定列名（用一个句点分隔表名和列名）。
[2]如果引用一个没有用表名限制的具有歧义的列名，大多数DBMS 会返回错误

WHERE子句的重要性

#使用WHERE 子句建立联结关系是有很充分的理由的
#在一条SELECT 语句中联结几个表时，相应的关系是在运行中构造的
#在联结两个表时，实际要做的是将第一个表中的每一行与第二个表中的每一行配对
#WHERE 子句作为过滤条件，只包含那些匹配给定条件（这里是联结条件）的行
#没有WHERE子句，第一个表中的每一行将与第二个表中的每一行配对，而不管它们逻辑上是否能配在一起。

注意3：
笛卡儿积：
[1]由没有联结条件的表关系返回的结果为笛卡儿积
[2]检索出的行的数目将是第一个表中的行数乘以第二个表中的行数

【2】SELECT vend_name, prod_name, prod_price FROM vendors, products;

#相应的笛卡儿积不是我们想要的。
#这里返回的数据用每个供应商匹配了每个产品，包括了供应商不正确的产品（即使供应商根本就没有产品）。

注意4：
不要忘了WHERE 子句：
[1]要保证所有联结都有WHERE 子句，否则DBMS 将返回比想要的数据多得多的数据
[2]要保证WHERE 子句的正确性。不正确的过滤条件会导致DBMS 返回不正确的数据。

注意5：
叉联结：
返回笛卡儿积的联结，也称叉联结（cross join）

内联结：

（1）目前为止使用的联结称为等值联结（equijoin），它基于两个表之间的相等测试，这种联结也称为内联结（inner join）

（2）可以对这种联结使用稍微不同的语法，明确指定联结的类型

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

【3】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 的相同
#ANSI SQL 规范首选INNER JOIN 语法

联结多个表：

#SQL 不限制一条SELECT 语句中可以联结的表的数目，，但实际上许多DBMS都有限制
#创建联结的基本规则也相同：首先列出所有表，然后定义表之间的关系

【4】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 存储在每个产品的记录中
#FROM 子句列出三个表
#WHERE 子句定义这两个联结条件，而第三个联结条件用来过滤出订单20007 中的物品。

注意6：
性能考虑：
不要联结不必要的表，联结的表越多，性能下降越厉害

【5】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 语句返回订购产品RGAN01 的顾客列表
#子查询并不总是执行复杂SELECT 操作的最有效方法
#下面是使用联结的相同查询：

【6】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';
#这个查询中的返回数据需要使用3 个表
#没有在嵌套子查询中使用它们，而是使用了两个联结来连接表
#有三个WHERE 子句条件，前两个关联联结中的表，后一个过滤产品RGAN01 的数据

注意7：
多做实验：
[1]执行任一给定的SQL 操作一般不止一种方法
[2]有必要试验不同的选择机制，找出最适合具体情况的方法

需要完整代码或书籍PDF版的小伙伴可关注微信公众号：菜田里守望者

打开微信扫一扫关注吧，你们的支持就是我的动力

参考文献：
【1】《SQL必知必会》第四版 人民邮电出版社 [美] Ben Forta 著 钟鸣 刘晓霞 译