Exists and IN

/*

查询来自'Spain'的客户，对每个匹配的客户，执行在Orders.custid列上的索引执行一次查找操作

检查Orders表是否包含具有该客户的custid的订单

子查询中筛选列custid上的索引在这里非常有帮助，因为通过它可以直接访问Orders表中具有特定custid值的行

*/

SET STATISTICS TIME ON

SELECT custid,companyname

FROM Sales.Customers AS C

WHERE country=N'Spain'

AND custid IN (SELECT custid FROM Sales.Orders)

/*

采用Exists

*/

SELECT *

FROM Sales.Customers AS C

WHERE EXISTS

(

SELECT * FROM Sales.Orders WHERE custid=c.custid

)

AND country='Spain'

/*

这两个查询哪个效率会更高呢

他们逻辑上是相等的2个查询,优化器会为他们生成相同的计划

也许是测试的表数据太少,好吧我们来建2张大表测试一下

*/

--学生表

IF OBJECT_ID('dbo.Sudents') IS NOT NULL

   DROP TABLE dbo.Sudents

CREATE TABLE dbo.Sudents

(

id INT IDENTITY(1,1) PRIMARY KEY,

NAME VARCHAR(16) NULL

)

--学生成绩表

IF OBJECT_ID('dbo.Score') IS NOT NULL

   DROP TABLE dbo.Score

CREATE TABLE dbo.Score

(

id INT IDENTITY(1,1) PRIMARY KEY,

StudentID INT NULL,

Score INT NULL

)

--测试数据

DECLARE @_n INT=1000000

DECLARE @_i INT=1

WHILE @_i<@_n

BEGIN

INSERT INTO dbo.Sudents

SELECT 'Student'+CAST(@_i AS VARCHAR(10))

INSERT INTO dbo.Score

SELECT @@IDENTITY,CAST(CEILING(RAND()*100) AS INT)

SET @_i+=1;

END

--100W条数据确实太多,执行到20+W的时候暂停

--现在删除学号为201的成绩

INSERT INTO dbo.Score

DELETE dbo.Score WHERE StudentID=201

--现在来查询没有成绩的学生

SELECT *

FROM dbo.Sudents AS S

WHERE NOT EXISTS

(

SELECT 1 FROM dbo.Score WHERE StudentID=S.ID

)

/*

SQL Server 执行时间:

   CPU 时间 = 264 毫秒，占用时间 = 105 毫秒。

*/

SELECT *

FROM dbo.Sudents AS S

WHERE id NOT IN

(

SELECT StudentID FROM dbo.Score

)

/*

SQL Server 执行时间:

   CPU 时间 = 297 毫秒，占用时间 = 115 毫秒。

*/

--现在看出Not Exists和Not in的区别了

--给Score表的StudentId加上非聚集索引

CREATE NONCLUSTERED INDEX idx_Score_StudentID ON dbo.Score(StudentID)

--执行时间

/*

  SQL Server 执行时间:

   CPU 时间 = 47 毫秒，占用时间 = 43 毫秒。

SQL Server 执行时间:

   CPU 时间 = 47 毫秒，占用时间 = 46 毫秒。

*/

--加上索引执行时间都缩短了,但两者还是相差不大,那好吧在多删几条记录

DELETE dbo.Score WHERE id IN (202,203,400,600,700,560,951,452)

/*

SQL Server 执行时间:

   CPU 时间 = 31 毫秒，占用时间 = 44 毫秒。

(9 行受影响)

SQL Server 执行时间:

   CPU 时间 = 47 毫秒，占用时间 = 46 毫秒。

*/

--好吧,not exists和not in 在时间效率上还是有区别的

--现在测试一下exists和in 的区别

--清空Score表

TRUNCATE TABLE Score

DECLARE @_m INT=300000

DECLARE @_j INT=210000

WHILE @_j<@_m

BEGIN

INSERT INTO dbo.Score

SELECT @_j,CAST(CEILING(RAND()*100) AS INT)

SELECT @_j+=1

END

--现在查询存在成绩的学生

INSERT INTO Score

SELECT 1000,CAST(CEILING(RAND()*100) AS INT) UNION ALL

SELECT 500,CAST(CEILING(RAND()*100) AS INT) UNION ALL

SELECT 100,CAST(CEILING(RAND()*100) AS INT)

--IN的用法

SET STATISTICS IO OFF

SELECT * FROM Sudents S

WHERE id IN (SELECT StudentID FROM Score )

--Exists的用法

SELECT * FROM Sudents S

WHERE EXISTS

(

SELECT 1 FROM score WHERE StudentId =S.ID

)

/*虽然2者的执行计划是一样的,但时间上效果还是挺明显的

执行时间

SQL Server 执行时间:

   CPU 时间 = 31 毫秒，占用时间 = 21 毫秒。

(3 行受影响)

SQL Server 执行时间:

   CPU 时间 = 16 毫秒，占用时间 = 18 毫秒。

*/

--可能是上面加了索引的原因

--有人会考虑到主表和从表的大小关系，好吧，就当把子查询的表为从表

--现在主表用20+W条记录,从表有9W条记录

--现在在给从表增加到100W条记录

DECLARE @_l INT=1000000

DECLARE @_k INT=300000

WHILE @_k<@_l

BEGIN

INSERT INTO Score

SELECT @_k,CAST(CEILING(RAND()*100) AS INT)

SET @_k+=1

END

--好了 在测试上面的语句

/*

SQL Server 执行时间:

   CPU 时间 = 16 毫秒，占用时间 = 21 毫秒。

(3 行受影响)

SQL Server 执行时间:

   CPU 时间 = 31 毫秒，占用时间 = 19 毫秒。

*/

--还是Exists的效率还是存在一定效率

/*

为什么IN和Exists会有相同的执行计划呢?

如果考虑一下三值逻辑可能就会意识到他们的区别了,与Exists不同,当输入列表中包含NUll时,In实际上会产生一个UNKOWN的逻辑结果,

例如: a IN (b,c,NULL)的结果是UNKOWN，不过在筛选器中UNKOWN和False处理方式类似，使用IN谓词的查询结果与使用Exists谓词一样，

而却优化器知道这一点所以生成了相同的执行计划，另外NOT IN中包含NULL时，NOT IN 查询总会返回空集。因为 val IN(a,b,c,NULL)永远不会返回False

而是返回TRUE、所以val NOT IN (val1,val2.....,NULL)返回的只有NOTtrue 和not Unkown，这两个结果都不会是true

*/