sql优化技巧之 not in 改 left join

在sql语句中，not in是经常会⽤到的⼀种写法，因为这种写法很直观，容易理解。

但如果不注意的话，很容易写出错误的sql，⽽且性能存在严重问题，所以，不建议使⽤not in，要尽量把 not in写法，改为left join。下面给个例子

CREATE TABLE emp
(
empid INT NOT NULL PRIMARY KEY CLUSTERED,
empname VARCHAR(20) NOT NULL,
tel VARCHAR(20) null
)
INSERT INTO dbo.emp
VALUES
(1, '张三','12'),
(2, '李四','34'),
(3, '王五','56'),
(4, '孙六','78'),
(5, '李二','90');

CREATE TABLE org
(
orgid INT NOT NULL PRIMARY KEY CLUSTERED,
orgname VARCHAR(30) NOT NULL,
parentid INT NULL,
empid INT NULL
)
INSERT INTO org(orgid,orgname,parentid,empid)
VALUES
(1, '部长', NULL,1),
(2, '副部长', 1,2),
(3, '组长', 1,3),
(5, '员工', 4,null)

如上sql创建两个表，一个组织表，一个雇员表。现在要查询，emp表中有，但是在org中不存在的empid，用not in 写法自然而然的写出如下的sql

SELECT e.empid,
       e.empname
FROM emp e
WHERE e.empid NOT IN (SELECT empid FROM org)

这个时候理论上讲，孙六和李二的id不在org表中，理应被查出来，来看看查询结果

这是不是要打个问号了，这⾥涉及到not in写法的⼀个坑，就是当not in ⼦查询中有null值，那么整个查询的结果就是空的。由于org表中员工的empid是NULL,所以就找不到值了。改写一下，过滤掉empid是null的情况，查询就有数据了。

SELECT e.empid,
       e.empname
FROM emp e
WHERE e.empid NOT IN (SELECT empid FROM org where empid is not null)

看看执行计划，sqlserver的解析是将not in 转成了 left Anti Semi Join。这个是左连接吧，但是 Semi 是什么意思？看下这两个次的意思。Anti 取反，Semi 半独立。

是不是先用左连接后取反的意思？就是说emp表中的每条empid，如果在org表中有记录就不返回。

我们再来看看将not in 改成左连接的写法

SELECT e.empid,
       e.empname
FROM emp e
LEFT JOIN org o
ON o.empid = e.empid
WHERE o.empid IS NULL

执行计划上看少了top，但增加了帅选器。

跟not in是完全一样的开销。这里有个差异就是执行嵌套循环最后返回的行数是20行再进行筛选，而not in 的执行嵌套循环后返回的是最终结果2行。

这里可以看出什么呢，就是not in 的时候要两个表逐行empid的对比Anti ，semi），不在的才返回，在的话就不反回了。

而左连接呢是逐行拼接，不需要逐行empid的对比。链接完就返回去。最后再通过一次empid is null 进行过滤。

数据量少的时候，由于逐行对比的操作没那么多，返回的数据也少，也不需要再进行一次过滤，not in 比left join 的效率要高一些。

但是当数据量大的时候，相比联查，逐行对比的开销的大的多了。