SQL Server 中Inner join 和where的效率差异

SQL Server 中Inner join 和where的效率差异

总结出来时说：对小数据量（<N万）的来说效率几乎无差异，更有说法说Inner join 和Where只是SQL标准不同，在查询分析器中SQL Server查询分析器是将Where直接转换为Join后查询的。

如是有了如下比较结果（均在查询分析器中查询和计时）：

语句（1）

declare @operatorName nvarchar(50)

set @operatorName = '%'

select distinct item.* from item , customer_item , customer_operator ,operator

where item.itemcode = customer_item.itemCode

and customer_item.customerCode = customer_operator.customerCode

and customer_operator.operatorId = customer_operator.operatorId

and operator.operatorName like @operatorName

and item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0

查询结果，74行，共时间0：00：04

语句（2）

declare @operatorName nvarchar(50)

set @operatorName = '%'

select distinct item.* from item inner join customer_item

on item.itemcode = customer_item.itemCode

inner join customer_operator on customer_item.customerCode = customer_operator.customerCode

inner join operator on customer_operator.operatorId = operator.operatorId

where operator.operatorName like @operatorName

and item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0

共74行，时间0：00：01

后检查发现语句（1）中有一个重复自查询条件 ：customer_operator.operatorId = customer_operator.operatorId

将其叶加到语句2中，语句（3）

declare @operatorName nvarchar(50)

set @operatorName = '%'

select distinct item.* from item inner join customer_item

on item.itemcode = customer_item.itemCode

inner join customer_operator on customer_item.customerCode = customer_operator.customerCode

inner join operator on customer_operator.operatorId = operator.operatorId

where operator.operatorName like @operatorName

and item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0

and customer_operator.operatorId = customer_operator.operatorId

所用时间和结果都为74行，时间0：00：01。

将语句（1）中的去掉该条件后成为语句（4）

declare @operatorName nvarchar(50)

set @operatorName = '%'

select distinct item.* from item , customer_item , customer_operator ,operator

where item.itemcode = customer_item.itemCode

and customer_item.customerCode = customer_operator.customerCode

--and customer_operator.operatorId = customer_operator.operatorId

and operator.operatorName like @operatorName

and item.deleted = 0 and customer_item.deleted = 0 and customer_operator.deleted = 0

时间和结果为74行，时间0：00：01。

终于发现了些他们的差异。

结论：

尽量使用Join 而不是Where来列出关联条件，特别是多个表联合的时候。

原因是：

（1）在效率上，Where可能具有和Inner join一样的效率。但基本可以肯定的（通过SQLServer帮助和其它资料，以及本测试）是Join的效率不比Where差。

（2）使用Join可以帮助检查语句中的无效或者误写的关联条件

转载于:https://www.cnblogs.com/jankerxp/p/7774031.html