博客

重现

a表 LEFT JOIN B表，结果表的数据量正常应该是=a表或者>a表(数据翻倍)。但有种情况，结果表会比a表少。

select 

a.XX,

...,

b.XX 

from  a 

left join b 

on a.id=b.id

where a.data_date=20210323 and b.data_date=20210323

逻辑很简单，就是用两个表的data_date做关联，但是结果却比 a 表的数据少了。当把唯一一个 “where” 换成 “and” 后，结果便正确了。这个原因其实是过滤数据的对象不同。

下面用 MySQL 创建示例来进行说明，

准备数据：分别创建两个表 emp和dept：

create table emp(

id int not null AUTO_INCREMENT PRIMARY KEY comment '员工编号',

name varchar(10) not null default '' comment '姓名',

dept_id int not null default 0 comment '所在部门编号'

) comment '员⼯表';


create table dept(

dept_id int not null AUTO_INCREMENT PRIMARY KEY comment '部门编号',

dept_name varchar(10) not null default '' comment '部门名称'

) comment '部门表';


insert into emp values (1,'张三',2),(2,'李四',2),(3,'王五',3),(4,'赵六',0),(5,'旺财',0);

insert into dept values (1,'财务部'),(2,'销售部'),(3,'研发部'),(4,'后勤部');

只用on，是我们想要的

select a.*,b.* from emp a left join dept b 
on a.dept_id =b.dept_id

如果加上 and a.dept_id = 2 ，结果也是我们想要的，如下：

select a.*,b.* from emp a left join dept b 
on a.dept_id = b.dept_id and a.dept_id = 2 

结果还是以左表为准，只是右边只有 dept_id = 2 的数据。

所以，LEFT JOIN时，无论ON的条件如何，左表不会被过滤，只会过滤右表。ON仅仅是连接的条件，但不是过滤条件，只是规定了什么样的数据需要连接==》

左表数据全部保留的情况下，需要保留哪些符合条件的右表数据

。

当把 and a.dept_id = 2 替换成 where a.dept_id = 2 ，结果就不正常了，如下：

select a.*,b.* from emp a left join dept b 
on a.dept_id = b.dept_id 
where a.dept_id = 2 

此时结果就比左表数据要少了。

summary

造成这种现象的原因是：数据库在通过两个表或者多个表返回数据时，都会生成一个中间的临时表， on后面的过滤条件是在生成临时表时进行过滤的，无论 on 条件的是否为真 ，都会返回左表的全部（以 left join 为例），如果右表无法匹配则补空。而 where 后面的过滤条件是在生成临时表之后进行过滤的，只有 where 过滤条件为真的数据才会返回。

也就是说，跟关联键有关的过滤条件，都要放在on中。

• 通过 on 过滤的原理如下：
  
  
• 通过 where 过滤的原理如下：
  
  
  所以要么在子查询中对b表进行where，或者在on后面写