HiveSql（3）HiveSQL与oracle SQL的区别

HiveSQL与oracle SQL的区别

1、hive sql暂时不能使用in，不支持where字句中的子查询。可以利用leftsemi join来实现in。

Hive:

Select * from employee e left semi joindept d on (e.deptno=d.deptno)

小表放在join左边。

Left semi join是in/exist在hive中的一种更为高效的实现。

Oracle：

Select * from employee e where e.deptno in (selectd.deptno from dept d))

 

2、hive sql同样不支持not in。可以利用leftouter join来实现。

Hive:

Select e.* from employee e left outer joindept d on(e.deptno=d.deptno) where d.deptno is null

Oracle:

Select * from employee e where e.deptno notin (select d.deptno from dept d))

 

3、join上的不同

Hive:

Select * from employee e join dept d on (e.id=d.id)

Oracle:

Select * from employee e,dept d where e.id=d.id

 

4、rank函数的不同

Hive：

Select e.name,e.dept,e.salary,rank(e.dept,e.salary)

From (Select name,dept,salary from employeedistributed by dept sort by dept,salary desc)e

Oracle:

Select name,dept,salary,rank() over(partitionby dept order by salary desc) from employee

 

5、min函数的不同

Hive:

Select dept,tmp.m

From (Select dept,min(salary) m fromemployee group by dept) tmp

On employee.dept=tmp.dept

Oracle:

Select dept,min(salary) over (partition bydept) from employee