Hive SQL查询left join下on条件和where条件区别

本文解析Hive SQL中LEFT JOIN时ON过滤左表条件的不寻常行为,强调为何将其放在WHERE的重要性,并通过实例说明其与WHERE条件的区别。同时介绍了在MySQL中的类似现象及深入分析链接。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

我们通常的认知是联表查询时ON是作用于联表前(时)的过滤条件,WHERE是对联表后的数据的过滤条件,但hive sql查询有个特殊点需要关注,那就是对于left join中的单独针对左表的ON过滤条件有可能会被忽略,比如

SELECT DISTINCT a.dt FROM 
wlxdw.dws_tuid_cheat_di a 
LEFT JOIN
wlxdw.apps_tuid_device_map_ds b
ON a.tuid = b.tuid AND a.dt='2021-10-18' AND b.dt='2021-10-19'

查询结果
在这里插入图片描述

可以看到a.dt='2021-10-18’这个过滤条件并没有生效,有人可能觉得b.dt='2021-10-19’是不是也没有生效,把上述SQL稍作修改来验证一下

SELECT DISTINCT b.dt FROM 
wlxdw.dws_tuid_cheat_di a 
LEFT JOIN
wlxdw.apps_tuid_device_map_ds b
ON a.tuid = b.tuid AND a.dt='2021-10-18' AND b.dt='2021-10-19'

在这里插入图片描述
在这里插入图片描述

显而易见,右表上的ON条件 b.dt=xxxx 生效了,那如果想要左表的 a.dt=‘2021-10-18’ 生效,可以放在WHERE条件上

在这里插入图片描述
而如果把右表的b.dt=‘2021-10-19’ 放到WHERE条件,语义则变的不一样,由于WHERE是先联表再进行过滤,这时查询结果中b.dt就不存在值为NULL的情况了,LEFT JOIN 相当于变成了 INNER JOIN

在这里插入图片描述

在这里插入图片描述

在这里插入图片描述
从上面的结果可以看出ON和WHERE查询条件语义上的差别

再来看一组SQL对比

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
神奇的一幕出现了,此时ON中的过滤条件a.dt=‘2021-10-18’ 时而生效时而不生效

总结:Hive SQL 中 LEFT JOIN 单独针对左表的过滤条件必须放在WHERE上,放在ON上的效果是不可预期的,单独针对右表的查询条件放在ON上是先过滤右表,再和左表联表,放在WHERE条件上则是先联表再过滤,语义上存在差别。

补充:对于MYSQL中的LEFT JOIN和Hive中表现相似,更深层次分析可以看看 https://developer.aliyun.com/article/718897

