Reason to prefer RIGHT JOIN over LEFT JOIN

database-designrelational-databasesql

If I understand correctly, every RIGHT JOIN:

SELECT Persons.*, Orders.*
FROM Orders
RIGHT JOIN Persons ON Orders.PersonID = Persons.ID

can be expressed as a LEFT JOIN:

SELECT Persons.*, Orders.*
FROM Persons
LEFT JOIN Orders ON Persons.ID = Orders.PersonID

My personal opinion is that the statement's intent:

  • First get the Persons
  • Then expand/repeat the Persons as necessary in order to match the Orders

is better expressed by the order of Persons LEFT JOIN Orders than by the reverse-ordered Orders RIGHT JOIN Persons (and I never use RIGHT JOIN as a result).

Are there any situations where a RIGHT JOIN is preferred? Or, are there any use cases where RIGHT JOIN can do something that LEFT JOIN cannot?

Best Answer

That depends on what requirement you are trying to fulfill.

It's not the same to say: "gimme all persons and their corresponding orders" that "I want all orders with their corresponding persons", particularly if you are going to use is null to bring rows with no corresponding match. That's what I call the "dominant table", which is the table I want to fetch rows from regardless of there not being a correponding row in the other side of the join.

Look at this images and you will notice they are not the same:

enter image description here

Source of image is this excellent article.

But you are right in that both requirements can be fulfilled with either join just inverting the order of the tables in the join.

But I guess that for western people acustomed to writing left to right it comes more naturally to use left joins over right joins, since we see as though we want the joins being in the same direction or in the same order as the selected columns.

So a possible reason to prefer a right join is because in your culture you write from right to left (like in the Arabic or Hebrew writing systems) and you tend to think that way, meaning perhaps in your brain textual info flows from right to left.

Some linguists think your language influences your way of thinking: https://www.edge.org/conversation/lera_boroditsky-how-does-our-language-shape-the-way-we-think

Related Topic