Performance difference between left join and inner join

Question

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

Answer 1

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.

Example (I am using AdventureWorks 2008 database):

-- 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;

Results for the first query:

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

Execution plans for the last two queries:

Note 1 / View 1: If we look at the execution plan for SELECT SalesOrderDetailID FROM View1 we 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.

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.

Answer 2

As well as the issue that outer join may return a larger result set because of the additional rows preserved one other point is that the optimiser has a greater range of possibilities when creating an execution plan because INNER JOIN is commutative and associative.

So for the following example B is indexed but A isn't.

CREATE TABLE A(X INT, Filler CHAR(8000))

INSERT INTO A 
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY @@SPID), ''
FROM sys.all_columns

CREATE TABLE B(X INT PRIMARY KEY, Filler CHAR(8000))

INSERT INTO B
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY @@SPID), ''
FROM sys.all_columns

SELECT *
FROM B INNER JOIN A ON A.X = B.X

SELECT *
FROM B LEFT JOIN A ON A.X = B.X

The optimiser knows that B INNER JOIN A and A INNER JOIN B are the same and produces a plan with a nested loops seeking into table B.

This transformation is not valid for the outer join and nested loops only supports left outer join not right outer join so it needs to use a different join type.

But from a practical perspective you should just choose the join type you need that will give you the correct semantics.