Skip to main content

You are not logged in. Your edit will be placed in a queue until it is peer reviewed.

We welcome edits that make the post easier to understand and more valuable for readers. Because community members review edits, please try to make the post substantially better than how you found it, for example, by fixing grammar or adding additional resources and hyperlinks.

Required fields*

8
  • 7
    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. Commented Jun 6, 2012 at 17:44
  • 1
    EDIT - Clarified the conditions that must exist for the optimizer to drop the outer joined table from the execution plan. Commented Dec 4, 2012 at 20:43
  • 2
    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. Commented Sep 18, 2014 at 5:31
  • 3
    Although this shows a seemingly trivial example indeed, this is an extraordinarily insightful answer! Commented May 5, 2015 at 8:30
  • 11
    +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. Commented Sep 13, 2016 at 3:16