Hive面试题(9)：LEFT JOIN ON后面加WHERE或者AND

来源：https://blog.csdn.net/qq_16590169/article/details/103332505

1 数据准备

emp1表：
1       张三    A1
2       李四    A2
3       王五    A3
4       小明    B1
dept1表：
A1      财务
A2      人事
 
create table if not exists emp1
(
emp_no string
,name string
,dept_no string
)row format delimited fields terminated by '	';
 
create table if not exists dept1
(
dept_no string
,dept_name string
)row format delimited fields terminated by '	';

2 LEFT JOIN(RIGHT JOIN)验证

2.1 LEFT JOIN(RIGHT JOIN)只带ON的HQL

select t1.name,t1.dept_no,t2.dept_no from emp1 t1
left join dept1 t2
on t1.dept_no = t2.dept_no;
 
张三    A1      A1
李四    A2      A2
王五    A3      NULL
小明    B1      NULL

2.2 LEFT JOIN(RIGHT JOIN)只ON和AND的HQL

select t1.name,t1.dept_no,t2.dept_no from emp1 t1
left join dept1 t2
on t1.dept_no = t2.dept_no
and t2.dept_no='A2';
 
张三    A1      NULL
李四    A2      A2
王五    A3      NULL
小明    B1      NULL

2.3 LEFT JOIN(RIGHT JOIN)只ON和WHERE的HQL

select t1.name,t1.dept_no,t2.dept_no from emp1 t1
left join dept1 t2
on t1.dept_no = t2.dept_no
where t2.dept_no='A2';
 
李四    A2      A2

2.4 LEFT JOIN(RIGHT JOIN) ON 1=1

select t1.name,t1.dept_no,t2.dept_no from emp1 t1
left join dept1 t2
on 1=1;
 
张三    A1      A1
张三    A1      A2
张三    A1
李四    A2      A1
李四    A2      A2
李四    A2
王五    A3      A1
王五    A3      A2
王五    A3
小明    B1      A1
小明    B1      A2
小明    B1

2.5 LEFT JOIN(RIGHT JOIN) ON 1=2

select t1.name,t1.dept_no,t2.dept_no from emp1 t1
left join dept1 t2
on 1=2;
 
张三    A1      NULL
李四    A2      NULL
王五    A3      NULL
小明    B1      NULL

2.6 left join（right join）小结

做left join（right join）的时候，只用on连接，就是左边全部展示，右边有关联数据就显示，没有就显示null；用on和and连接，也是左边全部显示，右边符合and条件的显示，否则显示为null；用on和where连接，达到了过滤的效果，只显示符合where条件的数据。
做left join（right join）的时候，使用on后恒为真，就会将两表进行笛卡尔积（等价于直接join，没有on关联）；使用on后恒不为真，只显示左表的全部，右边关联的显示全为null。

3 INNER JOIN验证

3.1 INNER JOIN只带ON的HQL

select t1.name,t1.dept_no,t2.dept_no from emp1 t1
inner join dept1 t2
on t1.dept_no = t2.dept_no;
 
张三    A1      A1
李四    A2      A2

3.2 INNER JOIN只ON和AND的HQL

select t1.name,t1.dept_no,t2.dept_no from emp1 t1
inner join dept1 t2
on t1.dept_no = t2.dept_no
and t2.dept_no='A2';
 
李四    A2      A2

3.2 INNER JOIN只ON和WHERE的HQL

select t1.name,t1.dept_no,t2.dept_no from emp1 t1
inner join dept1 t2
on t1.dept_no = t2.dept_no
where t2.dept_no='A2';
 
李四    A2      A2

3.3 inner join小结

做inner join的时候，使用on关联，会把关联表符合on条件的内容显示，不符合的全部过滤；使用on和and或者on和where效果是一样的。