1

When you have a query where you're joining from a non-null foreign key to the foreign table it doesn't matter if you use LEFT JOIN or INNER JOIN from a result standpoint (since there won't be any cases where it doesn't match something on the right). In a case like this which should be preferred, INNER JOIN or LEFT JOIN. I'd like to consider performance as well as style/documentation.

To me the INNER JOIN is closer to what you want, since the special characteristic of the LEFT JOIN doesn't happen, but in a lot of places I see LEFT JOIN just being the default unless INNER JOIN is explicitly needed so I'm partially worried that it may cause people to double think something that doesn't need thinking.

e.g. which of the following should be preferred

FROM dbo.Post p
LEFT JOIN dbo.User u ON p.UserID = u.UserID

or

FROM dbo.Post p
INNER JOIN dbo.User u ON p.UserID = u.UserID

assuming that UserID is a foreign key to the user table and no post can be created without a user

  • JOIN condition is not only about augmenting related rows, there are many cases in which you will need to filter your data in the join condition – Abdo Adel May 25 '15 at 16:37
  • Yeah but assuming the simple case of like dbo.Post p LEFT JOIN dbo.User u ON p.UserID = u.UserID – mirhagk May 25 '15 at 16:43
  • 7
    All other things being equal, always use the option that states your intent most clearly. – Robert Harvey May 25 '15 at 17:00
  • If you really can't make up your mind, pretend there are null values in all tables so that the difference does matter, and pick whichever join would probably be correct in that hypothetical alternate universe (which may someday become reality). – Ixrec May 25 '15 at 17:29
3

I've had this exact situation come up with a co-worker and when reviewing code from contractors -- the code should always convey your intent and expectations as clearly as possible given the constraints of performance and size.

Performance with SQL relies upon the query optimizer -- while it probably won't make any difference, if it does, more likely than not, using a left join will HURT performance not enhance; you are in effect lying to the optimizer.

I have yet to encounter a convincing argument for misleading code other than "it was necessary in order to make it work".

In short, never use a left join where you mean inner join.

Not part of your question but from the same experince -- never use top 1 "just in case more than 1 row is returned" for a sub-query. At best you hide your expectations, at worst you return incorrect results semi-randomly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.