mysql的not in 转换left join时候的踩坑记

在使用sql进行not in查询时通常可以转化为left join来进行sql优化，比如：

select * from A where id not in(select id from B)

通常建议转换为一下语句：

select * from A left join B on a.id=b.id where b.id is null

但是当not in后面存在null的情况下会导致转换出来的sql查询结果不对，如下示例：

现在有一个学生表和课程表：

 

这种数据的情况下，如下两个sql查询语句结果是一样的，是可以直接转换的：

select * from t_class where stu_id not in(select stu_id from t_student);



select * from t_class a left join  t_student b on a.stu_id=b.stu_id where b.stu_id is null;

 

 

但是如果在学生表中的stu_id字段存在null值，就会导致查询结果有问题，不能直接转换:

select * from t_class where stu_id not in(select stu_id from t_student);
查不出来数据：

select * from t_class a left join  t_student b on a.stu_id=b.stu_id where b.stu_id is null;

可以查出来数据:

 对于这种情况，需要改写not in才能保证转换之后的结果一样：

select * from t_class where stu_id not in(select stu_id from t_student where stu_id is not null);
这样就可以查出来结果，并且与转换之后的left join结果一样：