MySQL中关于join的一个误区

我估计应该有一部分MySQL的小白用户跟我一样，以为 A left join B on a.key=b.key and condition 等价于 A left join B on a.key = b.key where condition

假设我们有两张表，分别存放某个对象的信息1+信息2、信息3+信息4，表结构如下：

CREATE TABLE `part1` (
  `id` int(11) NOT NULL,
  `info1` int(11) NOT NULL,
  `info2` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 

CREATE TABLE `part2` (
  `id` int(11) NOT NULL,
  `info3` int(11) NOT NULL,
  `info4` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) 

然后我们往其中插入一些数据：

insert into part1(id,info1,info2)values(1,10,10),(2,20,20),(3,30,30);
insert into part2(id,info3,info4)values(1,10,null),(4,40,40),(2,20,20);

最终2张表的数据如下：

mysql> select * from part1;
+----+-------+-------+
| id | info1 | info2 |
+----+-------+-------+
|  1 |    10 |    10 |
|  2 |    20 |    20 |
|  3 |    30 |    30 |
+----+-------+-------+

 select * from part2;
+----+-------+-------+
| id | info3 | info4 |
+----+-------+-------+
|  1 |    10 |  NULL |
|  2 |    20 |    20 |
|  4 |    40 |    40 |
+----+-------+-------+

接下来我们看一个查询：

select * from part1 a left join part2 b on a.id = b.id and b.info4 = 20 order by a.id;

其结果如下：

+----+-------+-------+------+-------+-------+
| id | info1 | info2 | id   | info3 | info4 |
+----+-------+-------+------+-------+-------+
|  1 |    10 |    10 | NULL |  NULL |  NULL |
|  2 |    20 |    20 |    2 |    20 |    20 |
|  3 |    30 |    30 | NULL |  NULL |  NULL |
+----+-------+-------+------+-------+-------+

呵呵，我明明写着b.info4=20嘛，为啥第一行和第三行冒出来了呢？

好吧，我错了，我改：

select * from part1 a left join part2 b on a.id = b.id where b.info4 = 20 order by a.id;

这下结果对了：

+----+-------+-------+------+-------+-------+
| id | info1 | info2 | id   | info3 | info4 |
+----+-------+-------+------+-------+-------+
|  2 |    20 |    20 |    2 |    20 |    20 |
+----+-------+-------+------+-------+-------+

同样的一些类似的“奇怪”的SQL结果如下：

mysql> select * from part1 a left join part2 b on a.id = b.id and b.info4 is not null order by a.id;
+----+-------+-------+------+-------+-------+
| id | info1 | info2 | id   | info3 | info4 |
+----+-------+-------+------+-------+-------+
|  1 |    10 |    10 | NULL |  NULL |  NULL |
|  2 |    20 |    20 |    2 |    20 |    20 |
|  3 |    30 |    30 | NULL |  NULL |  NULL |
+----+-------+-------+------+-------+-------+

（b.info4 is not null嘛，这结果明显很诡异）

知道是我理解错了，错在这里：

MySQL的连接查询中，ON后面的condition只是指定了从B表检索数据的方式，并不做任何的过滤，如果B表没有匹配的数据，则以NULL填充（注意，关键点在这里，并不做where过滤）。数据填充返回了以后，再执行where条件的过滤，如果没有where条件，就不做任何过滤了

注：以上测试使用的MySQL版本为：

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.20-log |
+------------+