EXISTS,IN，连接查询在SQL2008R2中性能如何？

在查询中使用exists来进行判断性能会高，因为exists有短路的效果，整个表不必查完，通常比IN效果来的好，现在SQL2008R2中用代码测试一下，看看结果：

首先是使用IN来查询：

DBCC FREEPROCCACHE
CHECKPOINT
DBCC DROPCLEANBUFFERS

SELECT * FROM sales.salesorderheader AS soh 
WHERE contactid in 
        (
            SELECT contactid 
            FROM person.contact
            WHERE firstname='carla'
            AND lastname='adams'    
        )

逻辑读为：

(4 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 703, physical reads 19, read-ahead reads 699, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Contact'. Scan count 1, logical reads 366, physical reads 6, read-ahead reads 364, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

第二种是使用EXISTS写法：

DBCC FREEPROCCACHE
CHECKPOINT
DBCC DROPCLEANBUFFERS        

SELECT * FROM sales.salesorderheader AS soh 
WHERE EXISTS
        (
            SELECT c.contactid 
            FROM person.contact AS c
            WHERE firstname='carla'
            AND lastname='adams'
            AND c.contactid=soh.contactid    
        )

逻辑读为：

(4 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 703, physical reads 19, read-ahead reads 699, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Contact'. Scan count 1, logical reads 366, physical reads 6, read-ahead reads 364, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

第三种写法使用连接查询：

 

DBCC FREEPROCCACHE
CHECKPOINT
DBCC DROPCLEANBUFFERS        

SELECT soh.*
FROM sales.salesorderheader AS soh
JOIN person.contact AS c 
ON soh.contactid=c.contactid
AND c.firstname='carla'
AND lastname='adams'    

逻辑读为：

(4 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SalesOrderHeader'. Scan count 1, logical reads 703, physical reads 19, read-ahead reads 699, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Contact'. Scan count 1, logical reads 366, physical reads 6, read-ahead reads 364, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

最后来看看三种写法的执行计划：

可以看到执行计划一模一样，所以得出的结论如下：

1：IN,EXIST，连接查询性能孰高孰低，不能想当然，以上就是明证，以前脱口而出的答案，现在不管用了

2：我的感觉规则总有例外，尤其在SQL性能调优这块，以前最佳实践，现在只能通过不断的测试哪种写法最优了

 

转载于:https://www.cnblogs.com/fly_zj/archive/2012/08/11/2634004.html