在T-SQL中，多个INNER JOIN的最佳设计是什么？

I have looked for a similar issue to no avail. If I have missed the solution, my apologies and please point me in the right direction.

我找了一个类似的问题无济于事。如果我错过了解决方案,我的道歉并请指出正确的方向。

I have be playing with MS AdventureWorks and would like to know if there is any functional difference between two SELECT statements using INNER JOIN that I have created. They give the same results.

我正在使用MS AdventureWorks,并且想知道使用我创建的INNER JOIN的两个SELECT语句之间是否存在任何功能差异。他们给出了相同的结果。

Version 1

SELECT  pp.BusinessEntityID, pp.FirstName, pp.MiddleName, pp.LastName, pp.Suffix, 
    pe.EmailAddress, 
    pa.AddressLine1, pa.AddressLine2, pa.City, pa.PostalCode FROM 
Person.BusinessEntityAddress AS bea 
INNER JOIN  Person.Address AS pa 
    ON bea.AddressID = pa.AddressID 
INNER JOIN  Person.Person AS pp 
    ON bea.BusinessEntityID = pp.BusinessEntityID   
INNER JOIN  Person.EmailAddress AS pe 
    ON pp.BusinessEntityID = pe.BusinessEntityID 
WHERE pp.Suffix IS NOT NULL

Version 2

SELECT      pp.BusinessEntityID, pp.FirstName, pp.MiddleName, pp.LastName, pp.Suffix,
        pe.EmailAddress,
        pa.AddressLine1, pa.AddressLine2, pa.City, pa.PostalCode 
FROM        Person.BusinessEntityAddress AS bea 
        INNER JOIN Person.Address AS pa 
            ON bea.AddressID = pa.AddressID 
        INNER JOIN Person.Person AS pp 
            ON bea.BusinessEntityID = pp.BusinessEntityID
        INNER JOIN Person.EmailAddress AS pe 
            ON bea.BusinessEntityID = pe.BusinessEntityID 
WHERE     pp.Suffix IS NOT NULL

The only difference is in the last ON clause. The design diagrams look quite different though Any thoughts would be very welcome

唯一的区别在于最后一个ON子句。设计图看起来很不一样,但任何想法都会非常受欢迎

1 个解决方案

#1

No difference here, because equality is transitive. (*)

这里没有区别,因为平等是可传递的。 (*)

However, if you had OUTER JOINS mixed in somewhere, the result may well be different.

但是,如果你在某个地方混合使用OUTER JOINS,结果可能会有所不同。

(*) I'd take a look at the execution plan for both, though, because the query planner may not be clever enough to choose the best option and by phrasing it differently, may be coerced one way or the other. You could also try throwing in the redundant third equality. While they all arrive at the same result, the order of how joins are performed can lead to drastically different execution times.

(*)我会看一下两者的执行计划,因为查询计划器可能不够聪明,无法选择最佳选项,并且通过不同的方式表达,可能会以某种方式强制执行。你也可以试着抛出多余的第三个平等。虽然它们都得出相同的结果,但是如何执行连接的顺序可能会导致执行时间大不相同。

#1