Hive various joins (left OUTER join, join, full outer join)

First, the concept

1. Left join the left outer join

To the left table, go to the right table to find the same field, if more than one will be listed in sequence

2. Connect Join

Find the same records around the same

3. Fully connected full outer join

Includes two table join results, left side not found in the right side results (NULL), the right side is not found on the left side of the result

Second, the experiment

1. Prepare the data

Create external Table IF not EXISTS temp_testjoin_ta
(
label string,
qu string
)
partitioned by (dt s Tring)
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 T Extfile;
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 to temp_testjoin_ta where dt = ' 2014-08-08 ') TA join (select Qu,inmyway from Temp_testjoi N_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 to temp_testjoin_ta where dt = ' 2014-08-08 ') TA left outer join (select Qu,inmyway from t EMP_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 joins (select Qu,inmyway from temp_testjoin_tb where dt = ' 2014-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 to 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