INNER JOIN vs LEFT JOIN performance in SQL Server

Question

I've created SQL command that uses INNER JOIN on 9 tables, anyway this command takes a very long time (more than five minutes). So my folk suggested me to change INNER JOIN to LEFT JOIN because the performance of LEFT JOIN is better, despite what I know. After I changed it, the speed of query got significantly improved.

I would like to know why LEFT JOIN is faster than INNER JOIN?

My SQL command look like below: SELECT * FROM A INNER JOIN B ON ... INNER JOIN C ON ... INNER JOIN D and so on

Update: This is brief of my schema.

FROM sidisaleshdrmly a -- NOT HAVE PK AND FK     INNER JOIN sidisalesdetmly b -- THIS TABLE ALSO HAVE NO PK AND FK         ON a.CompanyCd = b.CompanyCd             AND a.SPRNo = b.SPRNo             AND a.SuffixNo = b.SuffixNo             AND a.dnno = b.dnno     INNER JOIN exFSlipDet h -- PK = CompanyCd, FSlipNo, FSlipSuffix, FSlipLine         ON a.CompanyCd = h.CompanyCd            AND a.sprno = h.AcctSPRNo     INNER JOIN exFSlipHdr c -- PK = CompanyCd, FSlipNo, FSlipSuffix         ON c.CompanyCd = h.CompanyCd            AND c.FSlipNo = h.FSlipNo             AND c.FSlipSuffix = h.FSlipSuffix      INNER JOIN coMappingExpParty d -- NO PK AND FK         ON c.CompanyCd = d.CompanyCd            AND c.CountryCd = d.CountryCd      INNER JOIN coProduct e -- PK = CompanyCd, ProductSalesCd         ON b.CompanyCd = e.CompanyCd            AND b.ProductSalesCd = e.ProductSalesCd      LEFT JOIN coUOM i -- PK = UOMId         ON h.UOMId = i.UOMId      INNER JOIN coProductOldInformation j -- PK = CompanyCd, BFStatus, SpecCd         ON a.CompanyCd = j.CompanyCd             AND b.BFStatus = j.BFStatus             AND b.ProductSalesCd = j.ProductSalesCd     INNER JOIN coProductGroup1 g1 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup1Cd         ON e.ProductGroup1Cd  = g1.ProductGroup1Cd     INNER JOIN coProductGroup2 g2 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup2Cd         ON e.ProductGroup1Cd  = g2.ProductGroup1Cd 

Answer 1

A LEFT JOIN is absolutely not faster than an INNER JOIN. In fact, it's slower; by definition, an outer join (LEFT JOIN or RIGHT JOIN) has to do all the work of an INNER JOIN plus the extra work of null-extending the results. It would also be expected to return more rows, further increasing the total execution time simply due to the larger size of the result set.

(And even if a LEFT JOIN were faster in specific situations due to some difficult-to-imagine confluence of factors, it is not functionally equivalent to an INNER JOIN, so you cannot simply go replacing all instances of one with the other!)

Most likely your performance problems lie elsewhere, such as not having a candidate key or foreign key indexed properly. 9 tables is quite a lot to be joining so the slowdown could literally be almost anywhere. If you post your schema, we might be able to provide more details.

---

Edit:

Reflecting further on this, I could think of one circumstance under which a LEFT JOIN might be faster than an INNER JOIN, and that is when:

• Some of the tables are very small (say, under 10 rows);
• The tables do not have sufficient indexes to cover the query.

Consider this example:

CREATE TABLE #Test1 (     ID int NOT NULL PRIMARY KEY,     Name varchar(50) NOT NULL ) INSERT #Test1 (ID, Name) VALUES (1, 'One') INSERT #Test1 (ID, Name) VALUES (2, 'Two') INSERT #Test1 (ID, Name) VALUES (3, 'Three') INSERT #Test1 (ID, Name) VALUES (4, 'Four') INSERT #Test1 (ID, Name) VALUES (5, 'Five')  CREATE TABLE #Test2 (     ID int NOT NULL PRIMARY KEY,     Name varchar(50) NOT NULL ) INSERT #Test2 (ID, Name) VALUES (1, 'One') INSERT #Test2 (ID, Name) VALUES (2, 'Two') INSERT #Test2 (ID, Name) VALUES (3, 'Three') INSERT #Test2 (ID, Name) VALUES (4, 'Four') INSERT #Test2 (ID, Name) VALUES (5, 'Five')  SELECT * FROM #Test1 t1 INNER JOIN #Test2 t2 ON t2.Name = t1.Name  SELECT * FROM #Test1 t1 LEFT JOIN #Test2 t2 ON t2.Name = t1.Name  DROP TABLE #Test1 DROP TABLE #Test2 

If you run this and view the execution plan, you'll see that the INNER JOIN query does indeed cost more than the LEFT JOIN, because it satisfies the two criteria above. It's because SQL Server wants to do a hash match for the INNER JOIN, but does nested loops for the LEFT JOIN; the former is normally much faster, but since the number of rows is so tiny and there's no index to use, the hashing operation turns out to be the most expensive part of the query.

You can see the same effect by writing a program in your favourite programming language to perform a large number of lookups on a list with 5 elements, vs. a hash table with 5 elements. Because of the size, the hash table version is actually slower. But increase it to 50 elements, or 5000 elements, and the list version slows to a crawl, because it's O(N) vs. O(1) for the hashtable.

But change this query to be on the ID column instead of Name and you'll see a very different story. In that case, it does nested loops for both queries, but the INNER JOIN version is able to replace one of the clustered index scans with a seek - meaning that this will literally be an order of magnitude faster with a large number of rows.

So the conclusion is more or less what I mentioned several paragraphs above; this is almost certainly an indexing or index coverage problem, possibly combined with one or more very small tables. Those are the only circumstances under which SQL Server might sometimes choose a worse execution plan for an INNER JOIN than a LEFT JOIN.