Reason to prefer RIGHT JOIN over LEFT JOIN

Question

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?

Tulains Córdova · Answer 1 · 2017-01-25T15:53:38.147

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 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

score 5 · Answer 2 · answered Nov 14 '16 at 15:37

There isn't anything (that I know of) that can be done with a right join that can't be done with a left join. But sometimes the syntax with left joins is uglier. Let's say you have the following tables:

Persons
ID | Name

Orders
ID | CustomerId | other unimportant stuff

SpecialOrderDetails
ID | OrderId | other stuff

Let's say you need to get a list of all the people in your database and any orders they have with special order details (we will say that not all orders have special order details). So you would normally do a left join from people to orders. But then you have to join in special order details. If you use an inner join there, it would effectively make the left join from people to orders into an inner join. IE: this is what you want to do but doesn't work (it will exclude anyone who doesn't have a special order):

select p.*, o.*, d.*
from Persons p
left join Orders o on o.CustomerId = p.Id
inner join SpecialOrderDetails d on d.OrderId = o.Id

So you could rewrite it as this:

--get all the people without a special order
select p.*, NULL, NULL, ... --NULLs placeholders for all the fields from OrderDetails and SpecialOrderDetails
from Persons p
left join Orders o on o.CustomerId = p.Id
left join SpecialOrderDetails d on d.OrderId = o.Id
where o.Id is null 

union

--get all the people with a special order
select p.*, o.*, d.*
from Persons p
inner join Orders o on o.CustomerId = p.Id
inner join SpecialOrderDetails d on d.OrderId = o.Id

Not exactly clear (assuming no comments), but it does the job. If this is something more than a one-off (ie. something someone is going to have to come back and maintain someday) using a right join might make it clearer what the intent was.

select p.*, o.*, d.*
from Orders o
inner join SpecialOrderDetails d on d.OrderId = o.Id
right join Persons p on p.Id = o.CustomerId

Which is a bit more succinct and clear (but only if whoever is reading it understands right joins). Note that this can be written with left joins, but it requires a nested join (which less people are probably familiar with than right joins).

select p.*, o.*, d.*
from Persons p
left join Orders o 
    inner join SpecialOrderDetails d on d.OrderId = o.Id
on o.CustomerId = p.Id

At this point, it is a choice of what is most clear and what most people will understand (would you know how to google that syntax if you didn't know it was called a nested join?).

In short, you don't strictly need right joins, but they might make it easier to read.

Jon Raynor · Answer 3 · 2016-11-14T20:21:45.290

I could see a RIGHT JOIN being used for replication/merge purposes. Let's say I have two tables A and B. A is on the left and B is on the right. Let's say I wanted to replicate data between these two table to make them equivalent.

If I wanted to show all the data that was in A but not in B it would be a LEFT join. If I wanted to show all the data in B that was not in A it would RIGHT join.

So, sometimes LEFT and RIGHT come in handy when merging and replicating data to keep things in prospective.

Other than that, I see no other reason to use a RIGHT join as all RIGHT joins can be converted to LEFT joins or vice versa all LEFT joins could be converted to RIGHT joins depending on how the tables are ordered or visualized. So, it would be a matter of preference in other cases.

Here's a nice link to visualize SQL Joins.

http://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

score -1 · Answer 4 · answered Jan 17 '19 at 13:49

left join is not the opposit of right join , check the following case which gives different results

select * from 
(select 1 as x  where 1=1) a left join 
(select 1 as x  where 1=0) b on a.x=b.x inner join 
(select 1 as x  where 1=1) c on b.x=c.x

select * from 
(select 1 as x where 1=1) c inner join 
(select 1 as x where 1=0) b on c.x=b.x right join 
(select 1 as x where 1=1) a on b.x=a.x

tables b anc c are allways inner joined but in the first a is left joined to the others and on the second a is right joined

left join returns no rows while right join returns a row

score -2 · Answer 5 · edited Nov 15 '16 at 16:27

There is never any reason to prefer RIGHT JOIN, and LEFT JOIN is much clearer:

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

as it allows you to immediately see which table is being queried. Whereas with RIGHT JOIN:

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

the first table is written after the JOIN.

In my experience, I have never seen a RIGHT JOIN.

Reason to prefer RIGHT JOIN over LEFT JOIN

5 Answers5