Are RIGHT JOINs ever necessary?

RIGHT JOINs can always be rewritten as LEFT JOINs, and some people consider RIGHT JOINs a bad practice. This article explains why.

Simple case: two tables

It goes without saying that a RIGHT JOIN between two tables can always be written as a LEFT JOIN. All we have to do is to invert the order of the tables:

author a RIGHT JOIN book b ON a.book_id = b.id

is the same as:

book b LEFT JOIN author a ON a.book_id = b.id

Complex case: multiple tables

A common objection is that JOINs are often more complicated and involve multiple tables, creating situations when LEFT and INNER JOINs are simply not enough.

The counter-intuitive truth is that, actually, JOINs are always between two tables. In this respect, it’s not different than the classical arithmetical operators we study at school. Consider this:

1 + 2 + 3

+ is a binary operator: it always involves two numbers. Assuming that we follow the left-to-right order, we’re summing 1 + 2, and then 3 + 3.

JOIN is a binary operator, too. So, consider this:

a LEFT JOIN b RIGHT JOIN c

is the same as:

c LEFT JOIN a LEFT JOIN b

or:

a LEFT JOIN c LEFT JOIN b

The actual join order is determined by the query planner, as long as it returns the results we asked for.

Bad practices

Many consider RIGHT JOINs as a bad practice. The reason is simple: following a series of LEFT and RIGHT is confusing for the human mind.

But why is the recommendation to use LEFT JOINs instead of RIGHT JOINs? Because SQL, like English, is written from left to right.