SQLServer 优化SQL语句 in 和not in的替代方案
字体：[ ] 类型：转载 时间：
用IN写出来的SQL的优点是比较容易写及清晰易懂，这比较适合现代软件开发的风格。
但是用IN的SQL性能总是比较低的，从SQL执行的步骤来分析用IN的SQL与不用IN的SQL有以下区别： SQL试图将其转换成多个表的连接，如果转换不成功则先执行IN里面的子查询，再查询外层的表记录，如果转换成功则直接采用多个表的连接方式查询。由此可见用IN的SQL至少多了一个转换的过程。一般的SQL都可以转换成功，但对于含有分组统计等方面的SQL就不能转换了。 推荐在业务密集的SQL当中尽量不采用IN操作符 NOT IN 此操作是强列推荐不使用的，因为它不能应用表的索引。推荐用NOT EXISTS 或（外连接+判断为空）方案代替 　　在数据库中有两个表，一个是当前表Info(id,PName,remark,impdate,upstate)，一个是备份数据表bakInfo(id,PName,remark,impdate,upstate)，将当前表数据备份到备份表去，就涉及到not in 和in 操作了： 　　首先，添加10万条测试数据
代码如下: create procedure AddData as declare @id int set @id=0 while(@id&100000) begin insert (id,PName,remark,impdate,upstate) values(@id,convert(varchar,@id)+'0','abc',getdate(),0) set @id=@id+1 end exec AddData
使用not in 和in操作：
代码如下: SET STATISTICS TIME ON GO --备份数据 insert into bakInfo(id,PName,remark,impdate,upstate) select id,PName,remark,impdate,upstate
where id not in(select id from dbo.bakInfo) GO SET STATISTICS TIME OFF
此操作执行时间：
代码如下: SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 3 毫秒。 SQL Server 执行时间: CPU 时间 = 453 毫秒，占用时间 = 43045 毫秒。 (100000 行受影响) SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。 --更改当前表状态 update Info set upstate=1 where id in(select id from dbo.bakInfo)
　　此操作执行时间：
代码如下: SQL Server 分析和编译时间: CPU 时间 = 62 毫秒，占用时间 = 79 毫秒。 SQL Server 执行时间: CPU 时间 = 188 毫秒，占用时间 = 318 毫秒。 (100000 行受影响) SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。 --删除当前表数据 delete from Info where upstate=1 and id in(select id from dbo.bakInfo)
　　此操作执行时间：
代码如下: SQL Server 分析和编译时间: CPU 时间 = 183 毫秒，占用时间 = 183 毫秒。 SQL Server 执行时间: CPU 时间 = 187 毫秒，占用时间 = 1506 毫秒。 (100000 行受影响) SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
　　使用join连接替代方案：
代码如下: SET STATISTICS TIME ON GO --备份数据 insert into bakInfo(id,PName,remark,impdate,upstate) select id,PName,remark,impdate,upstate from (SELECT Info.id,Info.PName, Info.remark, Info.impdate,Info.upstate, bakInfo.id AS bakID FROM Info left JOIN bakInfo ON Info.id = bakInfo.id ) as t where t.bakID is null and t.upstate=0 GO SET STATISTICS TIME OFF;
　　此操作执行时间：
代码如下: SQL Server 分析和编译时间: CPU 时间 = 247 毫秒，占用时间 = 247 毫秒。 SQL Server 执行时间: CPU 时间 = 406 毫秒，占用时间 = 475 毫秒。 (100000 行受影响) SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。 --更改当前表状态 update Info set upstate=1 FROM Info INNER JOIN bakInfo ON Info.id = bakInfo.id
　　此操作执行时间：
代码如下: SQL Server 分析和编译时间: CPU 时间 = 4 毫秒，占用时间 = 4 毫秒。 SQL Server 执行时间: CPU 时间 = 219 毫秒，占用时间 = 259 毫秒。 (100000 行受影响) SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
--删除当前表数据
代码如下: delete from Info FROM Info INNER JOIN bakInfo ON Info.id = bakInfo.id where Info.upstate=1
　　此操作执行时间：
代码如下: SQL Server 分析和编译时间: CPU 时间 = 177 毫秒，占用时间 = 177 毫秒。 SQL Server 执行时间: CPU 时间 = 219 毫秒，占用时间 = 550 毫秒。 (100000 行受影响) SQL Server 分析和编译时间: CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
　　可以看出使用join方案比使用not in 和in执行时间要短很多了
您可能感兴趣的文章:
大家感兴趣的内容
12345678910
最近更新的内容
常用在线小工具2008年 总版技术专家分年内排行榜第二
2010年 总版技术专家分年内排行榜第四2009年 总版技术专家分年内排行榜第五2007年 总版技术专家分年内排行榜第六
2008年 总版技术专家分年内排行榜第二
2010年 总版技术专家分年内排行榜第四2009年 总版技术专家分年内排行榜第五2007年 总版技术专家分年内排行榜第六
本帖子已过去太久远了，不再提供回复功能。当in子查询的结果集中包含null时，如： in('a','b',null)，父查询只返回非NULL结果集，因为null和null无法比较;
当子查询 not in的结果集中包含null时，父查询永远为空结果集;
当子查询中查询字段不存在时,SQL不会报错
select& * from t1 where id in (select kk from t2) --t2中不存在kk字段,该语句不会报错,结果集为t1中所有行
如果是delete from t1 where& id in (select kk from t2),呵呵,那就哭吧~~~
示例脚本：
CREATE TABLE T1
&& & id & INT,
&& & name VARCHAR(10)
CREATE TABLE T2
&& & id & & &INT,
&& & address VARCHAR(10)
INSERT INTO T1
SELECT 1,&'Tom'
SELECT 2,&&'Jack'
SELECT 3,&&'Lily'
SELECT 4,&&'Tony'
INSERT INTO T2
SELECT 1,&'Beijing'
SELECT 2,&'Shanghai'
SELECT NULL,'Tianjin'
SELECT * FROM & T1
WHERE &id IN (SELECT id FROM T2)
*********************************************************************************
SELECT * FROM & T1
WHERE &id NOT IN (SELECT id FROM T2)
*********************************************************************************
SELECT * FROM & T1
WHERE &id NOT IN (SELECT id FROM T2 WHERE &id IS NOT NULL)
*********************************************************************************
SELECT * FROM & T1
WHERE &name IN (SELECT name FROM T2)
*********************************************************************************
DELETE FROM T1&
WHERE name IN (SELECT name FROM T2)&
(4 行受影响)
本文已收录于以下专栏：
相关文章推荐
SQL SERVER 不支持多字段的IN 和 NOT IN
但是ORACLE是支持的。
今天突然想到之前在书上看到的一个例子,竟然想不起来了.
于是翻书找出来,测试一下.
-- drop table father,son
create table father(fid int,name...
In这个指令在sql中也是时常可见的,我们这里还是以实例为主进行讲解，它的语法格式：
SELECT "栏位名"
FROM "表格名"
WHERE "栏位名" IN('值一', '值二',
初始化两张表：CREATE TABLE tb1(ID int) INSERT tb1
SELECT NULLUNION
SELECT NULLUNION ...
今天写sql语句的时候，希望通过not in子查询来限制查询结果，实际SQL语句如下： select
ID as id, TYPE_CODE as typeCode , TYPE_NAME as t...
在SQL server 2008中，In的用法也是相当重要的。它查询的是若干个孤立的值。下面我以几个例子说明一下。
--查询工资在和5000的三种的工资
IN:确定给定的值是否与子查询或列表中的值相匹配。
IN 关键字使您得以选择与列表中的任意一个值匹配的行。
SELECT ProductID, ProductName FROM Northwind...
给定A, B两个整数，不使用除法和取模运算，求A/B的商和余数。
最基本的算法是，从小到大遍历：
for (i = 2 to A -1)
if (i * B > A)...
K-means聚类算法是一种非层次聚类算法，在最小误差的基础上将数据划分了特定的类，类间利用距离作为相似度指标，两个向量之间的距离越小，其相似度就越高。程序读取全国省市经纬度坐标，然后根据经纬度坐标进...
本人最近在研究Radon变换，在查阅了各种资料之后在此写下个人的理解，希望与各位牛牛进行交流共同进步，也使得理解更加深刻些。
Radon变换的本质是将原来的函数做了一个空间转换，即，将原来的XY平...
他的最新文章
讲师：李江龙
讲师：司徒正美
您举报文章：
举报原因：
原文地址：
原因补充：
(最多只允许输入30个字)Posts - 789,
Articles - 0,
Comments - 1506
17:07 by 潇湘隐者, ... 阅读,
这个月碰到几个人问我关于“SQL SERVER中INNER JOIN 与 IN两种写法的性能孰优孰劣？”这个问题。其实这个概括起来就是SQL Server中INNER JOIN与子查询孰优孰劣（IN是子查询的实现方式之一，本篇还是只对比INNER JOIN与子查询IN的性能，如果展开INNER JOIN与子查询性能对比，范围太大了，没法一一详述）。下面这篇文章，我们就INNER JOIN与子查询IN这两种写法孰优孰劣，在不同场景下进行一下测试对比一下，希望能解答你心中的疑惑。
& 下面例子以AdventureWorks2014为测试场景，测试表为Sales.SalesOrderHeader与Sales.SalesOrderDetail。 如下所示：
&DBCC FREEPROCCACHE;GODBCC DROPCLEANBUFFERS;GO&SET&STATISTICS IO ON;SET&STATISTICS&TIME&ON;&SELECT& h.* FROM&Sales.SalesOrderHeader hWHERE SalesOrderID IN ( SELECT SalesOrderID FROM Sales.SalesOrderDetail)
DBCC FREEPROCCACHE;GODBCC DROPCLEANBUFFERS;GOSET&STATISTICS IO ON;SET&STATISTICS&TIME&ON;&SELECT h.* FROM Sales.SalesOrderHeader hINNER&JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID
如下所示，两种写法的SQL的实际执行计划是几乎一致。而且对比IO开销也是一致。cpu time 与elapsed time 有所差别，这个是因为两者返回的数据有所差别的缘故（SQL 1 返回 31465行数据， SQL 2返回 121317行数据），两者在逻辑上实际上是不一致的。因为重复数据的缘故。撇开这个不谈，光从性能上来考察两种，它们几乎是一模一样。没有优劣之分。
如果有人对上面的重复数据不明白的话，下面做个简单的例子演示给大家看看。如下所示，截图中INNER JOIN就会有重复数据。
CREATE&TABLE P(&&& PID&&& INT ,&&& Pname& VARCHAR(24))&INSERT INTO dbo.PSELECT 1, 'P1'&UNION&ALLSELECT 2, 'P2'&UNION&ALLSELECT 3, 'P3'&&CREATE&TABLE dbo.C(&&& CID&&&&&& INT ,&&& PID&&&&&& INT ,&&& Cname& VARCHAR(24))&INSERT INTO dbo.cSELECT 1, 1, 'C1'&UNION&ALLSELECT 2, 1, 'C2'&UNION&ALLSELECT 3, 2, 'C3'&UNION&ALLSELECT 3, 3, 'C4'
& 其实下面SQL在逻辑上才是相等的，它们的实际执行计划与IO是一样的。没有优劣之分。
SELECT& h.* FROM&Sales.SalesOrderHeader hWHERE SalesOrderID IN ( SELECT SalesOrderID FROM Sales.SalesOrderDetail);&&SELECT&DISTINCT h.* FROM Sales.SalesOrderHeader hINNER&JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID;
& 那么我们再来看另外一个例子，测试一下两者的性能差别。如下所示
SET&STATISTICS IO ON;SET&STATISTICS&TIME&ON;&SELECT& C.*FROM&&& Sales.Customer C&&&&&&& INNER&JOIN Person.Person P ON C.PersonID = P.BusinessEntityID;&&SELECT& C.*FROM&&& Sales.Customer CWHERE& C.PersonID IN ( SELECT Person.Person.BusinessEntityID&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& FROM&& Person.Person );
INNER JOIN与子查询IN的实际执行计划对比的百分比为66% VS 34% ， 子查询IN的性能还比 INNER JOIN的性能要好一些. IO几乎无差别，cpu time 与elapsed time的对比情况来看，子查询IN的性能确实要好一些。
& 这个是因为子查询IN在这个上下文环境中，它使用右半连接（Right Semi Join）方式的Hash Match，即一个表中返回的行与另一个表中数据行进行不完全联接查询（查找到匹配的数据行就返回，不再继续查找）。那么可以肯定的是，在这个场景（上下文）中，子查询IN这种方式的SQL的性能比INNER JOIN 这种写法的SQL要好。
& & 那么我们再来看一个INNER JOIN性能比子查询（IN)要好的案例。如下所示，我们先构造测试数据。
CREATE&TABLE P(&&& P_ID&&& INT&IDENTITY(1,1),&&& OTHERCOL&&&&&&& CHAR(500),&&& CONSTRAINT PK_P PRIMARY&KEY(P_ID))GO&BEGIN&TRANDECLARE @I INT = 1WHILE @I&=10000BEGIN&&& INSERT INTO P VALUES (NEWID())&&& SET @I = @I+1&&& IF (@I%500)=0&&& BEGIN&&&&&&& IF @@TRANCOUNT&0&&&&&&& BEGIN&&&&&&&&&&& COMMIT&&&&&&&&&&& BEGIN&TRAN&&&&&&& END&&& ENDENDIF @@TRANCOUNT&0BEGIN&&& COMMITENDGO&&CREATE&TABLE C (&&& C_ID& INT&IDENTITY(1,1) ,&&& P_ID&& INT& FOREIGN&KEY&REFERENCES P(P_ID),&&& COLN& CHAR(500),&&& CONSTRAINT PK_C& PRIMARY&KEY (C_ID) )&&&&SET NOCOUNT ON;&DECLARE @I INT = 1WHILE @I&=1000000BEGIN&&& INSERT INTO C VALUES ( CAST(RAND()*10 AS&INT)+1,& NEWID())&&& SET @I = @I+1ENDGO
& 构造完测试数据后，我们对比下两者的性能差异
SET&STATISTICS IO ON;SET&STATISTICS&TIME&ON;&SELECT C.* FROM dbo.C CINNER&JOIN dbo.P& P ON C.P_ID = P.P_IDWHERE P.P_ID=8&&SELECT * FROM dbo.CWHERE P_ID IN (SELECT P_ID FROM dbo.P WHERE P_ID=8)
& 增加对应的索引后，这个性能差距更更明显。 如下截图所示
&USE [AdventureWorks2014]GOCREATE&NONCLUSTERED&INDEX [IX_C_N1]ON [dbo].[C] ([P_ID])INCLUDE ([C_ID],[COLN])GO
& 在生产环境遇到一个案例， 两个视图使用INNER JOIN 与 IN 两种写法，在性能上差距很大。 使用子查询IN的性能比使用INNER JOIN的性能要好很多。如下截图所示。因为视图里面涉及多表。这样肯定导致执行计划非常复杂，导致SQL用INNER JOIN 的写法在性能上没有用子查询IN的写法要快
& 其实一部分情况下，INNER JOIN 与 子查询IN都是等价的。因为SQL Server优化器已经足够聪明，能够进行一些内部转换，生成等价的计划。但是在某一些特殊场景下，各有优劣。不能武断的就说INNER JOIN在性能上要比子查询IN要好。一定要结合上下文环境具体来谈性能优劣。否则没有多大意义。另外，子查询可以分为相关子查询和无关子查询，对于无关子查询来说，Not In子句比较常见，但Not In潜在会带来两种问题，结果不正确和性能问题，具体可以参考