表a:project,1万条数据
表b:student,14万条数据
查询project表中的无效学号和空学号
代码一:
select * from project where student_id not in (SELECT id from student)
查询时间:352s
代码二:
SELECT * from
project a left join student b
on a.student_id = b.id
where b.id is null
查询时间:325s
代码三:
select *
from project a
where not exists ( select 1
from student b
where b.id = a.student_id)
查询时间:0.243s
注意代码二与以下代码的区别
SELECT * from
project a left join student b
on a.student_id = b.id
and b.id is null