hive中left/right join on连接中and与where的使用问题

很多同学在进行表关联的时候，and和where的使用有时候分不清，在这里实操记录下。

创建人员信息表并写入数据

create table tmp.userinfo
(
id string,
name string,
age string,
dept string
);

insert into tmp.userinfo values (\'1\',\'张三\',\'12\',\'1\'),(\'2\',\'张三1\',\'223\',\'2\'),(\'3\',\'张三2\',\'23\',\'3\'),(\'4\',\'张三3\',\'243\',\'4\'),(\'5\',\'张三4\',\'523\',\'5\');

创建部门表并写入数据

create table tmp.deptinfo
(
id string,
name string
);

insert into tmp.deptinfo values(\'1\',\'1\'),(\'2\',\'2\'),(\'3\',\'3\'),(\'4\',\'4\'),(\'5\',\'5\'),(\'6\',\'6\')

• 现象

查询语句 select u.*,d.* from userinfo u left join deptinfo d on u.dept=d.id and u.age<\'23\';

结果如下：

查询语句 select u.*,d.* from userinfo u left join deptinfo d on u.dept=d.id where u.age<\'23\';

结果如下：

• 解释

在join中on是起到关联关系的连接作用，on后面指定的是关联条件，比如select * from A  a left join B  b on a.clientno=b.clientno 就是当两张表clientno相同才能关联上，

如果后面再跟上and的话，就是要同时满足这两个条件，如  select * from A  a left join B  b on a.clientno=b.clientno and a.sex=b.sex，其实这个可以将on后面看成一个整体：select * from A  a left join B  b on (a.clientno=b.clientno and a.sex=b.sex)

• 总结

select u.*,d.* from userinfo u left join deptinfo d on u.dept=d.id and u.age<\'23\';
-- 对于left join，on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录，on后面的只作为关联条件。

select u.*,d.* from userinfo u left join deptinfo d on u.dept=d.id where u.age<\'23\';
-- where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉

比较好的写法
select u.*,d.* from (select * from userinfo where age<\'23\') u left join deptinfo d on u.dept=d.id;

如有纰漏，欢迎指正。