重现
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 过滤的原理如下：
所以要么在子查询中对b表进行where，或者在on后面写
Original: https://blog.csdn.net/qq_34224565/article/details/125760805
Author: zdkdchao
Title: hive&sql-LEFT JOIN之后，数据量为啥比左表还要少
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/818246/
转载文章受原作者版权保护。转载请注明原作者出处！