mysql单独一个join是_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 |

+------------+