SQL （十一）联结表（join，SQL最强大的功能之一，select能执行的最重要的操作）

先说说关系数据库

要理解好联结，必须先理解关系数据库及其设计的知识。

存储数据时，关系数据库比非关系数据库的可伸缩性要好的多。关系数据可以更有效的存储，不重复数据，不浪费空间；如果需要修改某个信息，关系数据库中修改的项少很多。

举个例子，有一个数据库，需要存储产品目录，即每一行是一个产品的信息，列要包括产品的供应商以及供应商信息，产品的描述，价格等。

如果我们把这些信息每一个都设置一个列，就会导致多个产品的供应商一样，那么就很浪费空间。

更好的办法是：

• 把供应商剔除出来，单独建表，vendors,用来存供应商的信息，列包括编号（主键），地址，联系方式等。
• 产品建一个表，products, 列有供应商编号vend_id，产品编号（主键），产品价格prod_price等。

这样就没有重复信息，并且产品表的vend_id列把vendors表和products表关联起来了。所以叫做关系数据库。

从这个示例中可以看到关系数据库的设计思想，就是把数据分解为多个表，尽量让每个表有自己的特殊用途，减少每个表的冗余信息，把多个不同的表关联起来。

为什么要用联结（为了用一条select语句检索出存储在多个表的数据）

• 联结其实就是用一条select语句联结多个关系表。注意这些表的关系是在运行时构造的。
• 由于关系数据库的思想是把数据分解式的存储，所以缺点是一条完整的数据需要通过查询多个表才可以获得到

大概这就是为什么数据库的体量特别特别大的时候就不喜欢用关系数据库而转而用非关系数据库吧。

• 联结不是物理实体，而是一种机制，它的用途就是在一条select语句中关联多个表，即数据库中并没有这种完整的一条数据，他只是在查询期间才存在。

创建联结

用where子句创建联结：事实上，所有的联结都必须用where子句

where子句本身是用来过滤的，这里它用来过滤出所有满足联结条件的数据行。

select vend_name, prod_name, prod_price
from vendors, products
where vendors.vend_id = products.vend_id;

这是第一次在select后面放了两个表的列，也是第一次在from后面放两个表

注意用到了完全限定列名来匹配（只要可能有歧义就要用），只要产品表中的供应商编号和vendors表的编号一样了，就输出三列信息。

即，要把products表的vend_id的每一行和vendors表的vend_id的每一行匹配，比如拿到products表的vend_id的第一行，就要vendors表的vend_id的每一行匹配一次。

注意from后面两个表的顺序无所谓，下面的代码也对：

select vend_name, prod_name, prod_price
from products, vendors
where vendors.vend_id = products.vend_id;

看个错误用法：查询多个表却没有使用联结（笛卡尔积）

代码（错误使用）：

select vend_name, prod_name, prod_price
from products, vendors;

结果：

可以看到，一个产品把所有供应商都对应了一遍，明显是错的结果。这就是因为查询多个表却没有使用联结

这里引出一个概念：

小结

• 所有联结都要有where子句

且过滤条件必须正确，否则返回的数据会比需要的多，还是错的，这说明联结的使用需要我们对where的过滤条件掌握极好，否则很容易出错！

• 如果不指定联结条件，或者两个表本身就没有联结关系，那么返回的就是笛卡尔积，也叫做叉联结。要尽量避免这种情况，因为没有用啊，是错的。

内联结（等值联结， 用on子句而非where指定联结条件）

等值联结 equijoin

select vend_name, prod_name, prod_price
from vendors inner join products
on vendors.vend_id = products.vend_id;

和之前的代码的代码的代码等价：

select vend_name, prod_name, prod_price
from vendors, products
where vendors.vend_id = products.vend_id;

小结

• 之前的那个用where相等测试创建的联结和现在用inner join和on子句配合的语法的结果是一样的，只是两种不同的实现语法。但是明显大家都偏向于后者这种语法，因为前者看起来太简单，没透露出联结原理。
• inner join 是放在from子句中的。
• on子句用来指定联结条件，之前是where子句用来指定联结条件。

联结多个表

之前只联结了2个表

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中有哪些产品，显示出不重复的产品编号

select distinct prod_id 
from orderitems
where order_num = 20007;

• 再根据第一步的代码嵌套一个子查询，查到产品信息

select prod_name, prod_price, vend_id
from products
where prod_id in (select distinct prod_id 
from orderitems
where order_num = 20007);