<think>嗯,用户问的是Hive SQL中左连接(LEFT JOIN)的ON条件WHERE条件区别。我得先回忆一下Hive的处理逻辑。根据之前的引用资料,记得左连接时,ON条件不会过滤左表的数据,无论ON里的条件是否满足,左表的记录都会保留。而WHERE条件是在连接之后应用的,所以会过滤掉不符合条件的行,包括左表中那些在右表没有匹配到的行,这可能导致LEFT JOIN变成类似INNER JOIN的效果。 首先,要明确左连接的基本特性:保留左表所有记录,右表没有匹配的则填充NULL。如果过滤条件放在ON里,比如右表的某个字段,这时候ON里的条件会影响右表的记录是否匹配,但左表的记录依然会被保留。例如,在引用[1]中提到,如果将右表的过滤条件放在WHERE里,会导致LEFT JOIN变成INNER JOIN,因为WHERE会在连接后过滤掉右表为NULL的行,这样左表原本保留的行可能就被排除了。 然后,引用[2][4]都强调了ONWHERE的执行顺序不同。ON条件是在连接过程中应用的,而WHERE是在连接之后。对于左连接来说,ON里的条件只会影响右表是否匹配,不会影响左表的数据保留。而WHERE则会对整个结果集进行过滤,包括左表的记录。如果左表的某个条件放在WHERE里,那么那些不符合条件的左表记录也会被过滤掉,这时候LEFT JOIN的效果可能就不如预期了。 还要考虑到NULL值的问题,如引用[3]提到的,比较运算可能返回NULL,这会影响条件判断。在ON条件中,如果右表的某个字段为NULL,可能会导致条件不满足,但左表的记录依然存在。而WHERE中的条件如果涉及右表字段,会排除那些右表为NULL的行,从而改变结果。 用户可能需要具体的例子来理解,所以最好给出两个对比的示例,比如在ON里过滤右表WHERE里过滤右表的不同结果。同时,还要说明如果对左表使用WHERE条件,会直接过滤左表的数据,这在LEFT JOIN中可能不适用,除非确实需要过滤左表。 总结起来,关键点在于执行顺序过滤的对象:ON影响右表的匹配,WHERE影响整个结果集。需要确保用户明白这两种条件放置位置的不同影响,避免误用导致查询结果不符合预期。</think>### Hive SQL左连接(LEFT JOIN)中ON条件WHERE条件区别 #### 一、核心区别Hive SQL中,`ON`条件与`WHERE`条件的执行顺序语义逻辑有本质差异: 1. **`ON`条件** - 作用于**连接过程**,仅影响**右表记录的匹配逻辑** - 即使条件不满足,**左表记录仍会被保留**(右表字段填充`NULL`) - 例如: ```sql SELECT * FROM a LEFT JOIN b ON a.id = b.id AND b.dt = '2023-01-01' -- 右表过滤条件 ``` 此时右表`b`未匹配的记录会显示为`NULL`,但左表`a`所有记录仍保留[^1][^2][^4]。 2. **`WHERE`条件** - 作用于**连接完成后的结果集**,对所有字段进行过滤 - 会**直接过滤掉不符合条件的记录**(包括左表记录) - 例如: ```sql SELECT * FROM a LEFT JOIN b ON a.id = b.id WHERE b.dt = '2023-01-01' -- 全局过滤条件 ``` 此时左表中未匹配到`b.dt='2023-01-01'`的记录会被过滤,相当于`INNER JOIN`。 #### 二、执行顺序对比 $$ \text{执行顺序:ON条件 → 连接操作 → WHERE条件} $$ - **优先级**:`ON`条件优先级高于`WHERE`条件 - **数据保留**:`WHERE`条件可能导致左表数据丢失,`ON`条件仅影响右表匹配结果[^2] #### 三、典型场景分析 | 场景 | 条件位置 | 左表数据保留 | 右表数据匹配 | 等效操作 | |------|----------|--------------|--------------|----------| | 过滤右表 | `ON` | ✔️ | 仅匹配符合条件的记录 | 标准LEFT JOIN | | 过滤右表 | `WHERE` | ❌ | 过滤掉所有未匹配记录 | INNER JOIN | | 过滤左表 | `ON` | ✔️ | 不影响左表过滤 | 无效操作 | | 过滤左表 | `WHERE` | ❌ | 直接过滤左表记录 | 普通WHERE过滤 | #### 四、NULL值处理 当比较运算涉及`NULL`值时(例如`1 < NULL`),结果为`NULL`,在条件判断中会被视为`FALSE`[^3]。 例如:`WHERE b.col > 10`会排除`b.col IS NULL`的记录。 #### 五、实验验证 ```sql -- 测试数据 CREATE TABLE a (id INT, value STRING); INSERT INTO a VALUES (1,'A'), (2,'B'); CREATE TABLE b (id INT, status STRING); INSERT INTO b VALUES (1,'C'), (2,'D'); -- 场景1:ON条件过滤右表 SELECT * FROM a LEFT JOIN b ON a.id = b.id AND b.status <> 'C'; -- 结果:保留所有左表记录,右表id=1的记录被过滤 -- 场景2:WHERE条件过滤右表 SELECT * FROM a LEFT JOIN b ON a.id = b.id WHERE b.status <> 'C'; -- 结果:仅保留id=2的记录(左表id=1的记录被过滤) ```
评论 5
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值