hive 各种 join （left outer join、join、full outer join）

一、概念

1、左连接 left outer join

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

2、连接join

找出左右相同同的记录。

3、全连接 full outer  join

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

会对结果去重，返回并集。

 

二、实验

1、准备数据

create external table IF NOT EXISTS temp_testjoin_ta
(
label string,
qu string
)
partitioned by (dt string)
row format delimited fields terminated by '\t'
stored as textfile;
ALTER TABLE temp_testjoin_ta ADD IF NOT EXISTS PARTITION (dt = '2014-08-08') location '/temp/jinlong10/testjoin/ta';

l1	q1
l1	q2


create external table IF NOT EXISTS temp_testjoin_tb
(
qu string,
inmyway string
)
partitioned by (dt string)
row format delimited fields terminated by '\t'
stored as textfile;
ALTER TABLE temp_testjoin_tb ADD IF NOT EXISTS PARTITION (dt = '2014-08-08') location '/temp/jinlong10/testjoin/tb/';

q1	i1
q1	i1
q1	i2
q1	i3
q2	i1
q2	i2
q3	i10

2、join

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 ;
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 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、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

 

4、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      i3
l1      q1      q1      i2
l1      q1      q1      i1
l1      q1      q1      i1
l1      q2      q2      i2
l1      q2      q2      i1
NULL    NULL    q3      i10