• 在根据第二步，在嵌套一个子查询，看到供应商名字

select vend_name, vend_id
from vendors
where vend_id in 
	 (select vend_id
	 from products
	 where prod_id in 
		(select distinct prod_id 
		 from orderitems
		 where order_num = 20007));

至此，核对出第一个联结代码输出的正确性了。还有个额外的收获：

• 子查询果然没有联结强大好用，因为子查询只可以显示出一个表的多个列，而联结可以同时显示多个不同表的不同列
• 越强大的功能需要脑子越好用才行，脑子不好用写不出来。。。

但是我发现理解复杂的联结语句好难，就是有很多个and条件的这种

• 我试了试，发现相等测试的左右表达式可以互换：

这样的代码和本节开头的代码的效果一样

select prod_name, vend_name, prod_price, quantity
from OrderItems, Products, Vendors
where vendors.vend_id = products.vend_id
and products.prod_id = OrderItems.prod_id
and order_num = 20007;

• 然后还想试试是否可以把三个联结条件的顺序换一换：

发现完全可以互换，不影响结果

select prod_name, vend_name, prod_price, quantity
from OrderItems, Products, Vendors
where order_num = 20007
and products.prod_id = OrderItems.prod_id
and vendors.vend_id = products.vend_id;

所以，只需要让脑子把需要的联结条件梳理清楚就好了，顺序，以及联结条件本身的顺序都不重要，不影响结果，这样就简单了。本例为了查询出订单20007的物品并显示prod_name, vend_name, prod_price, quantity信息，就需要用到三个表，我比较容易想到的逻辑是：

• 首先用order_num = 20007在OrderItems表中过滤出这个订单的所有物品，这时候已经可以获得OrderItems表的quantity信息。
• 然后用OrderItems.prod_id联结到products表，以获取prod_name, prod_price信息
• 然后用products.vend_id联结到vendors表，以获取vend_name信息

我按照这个逻辑写联结条件，就会清晰一些，好写好理解一些，但是其实三个条件的顺序并不影响结果，毕竟是and嘛。这样梳理顺序只是为了我理解方便。

把子查询改用联结实现

上一课的嵌套子查询代码：

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, OrderItems, Orders
where prod_id = 'RGAN01'
and OrderItems.order_num = Orders.order_num
and Orders.cust_id = Customers.cust_id;

结果一样的

我突然觉得这种用where和and的联结很好写了，先过滤出产品号是rgan01的产品，然后用order_num键联结OrderItems和Orders表，然后用cust_id键联结Orders表和Customers表。

这就是外键吗？看到上面的关系图中连线的地方就是这些用来联结两个表的键诶

我写联结代码时出的错：

select cust_name, cust_contact, cust_id
from customers, OrderItems, Orders
where prod_id = 'RGAN01'
and OrderItems.order_num = Orders.order_num
and Orders.cust_id = Customers.cust_id;

即在代码中第一行末尾多加了一个cust_id列，写的时候只是想多显示一点信息，但是却报错了

cust_id列的意义模糊，分析了一下，原来是因为这个键在多个表都出现了，DBMS不知道显示哪一个表的，虽然咱们知道都一样，所以改的方法也很简单，就是用完全限定列名呀

select cust_name, cust_contact, Customers.cust_id
from customers, OrderItems, Orders
where prod_id = 'RGAN01'
and OrderItems.order_num = Orders.order_num
and Orders.cust_id = Customers.cust_id;

嘻嘻，我真是一个机智的小聪明鬼，脑子不笨的，要自信

小结

• 不要联结没必要联结的表，因为联结表越多性能越差，并且其实所有的DBMS都有限制联结的表的数目。
  
  
• 尝试多种可能的方法，灵活思考，以提升性能

• 我觉得嵌套select语句和联结的对比上：

嵌套select语句的好处是：代码结构清晰，从内层到外层便于梳理逻辑，便于理解代码意图，好理解思路；
缺点是：代码写起来冗长麻烦，性能上也可能不算很好（不一定哈）。

联结的好处是：代码简单，性能好
缺点：稍微不好理解思路一点，但熟手应该觉得也很好理解

最关键的区别有一个：子查询最终显示的信息还是来自于一个表！而联结可以显示很多表的列。这是决定性的差异，不可逾越的鸿沟。

总结

• 最强大，最强大，最强大