hive各种join

一、概念

1、左连接 left outer join

以左边表为准，逐条去右边表找相同字段，如果有多条会依次列出

2、连接join

找出左右相同同的记录

3、全连接 full outer  join

包括两个表的join结果，左边在右边中没找到的结果（NULL），右边在左边没找到的结果

二、实验

1、准备数据

1. create external table IF NOT EXISTS temp_testjoin_ta  

2. (  

3. label string,  

4. qu string  

5. )  

6. partitioned by (dt string)  

7. row format delimited fields terminated by '\t'  

8. stored as textfile;  

9. ALTER TABLE temp_testjoin_ta ADD IF NOT EXISTS PARTITION (dt = '2014-08-08') location '/temp/jinlong10/testjoin/ta';  

10.   

11. l1  q1  

12. l1  q2  

13.   

14.   

15. create external table IF NOT EXISTS temp_testjoin_tb  

16. (  

17. qu string,  

18. inmyway string  

19. )  

20. partitioned by (dt string)  

21. row format delimited fields terminated by '\t'  

22. stored as textfile;  

23. ALTER TABLE temp_testjoin_tb ADD IF NOT EXISTS PARTITION (dt = '2014-08-08') location '/temp/jinlong10/testjoin/tb/';  

24.   

25. q1  i1  

26. q1  i1  

27. q1  i2  

28. q1  i3  

29. q2  i1  

30. q2  i2  

31. q3  i10  

2、join

1. select * from (select label,qu from temp_testjoin_ta where dt = '2014-08-08') ta join (select qu,inmyway from temp_testjoin_tb where dt = '2014-08-08') tb on ta.qu = tb.qu ;  

2. l1  q1  q1  i1  

3. l1  q1  q1  i1  

4. l1  q1  q1  i2  

5. l1  q1  q1  i3  

6. l1  q2  q2  i1  

7. l1  q2  q2  i2  

8.   

9.   

10. select * from (select label,qu from temp_testjoin_ta where dt = '2014-08-08') ta join (select qu,inmyway from temp_testjoin_tb where dt = '2014-08-08') tb on ta.qu = tb.qu group by label,inmyway;  

11. l1  i1  

12. l1  i2  

13. l1  i3  

3、left outer join

select * from (select label,qu from temp_testjoin_ta where dt = '2014-08-08') ta left outer join (select qu,inmyway from temp_testjoin_tb where dt = '2014-08-08') tb on ta.qu = tb.qu ;  

l1  q1  q1  i1  

l1  q1  q1  i1  

l1  q1  q1  i2  

l1  q1  q1  i3  

l1  q2  q2  i1  

l1  q2  q2  i2  

select * from (select label,qu from temp_testjoin_ta where dt = '2014-08-08') ta left outer join (select qu,inmyway from temp_testjoin_tb where dt = '2014-08-08') tb on ta.qu = tb.qu group by label,inmyway;   

l1  i1  

l1  i2  

l1  i3  

3、full outer  join

select *  from (select label,qu from temp_testjoin_ta where dt = '2014-08-08') ta  full outer  join (select qu,inmyway from temp_testjoin_tb  where dt = '2014-08-08') tb on ta.qu = tb.qu  ;   

l1  q1  q1  i1  

l1  q1  q1  i1  

l1  q1  q1  i2  

l1  q1  q1  i3  

l2  q1  q1  i1  

l2  q1  q1  i1  

l2  q1  q1  i2  

l2  q1  q1  i3  

l1  q123    NULL    NULL  

l1  q2  q2  i1  

l1  q2  q2  i2  

NULL    NULL    q3  i10