hive&sql-LEFT JOIN之后,数据量为啥比左表还要少

  • Post author:
  • Post category:其他




重现

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后面写



版权声明:本文为qq_34224565原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。