sql中的exist和in查询记录

一、示例

对于两个表A、B，以id作为两表的关联条件作查询，三条sql语句

1、select * from A where id in (select id from B)  使用in
2、select * from A where exists(select B.id from B where B.id=A.id)  使用exists
3、select A.* from A inner join B on A.id=B.id 使用inner join

第一条语句使用了A表的索引；
第二条语句使用了B表的索引；
第三条语句同时使用了A表、B表的索引；
如果A、B表的数据量不大，那么这三个语句执行效率几乎无差别；
如果A表大，B表小，显然第一条语句效率更高，反之，则第二条语句效率更高；
第三条语句尽管同时使用了A表、B表的索引，但是扫描次数是笛卡尔乘积，效率最差。

二、总结

• IN 和 EXISTS 是两种不同的条件判断方式，用于检查子查询的结果
• IN 运算符用于比较单个值或列与一组值，而 EXISTS 运算符用于检查子查询是否返回结果。
• IN 运算符将整个列表与主查询比较，而 EXISTS 运算符只需找到符合条件的一行即可。
• IN 运算符适用于静态数据列表，而 EXISTS 运算符适用于动态或复杂的子查询。
• 如果查询的两个表大小相当，那么用 in 和 exists 差别不大。
• 如果两个表中一个较小，一个是大表，则子查询表大的用 exists，子查询表小的用 in。

有两个简单例子，以说明 “exists”和“in”的效率问题
select * from A where exists(select 1 from B where A.id = B.id);
A数据量小而B数据量非常大时，A<<B 时，1) 的查询效率高。
select * from A where A.id in (select B.id from B);
A数据量非常大而B数据量小时，A>>B 时，2) 的查询效率高。

三、注意 not in

1、not in 索引问题

首先not in 是不能走索引的，因此对于

select * from A where A.id not in (select B.id from B)

这样的子查询，是不能够利用到A表id字段的索引的，内外表都要进行全表扫描。而 not extsts 的子查询依然能用到子查询表上的索引。

2、null值问题

create table A(id1 int)
create table B(id2 int)

insert into A(id1) values (1),(2),(3)
insert into B(id2) values (1),(2)

第一次查询的sql

select id1 from A
where id1 not in (select id2 from B)
结果是
id
3

当给B插入一个空值，同样第二次执行子查询语句

insert into B (id2) values (NULL)
结果是为空
id

原因是：NULL不等于任何非空的值啊！如果第一次查询下id2只有1和2， 那么3不等于1且3等于2 所以3作为结果输出了，但是第二次查询id2包含空值，那么3也不等于NULL 所以它不会输出。

3、替代方案

使用not exists代替

select * FROM A
 where NOT EXISTS (select * from B where id2 = id1 )