sql优化in和notin语句

sql优化in和notin语句
WHY？
IN 和 NOT IN 是⽐较常⽤的关键字，为什么要尽量避免呢？
1、效率低
可以参看我之前遇到的⼀个例⼦（）
2、容易出现问题，或查询结果有误（不能更严重的缺点）
以 IN 为例。

建两个表：test1 和 test2
create table test1 (id1 int)
create table test2 (id2 int)
insert into test1 (id1) values (1),(2),(3)
insert into test2 (id2) values (1),(2)
我想要查询，在test2中存在的 test1中的id 。

使⽤IN的⼀般写法是：
select id1 from test1
where id1 in (select id2 from test2)
结果是： OK ⽊有问题！
但是如果我⼀时⼿滑，写成了：
select id1 from test1
where id1 in (select id1 from test2)
不⼩⼼把id2写成id1了，会怎么样呢?
结果是： EXCUSE ME！为什么不报错？
单独查询 select id1 from test2 是⼀定会报错: 消息 207，级别 16，状态 1，第 11 ⾏列名 'id1' ⽆效。

然⽽使⽤了IN的⼦查询就是这么敷衍，直接查出 1 2 3
这仅仅是容易出错的情况，⾃⼰不写错还没啥事⼉，下⾯来看⼀下 NOT IN 直接查出错误结果的情况：
给test2插⼊⼀个空值：
insert into test2 (id2) values (NULL)
我想要查询，在test2中不存在的 test1中的id 。

select id1 from test1
where id1 not in (select id2 from test2)
结果是：空⽩！显然这个结果不是我们想要的。

我们想要3。

为什么会这样呢？
原因是：NULL不等于任何⾮空的值啊！如果id2只有1和2，那么3<>1 且 3<>2 所以3输出了，但是 id2包含空值，那么 3也不等于NULL 所以它不会输出。

（跑题⼀句：建表的时候最好不要允许含空值，否则问题多多。

）
HOW？
1、⽤ EXISTS 或 NOT EXISTS 代替
select * from test1
where EXISTS (select * from test2 where id2 = id1 )
select * FROM test1
where NOT EXISTS (select * from test2 where id2 = id1 )
2、⽤JOIN 代替
select id1 from test1
INNER JOIN test2 ON id2 = id1
select id1 from test1
LEFT JOIN test2 ON id2 = id1
where id2 IS NULL
妥妥的没有问题了！
PS：那我们死活都不能⽤ IN 和 NOT IN 了么？并没有，⼀位⼤神曾经说过，如果是确定且有限的集合时，可以使⽤。

如 IN （0，1，2）。