数据库left join相关的问题

前言

记录三个东西，
第一是left join的作用，
第二是left join中on和where的区别，
第三是left join，right join，inner join中的小表驱动大表的疑问。

left join的作用

作用：返回包括左表中的所有记录和右表中联结字段相等的记录。

下面通过例子来说明。

假设表1为：

+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+

表2为：

+----+------+
| id | name |
+----+------+
|  3 | c    |
|  4 | d    |
|  5 | c    |
+----+------+

例如：

select t1.*,t2.* from t1 left join t2 on t1.name=t2.name;

其中过程是怎样的？
我的理解：数据库会先取得表1的全部记录，对每条记录再用表2的每条记录根据on条件进行匹配(也就是说这里是两层循环)，
若匹配上得，则拼接这两个记录作为结果集的一部分，
若表2没有任何一条记录满足on条件，则保留表1的记录，表2的记录则以null补上再作为结果集的一部分，
最后从结果集中选择需要的字段部分作为最终的结果集。

因此最终输出的结果是：

+----+------+------+------+
| id | name | id   | name |
+----+------+------+------+
|  3 | c    |    3 | c    |
|  3 | c    |    5 | c    |
|  1 | a    | NULL | NULL |
|  2 | b    | NULL | NULL |
+----+------+------+------+

left join中on和where的区别

然后是另一个问题，on和where的问题。基于上面的select语句，如果我们只想保留t1.id>2的记录，这样写的话：

 select t1.*,t2.* from t1 left join t2 on t1.name =t2.name and t1.id>2;

输出的结果是：

+----+------+------+------+
| id | name | id   | name |
+----+------+------+------+
|  3 | c    |    3 | c    |
|  3 | c    |    5 | c    |
|  1 | a    | NULL | NULL |
|  2 | b    | NULL | NULL |
+----+------+------+------+

也就是说，on条件中的t1.id>2没有起作用。

这是为什么呢？

结论是这样的：on后面带的条件是作用于附表的，也就是说on后面带的条件是当表1的记录与表2的记录匹配时，作为表2的匹配条件。

举例来说：
当用表1的第3条（3，c）记录与表2的每条记录{（3，c），（4，d），（5，c）}进行匹配时，
假设on条件是只是t1.name =t2.name时，
当表1的（3，c）这条记录
与表2的（3，c）进行匹配时，对表2这条记录而言，on条件成立，（3，c，3，c）作为结果集，
与表2的（4，d）进行匹配时，对表2这条记录而言，on条件不成立，不处理，
与表2的（5，c）进行匹配时，对表2这条记录而言，on条件不成立，不处理。

当on条件是t1.name =t2.name and t1.id>2时，
当表1的（3，c）这条记录
与表2的（3，c）进行匹配时，对表2这条记录而言，on条件是t1.id>2成立，（3，c，3，c）作为结果集的一部分，
与表2的（4，d）进行匹配时，对表2这条记录而言，on条件不成立，不处理，
与表2的（5，c）进行匹配时，对表2这条记录而言，on条件不成立，不处理。
也就是说，on条件只有作用于附表（此例子中指表2）的字段时才是有效的。

因此，如果要保留t1.id>2的记录，应通过where关键字来实现：

select t1.*,t2.* from t1 left join t2 on t1.name =t2.name where t2.id>2;

输出结果为：

+----+------+------+------+
| id | name | id   | name |
+----+------+------+------+
|  3 | c    |    3 | c    |
|  3 | c    |    5 | c    |
+----+------+------+------+

on条件不能对附表进行完全过滤。

假如输入的是：

select t1.*,t2.* from t1 left join t2 on t1.name =t2.name and t2.id>3;

照理说，输出的记录中，t2.id都得大于3。可实际呢，输出结果为：

+----+------+------+------+
| id | name | id   | name |
+----+------+------+------+
|  3 | c    |    5 | c    |
|  1 | a    | NULL | NULL |
|  2 | b    | NULL | NULL |
+----+------+------+------+

为什么输出的记录中有t2.id为NULL的呢？

我们再用前面的那种方式进行分析：
当用表1的第3条（1，a）记录与表2的每条记录{（3，c），（4，d），（5，c）}进行匹配时，
假设on条件是只是t1.name =t2.name and t2.id>3，
当表1的（1，a）这条记录
与表2的（3，c）进行匹配时，对表2这条记录而言，on条件不成立，不处理，
与表2的（4，d）进行匹配时，对表2这条记录而言，on条件不成立，不处理，
与表2的（5，c）进行匹配时，对表2这条记录而言，on条件不成立，不处理。
然后，由于表2中没有一条记录能与表1的这条记录匹配。因此，会保留表1的这条记录并以表2的记录为NULL来进行拼接。
因此，（1，a，NULL，NULL）也作为结果集的一部分。

因此，这里并不是on条件的t2.id>3没有生效，而是on条件不能对附表进行完全过滤。
要想达到输出的结果满足t2.id>3，可以通过where来实现。

on条件，通常是用来使用主表的字段信息来结果附表进行过滤的。也就是说，on条件，通常是这样的：t1.xx=t2.xxx。即用到了两表的字段信息。

小表驱动大表

结论：小表驱动大表，查找效率会高很多。反之，效率会很低。我们应让小表作为驱动表。

大跟小描述的是表的记录行数以及字段数量。

小表驱动大表涉及的是效率的问题。

left join中，前表（左表）是主表，也是驱动表。如果前表是大表，后表是小表，就成了大表驱动小表，效率较低。
right join中，后表（右表）是主表，也是驱动表。如果后表是大表，前表示小表，也是大表驱动小表，效率较低。
而inner join，会自动选择小表作为驱动表。

当假如a表存的是某大型公司的员工信息表，b表存的是部门信息表。那么很明显，a表就是大表了，b表就是小表了。

疑问：就算我知道left join中前表是大表，又能怎么办？如果我要的就是left join的效果呢？换成right join输出的数据就不一样了啊。

在网上查了许多小表驱动大表的问题，有些感觉写得很扯。所有现在我也只是知道这个结论，但是这个结论怎么来的，我也没想懂。即使知道MySQL表关联的算法是Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。

如懂这一块的，望能分享之。

小表驱动大表相关文章：
http://blog.csdn.net/codejas/article/details/78632883
http://blog.csdn.net/u013065023/article/details/54964275
https://www.cnblogs.com/zhengyun_ustc/p/slowquery1.html
http://blog.itpub.net/22664653/viewspace-1692317/
https://www.cnblogs.com/softidea/p/6409443.html