Is there any difference between left join and inner join regarding performance? I use SQL Server 2012.
- 5Yes, there might be difference. But why do you care? These 2 are not equivalent and will (usually) produce different results. So, use the one that is useful for your query and gives you the results you want.ypercubeᵀᴹ– ypercubeᵀᴹ2013-02-24 09:36:28 +00:00Commented Feb 24, 2013 at 9:36
- possible duplicate of INNER JOIN vs LEFT JOIN performance in SQL Serverypercubeᵀᴹ– ypercubeᵀᴹ2013-02-24 09:45:58 +00:00Commented Feb 24, 2013 at 9:45
Add a comment |
2 Answers
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.
6 Comments
Bogdan Sahlean
@MartinSmith: I'm adding some notes now but in this case they have the same semantics.
ypercubeᵀᴹ
They have the same semantics only if there is a
FOREIGN KEY from SalesOrderDetail referencing SpecialOfferProduct.ypercubeᵀᴹ
Nice. There is a Connect page where one can vote for adding the feature: Support join elimination for multi-column foreign key constraints
ypercubeᵀᴹ
And another related blog post: When row estimation goes wrong
dbenham
@ypercubeᵀᴹ - They have the same semantics "only if there is a FOREIGN KEY from SalesOrderDetail referencing SpecialOfferProduct", and the FOREIGN KEY is NOT NULL
|
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.
