文章来源:http://www.it165.net/database/html/201408/7812.html
一、概念
1、左连接 left outer join
以左边表为准,逐条去右边表找相同字段,如果有多条会依次列出
2、连接join
找出左右相同同的记录
3、全连接 full outer join
包括两个表的join结果,左边在右边中没找到的结果(NULL),右边在左边没找到的结果
二、实验
1、准备数据
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
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
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
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