Hive in exists 区别

￡神魔★判官ぃ 2021-09-25 02:22 432阅读 0赞

**IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。**

**in  /exists / left semi join 不会产生笛卡尔积 ！  inner join可能会产生笛卡尔积！**

1. in

select * from A
    where A.id in (select B.id from B)

它查出B表中的所有id字段并缓存起来.之后,检查A表的id是否与B表中的id相等,如果相等则将A表的记录加入结果集中,直到遍历完A表的所有记录.

可以看出,当B表数据较大时不适合使用in(),因为它会B表数据全部遍历一次.  
如: A表有 10000 条记录, B表有 1000000 条记录,那么最多有可能遍历 10000 \* 1000000 次, 效率很差.  
再如:A表有10000条记录,B表有100条记录,那么最多有可能遍历10000\*100次,遍历次数大大减少,效率大大提升.

结论：in()适合B表比A表数据小的情况。

2. exists

select A.* from A
    where exists (select B.id from B where A.id = B.id)

当B表比A表数据大时适合使用exists(),因为它没有那么遍历操作,只需要再执行一次查询就行.

如:A表有10000条记录,B表有1000000条记录,那么exists()会执行10000次去判断A表中的id是否与B表中的id相等.

再如:A表有10000条记录,B表有100条记录,那么exists()还是执行10000次,还不如使用in()遍历10000\*100次,因为in()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.

结论:exists()适合B表比A表数据大的情况

**3. left semi join 使用场景 【推荐使用 left semi join替代in  / exist】**

上面的例子 应该转换成：

select A.id from A left semi join B on A.id = B.id;

更多关于 left semi join 的，请参考：[https://blog.csdn.net/djy37010/article/details/72780827][https_blog.csdn.net_djy37010_article_details_72780827]

以上Hive 2.0.0

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70][]

hive在0.13版本以后开始支持更多的子查询，如in ，not in的子查询。如果我们用的hive不支持如in，exists,not in等子查询，很可能是0.13版本之前的旧版本

[Hive exists 用法][Hive exists]

[https_blog.csdn.net_djy37010_article_details_72780827]: https://blog.csdn.net/djy37010/article/details/72780827
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3UwMTAwMDIxODQ_size_16_color_FFFFFF_t_70]: /images/20210923/1b23abd639bf471b8915ba4345a21609.png
[Hive exists]: https://mp.csdn.net/console/editor/html/112428463