17

In the MySQL documentation for joins, a coworker pointed out this gem to me today:

RIGHT JOIN works analogously to LEFT JOIN. To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN.

Is anyone able to shed some light on this? This strikes me as probably a remnant of a past age - as in maybe the documentation means to say "To keep code reverse compatible with earlier versions of MySQL..."

Is there a modern RDBMS that doesn't support RIGHT JOIN? I get that RIGHT JOIN is syntactic sugar over LEFT JOIN, and any RIGHT JOIN can be expressed as a LEFT JOIN, but there are times when readability suffers if you write a query in that direction.

Is this advice still modern and valid? Is there a compelling reason to avoid RIGHT JOIN?

MightyE
  • 2,679
  • 18
  • 18
  • I tend to avoid right joins just as I'm more used to thinking in terms of left joins but occasionally they are useful. Related [Does anyone use Right Outer Joins?](http://stackoverflow.com/questions/689963/does-anyone-use-right-outer-joins). – Martin Smith Jul 11 '12 at 18:33
  • I tend to use left joins as well - when building a query, you tend to think in one direction, and you start with the kernel of your data, then extend on that, making left join the more natural of the two. But I'm surprised to see MySQL documentation saying there's a portability issue at stake. – MightyE Jul 11 '12 at 18:40
  • 9
    I find it rather funny that the MySQL manual cares about portability in that case where they ignore it at so many other places. –  Jul 11 '12 at 19:51
  • 3
    Do people who use right-to-left languages tend to use right join more often? – Mathieu Longtin Jul 12 '12 at 02:05
  • 1
    Mathieu - that's a good question =) I'm not sure they do because it's not really LtoR vs RtoL that makes left join the more natural, but more that the tendency is to start with the the records you want to display all of, then join onto that with references, which is the direction of a left join. – MightyE Jul 12 '12 at 13:21

3 Answers3

27

There's at least one SQL engine that does not support RIGHT JOIN: SQLite. Maybe that's the reason why compatibility was listed as a concern. There may potentially be other SQL engines as well.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
Jacob
  • 77,566
  • 24
  • 149
  • 228
  • Useful to know =). Still, as a_horse_with_no_name observes, it's odd that MySQL would talk about portability on something like joins when they disregard it in so many other areas - I suspect the recommendation might be more based on compatibility with earlier MySQL versions. – MightyE Jul 12 '12 at 13:23
1

So, a RIGHT and LEFT JOIN perform the same action in typical SQL engines. LEFT JOIN table a to table b returns everything from a that exists in b or not. RIGHT JOIN table a to table b returns everything from b that exists in a or not. Prior to optimizing the query, LEFT and RIGHT keywords only refer to an action to be taken on which table. The MySQL optimizer will always normalize the query and make the JOIN effectively a LEFT JOIN. Thus, writing your query to use LEFT JOIN instead of RIGHT will cost less in the optimizer.

EMM
  • 11
  • 1
-1

It's a sensible convention so it should be preferred unless you want to express something distinct or out of the ordinary to anyone who might read it later, (importantly, including yourself).

Your question is valid, though to answer it completed would require either:

1) a counter-example where a RIGHT JOIN doesn't operate, or operates with significant differences

-or-

2) proof that no such case:1 exists

These will be hard to come by I think. Your suggestion that the advice may have been deprecated since it's writing is possible. It may also be due to some wrinkle of MySQL supporting multiple database back-ends. Perhaps one of them is RIGHT JOIN intolerant?

niklauzg
  • 571
  • 4
  • 6