一、in 和exists
场景:筛选出student 表中有考试成绩的学生信息
1、使用IN
SELECT * FROM student s WHERE stuid IN (SELECT stuid FROM score ss WHERE ss.stuid = s.stuid)
执行顺序是FROM student表,执行where语句里的in 子查询,将子查询中的内表score与外表student作笛卡尔积,再根据子查询中的 where语句ss.stuid = s.stuid筛选出结果
2、使用EXISTS
SELECT * FROM student s WHERE EXISTS (SELECT stuid FROM score ss WHERE ss.stuid = s.stuid)
先遍历外表student,即执行sql语句:
select * from student s
根据表的每一条记录,执行以下sql语句,逐行判断是否满足where条件
EXISTS(select stuid from score ss where ss.stuid = s.stuid)
满足的留下,不满足的删去
结论:当外表行数比较少时,内表较大且有索引时用EXISTS;
当外表行数大且有索引,内表较小时用IN
二、not in 和 not exists
使用not in,内外表都要进行全表扫描, 没有用到索引;
而not extsts 的子查询依然能用到表上的索引
结论:not exists比 not in快