Left Join之后，数据为何变少了？

大数据记事本 2021-03-25

15437

今天被问到一个问题，通过 left join 对两个表做关联查询，结果中数据量为何比左表的数据量少？按理说数据量应该和左表保持一致呀。看了一下 SQL，大概逻辑如下：

select 
a.XX,
...,
b.XX 
from  a 
left join b 
on a.id=b.object_id
where a.data_date=20210323
and b.data_date=20210323
复制

逻辑很简单，就是用两个表同一天的分区数据做关联，但是结果却比 a 表的数据少了。当把 "where" 换成 "and" 后，结果便正确了。这个原因其实是过滤数据时机的不同。

下面用 MySQL 创建示例来进行说明，分别创建两个表 dept 和 emp：

create table dept(
dept_id int not null AUTO_INCREMENT PRIMARY KEY comment '部门编号',
dept_name varchar(10) not null default '' comment '部门名称'
) comment '部门表';


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 '员⼯表';


insert into dept values (1,'财务部'),(2,'销售部'),(3,'研发部'),(4,'后勤部');
insert into emp values (1,'张三',2),(2,'李四',2),(3,'王五',3),(4,'赵六',0),(5,'旺财',0);
复制

当执行如下语句时，结果如下图：

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 的数据，其余都用 NULL 补齐了

当把 ' and a.dept_id = 2 ' 替换成 ' where a.dept_id = 2 '，结果如下：

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

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

通过 on 过滤的原理如下：