StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>A <code>LEFT JOIN</code> is absolutely not faster than an <code>INNER JOIN</code>. In fact, it's slower; by definition, an outer join (<code>LEFT JOIN</code> or <code>RIGHT JOIN</code>) has to do all the work of an <code>INNER JOIN</code> 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.</p> <p>(And even if a <code>LEFT JOIN</code> <em>were</em> faster in <em>specific</em> situations due to some difficult-to-imagine confluence of factors, it is not functionally equivalent to an <code>INNER JOIN</code>, so you cannot simply go replacing all instances of one with the other!)</p> <p>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.</p> <hr> <p><strong>Edit:</strong></p> <p>Reflecting further on this, I could think of one circumstance under which a <code>LEFT JOIN</code> might be faster than an <code>INNER JOIN</code>, and that is when:</p> <ul> <li>Some of the tables are <em>very</em> small (say, under 10 rows);</li> <li>The tables do not have sufficient indexes to cover the query.</li> </ul> <p>Consider this example:</p> <pre><code>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 </code></pre> <p>If you run this and view the execution plan, you'll see that the <code>INNER JOIN</code> query does indeed cost more than the <code>LEFT JOIN</code>, because it satisfies the two criteria above. It's because SQL Server wants to do a hash match for the <code>INNER JOIN</code>, but does nested loops for the <code>LEFT JOIN</code>; the former is <em>normally</em> much faster, but since the number of rows is so tiny <em>and</em> there's no index to use, the hashing operation turns out to be the most expensive part of the query.</p> <p>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.</p> <p>But change this query to be on the <code>ID</code> column instead of <code>Name</code> and you'll see a very different story. In that case, it does nested loops for both queries, but the <code>INNER JOIN</code> version is able to replace one of the clustered index scans with a seek - meaning that this will literally be <em>an order of magnitude</em> faster with a large number of rows.</p> <p>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 <em>might</em> sometimes choose a worse execution plan for an <code>INNER JOIN</code> than a <code>LEFT JOIN</code>.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload