一、in和exists
1 |
select * from A where cc in(select cc from B) |
等同于:
1 |
select * from A where exists(select cc from B where cc=A.cc) |
区分 in 和 exists 主要是造成了驱动顺序的改变(这是性能变化的关键),如果是 exists,那么以外层表为驱动表,先被访问,如果是 IN,那么先执行子查询。所以 IN 适合于外表大而内表小的情况;EXISTS 适合于外表小而内表大的情况。
二、not in和not exists
关于 not in 和 not exists,推荐使用 not exists,不仅仅是效率问题,not in 可能存在逻辑问题。
not in逻辑上不完全等同于not exists,如果你误用了not in,小心你的程序存在致命的BUG,看下面的例子:
1 2 3 4 5 6 |
create table t1(c1 int,c2 int); create table t2(c1 int,c2 int); insert into t1 values(1,2); insert into t1 values(1,3); insert into t2 values(1,2); insert into t2 values(1,null); |
1 2 |
select * from t1 where c2 not in(select c2 from t2); -->执行结果:无 select * from t1 where not exists(select 1 from t2 where t2.c2=t1.c2) -->执行结果:1 3 |
正如所看到的,not in 出现了不期望的结果集,存在逻辑错误。所以,请尽量不要使用 not in(它会调用独立子查询),而尽量使用 not exists(它会调用关联子查询)。如果独立子查询中返回的任意一条记录含有 NULL 值,则查询将不返回任何记录。如果独立子查询字段有非空限制,这时可以使用 not in。
如果查询语句使用了 not in,那么对内外表都进行全表扫描,没有用到索引;而 not exists 的子查询依然能用到表上的索引。所以无论哪个表大,用 not exists 都比 not in 要快。
如何高效的写出一个替代 not exists 的 SQL 语句?
1 |
select colname … from A where a.id not in (select b.id from B) |
使用 left join 替换
1 |
select colname … from A left join B on where a.id = b.id where b.id is null |
取出的结果集就是 A 表不在 B 表中的数据。
三、in与=的区别
1 |
select name from student where name in('zhang','wang','zhao'); |
与
1 |
select name from student where name='zhang' or name='wang' or name='zhao' |
结果是相同的。