hive面试题——LEFT JOIN ON后面加WHERE或者AND

笔试的时候会碰到join on后面会跟where或者and的笔试题，刚开始以为是一样的，但是后面本人测试了一下，发现不一样，现做一下记录，方便自己记忆，也希望给不了解的看官介绍一下，了解的看官温故知新。

目录

数据准备

验证内容

LEFT JOIN(RIGHT JOIN)验证

LEFT JOIN(RIGHT JOIN)只带ON的HQL

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

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

LEFT JOIN(RIGHT JOIN)  ON 1=1

LEFT JOIN(RIGHT JOIN)  ON 1=2

left join（right join）小结

INNER JOIN验证

INNER JOIN只带ON的HQL

INNER JOIN只ON和AND的HQL

INNER JOIN只ON和WHERE的HQL

inner join小结

---

数据准备

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 '\t';

create table if not exists dept1
(
dept_no string
,dept_name string
)row format delimited fields terminated by '\t';

验证内容

LEFT JOIN(RIGHT JOIN)验证

• 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

• 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

• 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

• 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    

• 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

left join（right join）小结

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

INNER JOIN验证

• 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

• 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

• 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

inner join小结

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