hive面试题-left-join的坑
目录
hive面试题–left join的坑
student 表：
课程表course:
1、key为null, 不关联
select * from student s left join course c on s.id = c.s_id;
2、on中过滤条件 与 where 过滤条件区别
on and c.id<>‘1001’ 先过滤右表数据，然后与左表关联
select * from student s left join course c on s.id = c.s_id and c.id<>'1001';
where c.id<>‘1001’ 先关联，然后过滤结果数据
select * from student s left join course c on s.id = c.s_id where c.id<>'1001';
模拟数据：
drop table if exists student;
create table student (
id int,
name STRING
);
insert into student values (1, 'chb'), (2, 'lis'), (3, 'wzng'), (4, 'sdsd'),(cast(null as int), 'sdok');
create table course(
id STRING,
name STRING,
s_id int
);
insert into course values ('1001', 'c1', 1), ('1001', 'c1', 2), ('1002', 'c2', 3),('1002', 'c2', 3),('1003', 'c3', 5),('1003', 'c3', cast(null as int));