mysql not in exists,SQL中的in、not in、exists及not exists的区别

一、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'

结果是相同的。

如果您觉得本站对你有帮助，那么可以支付宝扫码捐助以帮助本站更好地发展，在此谢过。