all_student表是所有的学生信息，fail_student表是不及格的学生信息
select * from all_student
image.png
select * from fail_student
image.png
查询及格的学生信息
select * from all_student
where name not in (
select name from fail_student
)
image.png
假如fail_student有一条name为null的记录
image.png
再次查询及格的学生信息，结果集为空
select * from all_student
where name not in (
select name from fail_student
)
image.png
解决方案
1.修改表结构，设置name字段为not null，并设置默认值
2.修改sql为
select * from all_student a
left join fail_student f on a.name = f.name
where f.name is null
image.png
网友评论