左联接和内联接之间的性能差异

问题描述

在性能方面，左联接和内联接之间是否有区别?我使用的是SQL Server 2012.

Is there any difference between left join and inner join regarding performance? I use SQL Server 2012.

推荐答案

在至少一种情况下，LEFT [OUTER] JOIN是比[INNER] JOIN更好的选择.我说的是使用OUTER而不是INNER来获得相同的结果.

There is at least one case where LEFT [OUTER] JOIN is a better option than [INNER] JOIN. I talk about getting the same results using OUTER instead of INNER.

示例(我正在使用 AdventureWorks 2008数据库):

-- Some metadata infos
SELECT  fk.is_not_trusted,  fk.name
FROM    sys.foreign_keys fk
WHERE   fk.parent_object_id=object_id('Sales.SalesOrderDetail');
GO

CREATE VIEW View1
AS
SELECT  h.OrderDate, d.SalesOrderDetailID, o.ModifiedDate
FROM    Sales.SalesOrderDetail d
INNER JOIN Sales.SalesOrderHeader h ON d.SalesOrderID = h.SalesOrderID
INNER JOIN Sales.SpecialOfferProduct o ON d.SpecialOfferID=o.SpecialOfferID AND d.ProductID=o.ProductID;
GO

CREATE VIEW View2
AS
SELECT  h.OrderDate, d.SalesOrderDetailID, o.ModifiedDate
FROM    Sales.SalesOrderDetail d
INNER JOIN Sales.SalesOrderHeader h ON d.SalesOrderID = h.SalesOrderID
LEFT JOIN Sales.SpecialOfferProduct o ON d.SpecialOfferID=o.SpecialOfferID AND d.ProductID=o.ProductID;
GO

SELECT  SalesOrderDetailID
FROM    View1;

SELECT  SalesOrderDetailID
FROM    View2;

第一个查询的结果:

is_not_trusted name
-------------- ---------------------------------------------------------------
0              FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID
0              FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID

最后两个查询的执行计划:

Execution plans for the last two queries:

注释1/视图1:如果我们查看SELECT SalesOrderDetailID FROM View1的执行计划我们看到了 FK消除 ，因为FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID约束是可信任的，并且它只有一列.但是，由于SELECT/WHERE子句不包含该表中的任何列，并且强制FK约束(FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID)，服务器也被迫(由于INNER JOIN Sales.SpecialOfferProduct)从第三张表(SpecialOfferProduct)中读取数据. .发生这种情况是因为最后一个FK是多列.

Note 1 / View 1: If we look at the execution plan for SELECT SalesOrderDetailID FROM View1we see a FK elimination because the FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID constraint is trusted and it has a single column. But, the server is forced (because of INNER JOIN Sales.SpecialOfferProduct) to read data from the third table (SpecialOfferProduct) even the SELECT/WHERE clauses doesn't contain any columns from this table and the FK constraint (FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID) is (also) trusted. This happens because this last FK is multicolumn.

注释2/视图2:如果要删除Sales.SpecialOfferProduct上的读数(Scan/Seek)，该怎么办?第二个FK是多列，在这种情况下，SQL Server无法消除FK(请参阅先前的Conor Cunnigham博客文章).在这种情况下，我们需要将INNER JOIN Sales.SpecialOfferProduct替换为LEFT OUTER JOIN Sales.SpecialOfferProduct才能消除FK. SpecialOfferID和ProductID列均为NOT NULL，我们有一个受信任的FK引用SpecialOfferProduct表.

Note 2 / View 2: What if we want to remove the read (Scan/Seek) on the Sales.SpecialOfferProduct? This second FK is multicolumn and for such cases the SQL Server cannot eliminates the FK (see previous Conor Cunnigham blog post). In this case we need to replace the INNER JOIN Sales.SpecialOfferProduct with LEFT OUTER JOIN Sales.SpecialOfferProduct in order to get FK elimination. Both SpecialOfferID and ProductID columns are NOT NULL and we a have a trusted FK referencing SpecialOfferProduct table.

这篇关于左联接和内联接之间的性能差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！