hive：几个join的差别汇总

employee表

department表

1.笛卡尔积

select * from employee a ,department b;

共4*2=8条结果。

2.inner join

select * from employee a 
inner join department b
on a.DepartmentId=b.id; 

等价于

select * from employee a ,department b
where a.DepartmentId=b.id; 

3.left join

返回满足连接条件的左边表的所有记录，若左边表的某些记录在右边表中没有匹配记录，右边表则显示null。

（1）如果不指定连接条件，left join的结果将和笛卡尔积的结果相同。

select * from employee a 
left join department b;

（2）employee left join department

select * from employee a 
left join department b
on a.DepartmentId=b.id;

（3）department left join employee

交换一下left join左右两表的位置：
department left join employee的结果 = employee right join department的结果（显示位置不同）

select * from department a
left join  employee b 
on a.id=b.departmentId;

4.right join

返回满足连接条件的右边表的所有记录，若右边表的记录在左边表中没有匹配记录，左边表则显示null。

select * from employee a 
right join department b;

select * from employee a 
right join department b
on a.departmentId=b.id;

等价于

select * from employee a 
left join department b
on a.departmentId=b.id
where b.name is not null;