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

以上Hive 2.0.0

 

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