hive中表关联时null和‘’的问题

在hive开发过程中，我们经常会有这样的疑问，a表关联b表，我想找到a表中的键在b表中不存在的数据，这时候用a表左连b表，然后where条件中加入b.flied is null。 但是细心的人会有这样的疑问，两个表关联之后，如果关联不上，b表字段的返回值真的都是null吗？？？

下面我们来做个测试，测试表tmp.tmp_citys，表结构如下：

表中的数据：

sql：
select
t1.city_id,t1.city_name ,t2.city_id ,t2.city_name ,
t2.city_id is null city_id为null,
t2.city_id is not null city_id不为null,
t2.city_id = ‘’ city_id为‘’,
t2.city_id <> ‘’ city_id不为‘’,
t2.city_name is null city_name为null,
t2.city_name is not null city_name不为null,
t2.city_name = ‘’ city_name为‘’,
t2.city_name <> ‘’ city_name不为‘’

from (select * from tmp.tmp_citys
where city_id = 1)t1
left join (select * from tmp.tmp_citys
where city_id <> 1)t2
on t1.city_id = t2.city_id ;
t1表中是city_id = 1 的数据，t2表中是city_id <>1的数据，显然，t1表左连t2表，t2表的字段值将什么也没有，我们来看下查询结果：

这里，t2表的第一个字段city_id，int类型，返回值不是null，但也不等于null；跟‘’比较的时候，怎么都是false，按照传统数据库的逻辑，我们限制city_id is null就可以筛选出键在t1表存在而t2表不存在的数据，显然，通过实验，我们发现并不可以，city_id is null返回false，连主表的数据都看不到，hive狠不狠。。。
再看第二个字段city_name，string类型，返回值是null，因为null和‘’比也是null，所以最后连个返回结果是空。看来，string类型的字段还是靠谱些。

下面我来执行下面的sql：
select t1.* ,t2.*
from (select * from tmp.tmp_citys
where city_id = 1)t1
left join (select * from tmp.tmp_citys
where city_id <> 1)t2
on t1.city_id = t2.city_id
where t2.city_id is null ；
大家猜猜结果是什么？

你没看错，空！！！这对用惯了传统数据库的人来说，这有点不可能，可事实就是这样，至于返回值是啥我也不知道。。

再运行另一个sql：
select t1.* ,t2.*
from (select * from tmp.tmp_citys
where city_id = 1)t1
left join (select * from tmp.tmp_citys
where city_id <> 1)t2
on t1.city_id = t2.city_id
where t2.city_name is null ;
这次的返回值：

这次的结果还是可以接受的，那上面俩个差哪了呢？数据类型，字段的数据类型会与返回值有关系。这里我们可以推断一下，在关联不上的情况下，string默认返回的是null，int默认返回的就不知道了。

经常在开发中看见这样的句式
select a.*
from a
left join b
on a.fld1 = b.fld1
where b.fld1 is null or b.fld1 = ‘’ ;
从上面例子中我们可以得出，b.fld1的返回值根本不会是‘’，所以where子句可以简化成where b.fld1 is null，但是要补充一句，要注意b.fld的字段类型哦。

综上总结几点，仅供参考：
1 不要拿数值类型的字段和‘’做比较，因为横竖都是false，最后出错都不知道出在哪里。
2 俩个表关联的时候，想要筛选数据，最好在where中用string类型的字段，千万不要用数值类型！！！

ps：个人愚见，如有错误之处请大神指正。