Hive SQL查询之null踩坑

---

数据准备

---

1.hive sql中null是特殊情况，要单独处理

SELECT 
	*
FROM 
	student st
left join
	(SELECT
	sc.s_id,
	count(*) num1
FROM 
	score sc
group by
	sc.s_id)tmp on st.s_id = tmp.s_id
WHERE tmp.num1 != 3 or tmp.num1 is null
order by st.s_id DESC 

查询结果

---

2.不单独处理查询

SELECT 
	*
FROM 
	student st
left join
	(SELECT
	sc.s_id,
	count(*) num1
FROM 
	score sc
group by
	sc.s_id)tmp on st.s_id = tmp.s_id
WHERE tmp.num1 != 3
order by st.s_id DESC 

查询结果

---

3.观察1和2的查询结果可以看到，不等于的情况并不包含null.