MySQL表的联结之inner join/outer join

本文仅是自己阅读笔记，不正确之处请多包涵和纠正。

一、关系表

先看一个关系表的例子：
假设有一个产品表：里面存储了产品描述、价格、供应商信息。
然后表里有同一供应商生产的多种产品、那么在何处存储供应商信息？
答：应新建一个表存储供应商信息，每个供应商占一行每个供应商具有唯一标识。此标识为主键。例如供应商ID
而产品表只存储产品信息，它除了存储供应商ID作为外键以外不存储供应商的其他信息。

• 主键：能唯一标识表中的每一行。
• 外键：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系。

二、联结表

1、为什么要使用联结

由上面关系表的例子我们可以知道分解数据为多个表可以更有效地存储和处理，但是数据存储在多个表，我们应该怎么用单挑select语句检索出数据？

答：使用联结。

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

2、创建联结

解释：
例子用到WHERE子句创建联结。WHERE子句作为过滤条件，它只包含那些匹配联结条件的行。没有WHERE子句，第一个表中每个行将与第二个表中的每个行配对。
注意：上面用到完全限定列名；因为上面例子如果不用完全限定列名，只给出vend_id，MySQL会不知道是哪个表中的vend_id。

3、内部联结

上面所用的联结为等值联结，它基于两个表之间的相等测试。这种联结也称为内部联结。下面使用不同的语法代替上面WHERE子句指定联结条件的写法：

4、联结多个表

上面的例子就是联结了多个表的，但是这种联结多个表的处理是非常耗费资源的，所以我们要仔细检查不要联结不必要的表

三、创建高级联结

1、使用表别名

SQL允许给列名、计算字段、表名起别名。目的为了缩短SQL语句以及允许在单条SELECT语句中多次使用相同的表。

表别名不仅能用于WHERE子句，还可以用于SELECT的列表、ORDER BY子句以及语句的其他部分。

2、使用不同类型的联结

前面都是内部联结或称作等值联结的简单联结，还有其他的三种联结类型，分别是：自联结、自然联结、外部联结。

①自联结

例如：已知产品A的ID为DTNTR，想查看与相同供应商其他产品的信息
我们很容易首先就想到子查询：

但我们也可以使用联结来解决：

无论是用自联结还是子查询得到的结果都是一样的，但是有时候处理联结比处理子查询快，所以我们可以尝试两种方法看哪种性能更好。

②自然联结

当对表进行联结时，应该至少有一个列出现在不止一个表中，例如被联结的列。内部联结返回所有数据，其中相同的列多次出现。而自然联结排除多次出现，是每个列只返回一次。

自然联结要求我们只能选择那些唯一的列,一般通过对一个表使用通配符(SELECT*),而对其他表的列使用明确的子集来完成.如:

select c.*,o.order_num,o.order_date, oi.prod_id,oi.quantity,oi.item_price from customers as c,orders as o, orderitems as oi where c.cust_id = o.cust_id  and oi.order_num = o.order_num and prod_id='FB';

在这个例子中,通配符只对第一个表使用,所有其他列明确列出,所以没有重复的列被检索出来。

③外部联结

许多联结将一个表中的行与另一个表中的行进行关联。但有时候会需要包含没有关联行的那些行。
例如：

• 对每个客户下了多少订单进行计数，包括那些至今尚未下订单的客户；
• 列出所有产品以及它的订购数量，以及那些没有被订购过的产品；
• 计算平均销售规模，包括那些至今尚未下订单的客户；

上述例子中，联结包含了那些在相关表中没有关联行的行。这种类型的联结称为外部联结。

例子：检索所有客户及其订单：

关键字OUTER JOIN来指定外部联结类型。与内联结关联不同的是，外部联结还包括没有关联行的行。
在使用OUTER JOIN语法时，必须制定LEFT 或者RIGHT关键字指定其包括所有行的表。

3、使用带聚集函数的联结

例子：如果要检索所有客户及每个客户所下单的订单数：