HIVE 中内连接(join on )与left semi join 查询结果不一致的分析（两者区别）

一、第一种讲解

LEFT SEMI JOIN （左半连接）是 IN/EXISTS 子查询的一种更高效的实现。

Hive 当前没有实现 IN/EXISTS 子查询，所以可以用 LEFT SEMI JOIN 重写你的子查询语句。

示例

可以改写为

特点

1、left semi join 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行。

2、left semi join 是只传递表的 join key 给 map 阶段，因此left semi join 中最后 select 的结果只许出现左表。

3、因为 left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过，而 join 则会一直遍历。这就导致右表有重复值得情况下 left semi join 只产生一条，join 会产生多条，也会导致 left semi join 的性能更高。 

比如以下A表和B表进行 join 或 left semi join，然后 select 出所有字段，结果区别如下：

二、第二种讲解

重点：由于子表（tmall_data_fdi_dim_main_auc）中存在重复的数据，当使用JOIN ON的时候，A,B表会关联出两条记录，应为ON上的条件符合； 
而是用LEFT SEMI JOIN 当A表中的记录，在B表上产生符合条件之后就返回，不会再继续查找B表记录了，所以如果B表有重复，也不会产生重复的多条记录。 

由于HIVE中都是等值连接，在JOIN使用的时候，有两种写法在理论上是可以达到相同的效果的，但是由于实际情况的不一样，子表中数据的差异导致结果也不太一样。 
  
写法一： 

Java代码 

1. select  
2.            a.bucket_id,  
3.         a.search_type,  
4.             a.level1,  
5.         a.name1,  
6.         a.level2,  
7.         a.name2,  
8.         cast((a.alipay_fee) as double) as zhuliu_alipay,  
9.         cast(0 as double) as total_alipay  
10.         from tmall_data_fdi_search_zhuliu_alipay_cocerage_bucket_1 a  
11.      left semi join  
12.      tmall_data_fdi_dim_main_auc b  
13.      on (a.level2 = b.cat_id2  
14.          and a.brand_id = b.brand_id  
15.        and b.cat_id2 > 0  
16.          and b.brand_id > 0  
17.          and b.max_price = 0  
18.      )  

3121 条 

写法二： 
Java代码 

1. select  
2.            a.bucket_id,  
3.         a.search_type,  
4.             a.level1,  
5.         a.name1,  
6.         a.level2,  
7.         a.name2,  
8.         cast((a.alipay_fee) as double) as zhuliu_alipay,  
9.         cast(0 as double) as total_alipay  
10.         from tmall_data_fdi_search_zhuliu_alipay_cocerage_bucket_1 a  
11.      join   tmall_data_fdi_dim_main_auc b  
12.      on (a.level2 = b.cat_id2  
13.          and a.brand_id = b.brand_id)  
14.   where  b.cat_id2 > 0  
15.          and b.brand_id > 0  
16.          and b.max_price = 0  

结果是：3142 


这两种写法带来的值居然不是相等的，我一直以为理解这两种方式的写法是一样的， 
但是统计的结果却是不一样的。 
目前还没有搞清楚是什么原因，谁能那个搞清楚呢。 


经过一层一层的查找，发现是由于子表（tmall_data_fdi_dim_main_auc）中存在重复的数据，当使用JOIN ON的时候，A,B表会关联出两条记录，应为ON上的条件符合； 
而是用LEFT SEMI JOIN 当A表中的记录，在B表上产生符合条件之后就返回，不会再继续查找B表记录了，所以如果B表有重复，也不会产生重复的多条记录。 

大多数情况下JOIN ON 和left semi on是对等的，但是在上述情况下会出现重复记录。大家以后可要小心。