一、in和exists
select * from A where cc in(select cc from B)
1
select*fromAwhereccin(selectccfromB)
等同于:
select * from A where exists(select cc from B where cc=A.cc)
1
select*fromAwhereexists(selectccfromBwherecc=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,看下面的例子:
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
3
4
5
6
createtablet1(c1int,c2int);
createtablet2(c1int,c2int);
insertintot1values(1,2);
insertintot1values(1,3);
insertintot2values(1,2);
insertintot2values(1,null);
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
1
2
select*fromt1wherec2notin(selectc2fromt2); -->执行结果:无
select*fromt1wherenotexists(select1fromt2wheret2.c2=t1.c2) -->执行结果:13
正如所看到的,not in 出现了不期望的结果集,存在逻辑错误。所以,请尽量不要使用 not in(它会调用独立子查询),而尽量使用 not exists(它会调用关联子查询)。如果独立子查询中返回的任意一条记录含有 NULL 值,则查询将不返回任何记录。如果独立子查询字段有非空限制,这时可以使用 not in。
如果查询语句使用了 not in,那么对内外表都进行全表扫描,没有用到索引;而 not exists 的子查询依然能用到表上的索引。所以无论哪个表大,用 not exists 都比 not in 要快。
如何高效的写出一个替代 not exists 的 SQL 语句?
select colname … from A where a.id not in (select b.id from B)
1
selectcolname…fromAwherea.idnotin(selectb.idfromB)
使用 left join 替换
select colname … from A left join B on where a.id = b.id where b.id is null
1
selectcolname…fromAleftjoinBonwherea.id=b.idwhereb.idisnull
取出的结果集就是 A 表不在 B 表中的数据。
三、in与=的区别
select name from student where name in('zhang','wang','zhao');
1
selectnamefromstudentwherenamein('zhang','wang','zhao');
与
select name from student where name='zhang' or name='wang' or name='zhao'
1
selectnamefromstudentwherename='zhang'orname='wang'orname='zhao'
结果是相同的。
如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。