matlab left join,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

结果如下：

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

结果如下：

解释

在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

-- 对于left join，on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录，on后面的只作为关联条件。

select u.*,d.* from userinfo u left join deptinfo d on u.dept=d.id where u.age

-- where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了，条件不为真的就全部过滤掉

比较好的写法

select u.*,d.* from (select * from userinfo where age

如有纰漏，欢迎指正。