hive各种join（leftouterjoin、join、fullouterjoin）

文章来源：http://www.it165.net/database/html/201408/7812.html

一、概念

1、左连接 left outer join

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

2、连接join

找出左右相同同的记录

3、全连接 full outer join

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

二、实验

1、准备数据

 

view source print ?

01. create external table IF NOT EXISTS temp_testjoin_ta

02. (

03. label string,

04. qu string

05. )

06. partitioned by (dt string)

07. row format delimited fields terminated by ' '

08. stored as textfile;

09. 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 ' '

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

 

 

view source print ?

01. 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 ;

02. l1  q1  q1  i1

03. l1  q1  q1  i1

04. l1  q1  q1  i2

05. l1  q1  q1  i3

06. l1  q2  q2  i1

07. l1  q2  q2  i2

08.  

09.  

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

 

 

view source print ?

01. 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 ;

02. l1  q1  q1  i1

03. l1  q1  q1  i1

04. l1  q1  q1  i2

05. l1  q1  q1  i3

06. l1  q2  q2  i1

07. l1  q2  q2  i2

08.  

09. 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;

10. l1  i1

11. l1  i2

12. l1  i3

3、full outer join

 

view source print ?

01. 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  ;

02. l1  q1  q1  i1

03. l1  q1  q1  i1

04. l1  q1  q1  i2

05. l1  q1  q1  i3

06. l2  q1  q1  i1

07. l2  q1  q1  i1

08. l2  q1  q1  i2

09. l2  q1  q1  i3

10. l1  q123    NULL    NULL

11. l1  q2  q2  i1

12. l1  q2  q2  i2

13. NULL    NULL    q3  i10