有两张表,user1和user2
user1:
hive> select * from user1;
OK
user1.id user1.name
1 name1
2 name2
3 name1
user2:
hive> select * from user2;
OK
user2.id user2.age
1 age1
1 age1
正常左连接
select a.*,b.* from user1 a left join user2 b on a.id=b.id;
a.id a.name b.id b.age
1 name1 1 age1
1 name1 1 age1
3 name1 NULL NULL
2 name2 NULL NULL
左连接 on条件里写常量等值操作
select a.*,b.* from user1 a left join user2 b on a.id=b.id and a.id=2;
a.id a.name b.id b.age
1 name1 NULL NULL
3 name1 NULL NULL
2 name2 NULL NULL
相当于在left join 之前,对on条件里的常量等值连接先做where过滤