【Hive】（七）Hive的join

SQL Joins

SQL中的连接查询有inner join(内连接）、left join(左连接)、right join（右连接）、full join（全连接）left semi join(左半连接)五种方式，它们之间其实并没有太大区别，仅仅是查询出来的结果有所不同。

left join(左联接)： 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接)： 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) ： 只返回两个表中联结字段相等的行
full join(全连接)： 返回两个表中的所有字段。空缺的字段为NULL
left semi join(左半连接)： 并不拼接两张表，两个表对 on 的条件字段做交集，返回前面表的记录
  union和full join on的区别，“full join on 列合并和 union 行合并”：

1） full join 使用on条件时，select * 相当于把两个表(左表有m列p行和右表有n列q行)的所有列拼接成了一个有m+n列的结果表。
2）而union相当于把两个查询结果(左查询结果表有m列p行和右查询结果表有n列q行)的所有行进行了拼接，形成具有p+q行的查询结果。
  测试数据

测试数据如下：
data_table1.txt
1    name1
2    name2
3    name3
4    name4
5    name5
6    name6

data_table2.txt
1    11
1    12
1    13
2    11
2    14
3    12
3    15
4    12
4    13
5    14
5    16
7    13
7    15

left join

select [fileds] 
from tableA a 
left join tableB b
on a.key=b.key

SQL:

select * 
from table1 left outer join table2 
on(table1.student_no=table2.student_no);

select * 
from table1 left  join table2 
on(table1.student_no=table2.student_no);

select [fileds] 
from tableA a 
left join tableB b
on a.key=b.key
where b.key IS NOT NULL

SQL

select * 
from table1 left outer join table2 
on table1.student_no=table2.student_no 
where table2.student_no is not null;

right join

select [fileds] 
from tableA a 
right join tableB b
on a.key=b.key

SQL:

select * 
from table1 right join table2 
on(table1.student_no=table2.student_no);

select * 
from table1 right  outer join table2 
on(table1.student_no=table2.student_no);

结果

右表独有

select [fileds] 
from tableA a 
right join tableB b
on a.key=b.key
where a.key IS NULL

SQL

select * 
from table1 right join table2 
on(table1.student_no=table2.student_no) 
where table1.student_no is not null;

结果

inner join

1）如果有on 条件的话，则两边关联只取交集。

select * 
from tableA a join tableB b 
on a.student_no=b.student_no;

2）笛卡尔积：如果没有on条件的话，则是左表和右表的列通过笛卡尔积的形式表达出来，下面两个sql就是求笛卡尔积：

 select * from tableA join tableB;
 select * from tableA inner join tableB;

比如tableA有m行，tableB有n行，最终的结果将有 m*n行

select [fileds] 
from tableA a 
inner join tableB b
on a.key=b.key

SQL

select * 
from table1 inner join table2 
on table1.student_no=table2.student_no;

select * 
from table1 join table2 
on table1.student_no=table2.student_no;

full join

select [fileds] 
from tableA a 
full outer join tableB b
on a.key=b.key;

SQL

select * 
from table1 full join table2 
on table1.student_no=table2.student_no;

取并集后去交集

select [fileds] 
from tableA a 
full outer join tableB b
on a.key=b.key
where a.key IS NULL OR b.key IS NULL

SQL

select * 
from table1 left outer join table2 
on table1.student_no=table2.student_no 
where table2.student_no is null;

select * 
from table1 right outer join table2 
on table1.student_no=table2.student_no 
where table1.student_no is null;

select * from table1 left outer join table2 
 	on table1.student_no=table2.student_no 
	where table2.student_no is null
UNION
select * from table1 RIGHT  outer join table2
	on table1.student_no=table2.student_no 
	where table1.student_no is null;

left semi join

 left semi join 的限制是， JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行

SQL1:

SELECT table1.student_no, table1.student_name 
FROM table1 LEFT SEMI JOIN table2 
on ( table1.student_no =table2.student_no);

SQL2:

SELECT * 
FROM table1 LEFT SEMI JOIN table2 
on ( table1.student_no =table2.student_no);

结果：

PS:只存在 left SEMI JOIN，不存在SEMI JOIN 和 right SEMI JOIN。

例子来源：https://www.pianshen.com/article/5498264012/