HIVE中join、semi join、outer join举例详解

转自 http://www.cnblogs.com/xd502djj/archive/2013/01/18/2866662.html

 

举例子：

hive> select * from zz0; 
111111 
222222 
888888 
hive> select * from zz1; 
111111 
333333 
444444 
888888

hive> select * from zz0 join zz1 on zz0.uid = zz1.uid; 
111111  111111 
888888  888888 
hive> select * from zz0 left outer join zz1 on zz0.uid = zz1.uid; 
111111  111111 
222222  NULL 
888888  888888 
hive> select * from zz0 right outer join zz1 on zz0.uid = zz1.uid; 
NULL 
111111  111111 
NULL    333333 
NULL    444444 
888888  888888 
hive> select * from zz0 full outer join zz1 on zz0.uid = zz1.uid; 
NULL 
111111  111111 
222222  NULL 
NULL    333333 
NULL    444444 
888888  888888 
hive> select * from zz0 left semi join zz1 on zz0.uid = zz1.uid; 
111111  111111 
888888  888888