left join on and vs where

场景描述

查询业务数据是，有如下需求。需要从a表中去除b表中包含的数据。我的第一个想法是a left join b on a.id = b.aid and b.aid is null，然后数据是出来了。但是抽了几个发现，a中有，b中也有。于是我陷入了沉思。

复现

造数据

CREATE TABLE `join_1` (
  `id` int(11) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4
5

CREATE TABLE `join_2` (
  `id` int(11) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  `thing` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
1
2
3
4
5

查询

select * from join_1 left join join_2 on join_1.id = join_2.id and join_2.phone  is not  null;
1

以and查询结果如下

这里很明显可以看到，我明明不想要手机号为null的，但是查询结果里怎么还是有。。我明明加了判空条件了。。

select * from join_1 left join join_2 on join_1.id = join_2.id where join_2.phone  is not  null;
1

以where查询结果如下

这里的查询结果正是我想要的。但是and 和where 有啥区别呢。

分析

where语句发生在join之后，它是对join结果的过滤
on、and语句，发生在join之前，数据在join的过程中被过滤。
这里我用and查询存在空值是因为，join之前join_2表中的确有数据，后面left join之后才产生null数据的。

stackoverflow解释