Hive不支持on语句中的非等值比较的解决办法

首先来看个例子

SELECT * 
FROM A
LEFT JOIN B 
ON A.id=B.id 
AND A.id2=B.id2 
AND A.startTime<= B.time AND B.time < A.endTime

因为Hive中不支持on语句的非等值比较，所以上述执行会出错。你以为修改成下面这个样子，把非等值的比较放到where字句上面就行了吧？来看看结果如何。

SELECT * 
FROM A
LEFT JOIN B ON A.id=B.id AND A.id2=B.id2
WHERE A.startTime<= B.time AND B.time < A.endTime

如果不幸left join后右表为NULL呢？结果是，where字句中的比较只要包含了NULL，就会比较结果为false，返回为空值。也就是得到的结果会比你想象的少。

SQL	结果
select 1 from dual where null=null	什么都没有
select 1 from dual where null<>null	什么都没有
select 1 from dual where 1=null	什么都没有
select 1 from dual where 1<>null	什么都没有

正确做法是加上nvl函数，并且把nvl(目标字段，null_if_value)中null_if_value一般设置为特殊字段"0"或者"-1"。

SQL	结果
select 1 from dual where nvl(null,-1)=nvl(null,-1)	1
select 1 from dual where nvl(null,-1)<>nvl(null,-1)	什么都没有
select 1 from dual where 1=nvl(null,-1)	什么都没有
select 1 from dual where 1<>nvl(null,-1)	1

弄明白原理之后来看看应该怎么修改原来的代码才能不出错。
一种方法是补上右表为null的那部分表，也就是union all右表为空的表。

SELECT * 
FROM A
LEFT JOIN B ON A.id=B.id AND A.id2=B.id2
WHERE A.startTime<= B.time AND B.time < A.endTime
UNION ALL
SELECT * 
FROM A
LEFT JOIN B ON A.id=B.id AND A.id2=B.id2
WHERE B.time IS NULL

另一种方法可以使用子查询来筛选出A表的startTime、endTime和B表的time都非空的字段后，再与原表left join。

SELECT *
FROM A
LEFT JOIN
(SELECT A.id 
FROM A JOIN B
ON A.id=B.id
WHERE A.startTime<= B.time AND B.time < A.endTime
)B
ON A.id=B.id

参考：
[1]: https://blog.csdn.net/wisdom_c_1010/article/details/79483380
[2]: https://blog.csdn.net/wisdom_c_1010/article/details/79539741
[3]: https://segmentfault.com/a/1190000006189065