Timeline for INNER JOIN vs LEFT JOIN performance in SQL Server
Current License: CC BY-SA 4.0
12 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jun 18, 2020 at 20:06 | history | edited | dbenham | CC BY-SA 4.0 |
added 26 characters in body
|
| Sep 13, 2016 at 3:16 | comment | added | phosplait | +1: I seem to have run into this on a few queries where I was using inner joins with some very large tables. The inner join was causing a spill into tempdb in the query plan (I assume for the reason stated above -- and my server lacking the RAM to hold everything in memory). Switching to left joins eliminated the spill to tempdb, result being that some of my 20-30 second queries now run in fractions of a second. This is a very important gotcha seeing as most people seem to make the blanket assumption that inner joins are faster. | |
| May 5, 2015 at 8:30 | comment | added | pbalaga | Although this shows a seemingly trivial example indeed, this is an extraordinarily insightful answer! | |
| Sep 19, 2014 at 5:45 | history | bounty awarded | Douglas | ||
| Sep 18, 2014 at 5:31 | comment | added | Douglas | One minor clarification to your answer: When the foreign key column is non-nullable, the INNER JOIN and the LEFT JOIN become semantically equivalent (i.e. your suggested WHERE clause is redundant); the only difference would be the execution plan. | |
| Sep 18, 2014 at 5:28 | comment | added | Douglas | This pitfall is elusive because it does not always manifest – the query optimizer can eliminate unnecessary inner joins for simple queries – and is missed by otherwise-respectable sources (see #9 in 13 Things You Should Know About Statistics and the Query Optimizer). However, when it hits, I've seen it slow down performance by orders of magnitude (100× and worse). | |
| Sep 18, 2014 at 5:24 | comment | added | Douglas | +1: This is the only online mention I could find of what I consider to be a grave pitfall of the SQL Server query optimizer. I frequently use "do-everything" views (as you call them) to compensate for SQL's serious shortcomings in abstraction (and, by consequence, reuse and maintainability) – I shouldn't need to update dozens of views, functions, and procedures each time I decide to normalize or restructure any of my core tables. | |
| Jan 11, 2013 at 0:02 | comment | added | Esteban Brenes | I also ran into a situation like this, while building a dynamic query. The central query had 20 different LEFT JOINS, but upon reviewing the execution plan it was pretty similar in performance to a hand-written query that only had the required 2 INNER JOINS. | |
| Dec 4, 2012 at 20:43 | comment | added | dbenham | EDIT - Clarified the conditions that must exist for the optimizer to drop the outer joined table from the execution plan. | |
| Dec 4, 2012 at 20:42 | history | edited | dbenham | CC BY-SA 3.0 |
Further clarified the conditions that must exist for the optimizer to drip the outer table from the execution plan
|
| Jun 6, 2012 at 17:44 | comment | added | Erik Philips | I actually ran into this problem when building out extremely dynamic queries. I had left in an INNER JOIN that I was using and not pulling data from, and when I switched it to a LEFT JOIN (out of shear curiosity) the query actually ran faster. | |
| Dec 14, 2011 at 20:12 | history | answered | dbenham | CC BY-SA 3.0 |