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 theOrders
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:
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
select
ed 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