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?

I can't recall one case where I wanted a right join. I have had cases where the execution plan of a query flipped a left join around into a right join for performance reasons. But from a pure code writing standpoint, nope, I don't recall ever writing a right join. — Brandon, Nov 14 '16 at 14:01
I don't see this as a question about "explaining, writing or debugging code". It's asking why a language has two features which apparently do the same thing, if there is actually any difference between them, and if not when the "non-obvious" one would be preferred. — Philip Kendall, Nov 14 '16 at 14:19
No, a left join almost always captures what you need most succinctly and is easier to reason about. At my workplace, we actually have a standard in place to prevent right joins since there's never a case where they are necessary (i.e. they can always be replaced by the opposite left join). — mgw854, Nov 14 '16 at 14:24
I've reset the close votes - this question is on-topic here, since understanding the difference between joins affects software design (database design is part of software design, as are algorithms that may query databases). It may also [be on-topic at Database Administrators](http://dba.stackexchange.com/help/on-topic), and there may be a duplicate there as well. — Thomas Owens, Nov 14 '16 at 14:26
I'm not sure if you are suggesting that `RIGHT JOIN` is recommended or more common. If that is your premise, it's incorrect. I can't think of a time I've ever seen right join used in either code nor in examples. It's `JOIN` or `LEFT OUTER JOIN`. In rare cases you might see a `FULL OUTER JOIN`. — JimmyJames, Nov 14 '16 at 15:32
@JimmyJames Neither; my experience has been the same as yours, and I think `LEFT JOIN` is clearer. But as a result, I was wondering if there was any case where `RIGHT JOIN` would be preferred. — Zev Spitz, Nov 14 '16 at 15:37
See this answer: http://stackoverflow.com/questions/4715677/difference-between-left-join-and-right-join-in-sql-server — , Nov 14 '16 at 17:07
I have used right joins before for sake of convenience, when I needed to edit a query I wrote earlier and didn't want to rearrange all the existing joins to make a left join work. Of course, if this were a query to be used more than once I would put forth that extra effort to make it a left join for readability reasons. — Devsman, Nov 14 '16 at 21:14

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

14

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

edited Jan 25 '17 at 15:53

answered Nov 14 '16 at 19:42

Tulains Córdova

39,201
12
97
154

1

I had begun to think along these lines, but I do not think it is correct. I started imagining writing SQL in Hebrew (which I have never actually done). OK: right justified, right-to-left, top-down. You will still mention table A first then table B. With a right join, you will then exclude (especially in the null case) most or all of the table mentioned first. I think that the human mind tends to associate **first** with **primary** and **most important**. This occurs regardless of writing direction. You could call them "FIRST JOIN" and "SECOND JOIN" and this bias would still occur. – Mike Nov 14 '16 at 19:59
I've include a link to the image you have shown below. – Jon Raynor Nov 14 '16 at 20:05
@Mike I'm not suggesting people write SQL in arabic or hebrew. Just that perhaps the orientation of your mother tongue could be a reason for you to prefer right-joins. But that is just a possibility. I find left joins more natural. – Tulains Córdova Nov 14 '16 at 20:07
Now I gave the due credit to the creator of the image. I've had it for years in my HD and I didn't remember where it came from, – Tulains Córdova Nov 14 '16 at 20:17
1

I am fluent in Hebrew and I still find `LEFT JOIN` more natural. – Zev Spitz Nov 15 '16 at 05:32

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.

I don't follow why you can't write multiple left joins in this case: `SELECT p.*, o.*, d.* FROM Persons p LEFT JOIN Orders o ON o.CustomerID = p.ID LEFT JOIN SpecialOrders d ON o.Id = d.OrderID`. — Zev Spitz, Nov 14 '16 at 16:22
@RobertHarvey I am, but I'm not sure what handed-ness has to do with it. — Becuzz, Nov 14 '16 at 16:23
@ZevSpitz Maybe it wasn't clear from what I wrote, but the idea was you only wanted the fields from orders only if a special order details record exists, ie. left join them only if the pair of them exist. — Becuzz, Nov 14 '16 at 16:24

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

this reads more like a tangential comment, see [answer] – gnat Jan 17 '19 at 14:59 — gnat, Jan 17 '19 at 14:59

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

-2

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.

edited Nov 15 '16 at 16:27

Zev Spitz

693
5
18

answered Nov 14 '16 at 16:02

kirie

452
2
6

2

What does this add to the question? The question is not _what is the difference?_; The question is _why should I use `RIGHT JOIN`?_. – Zev Spitz Nov 14 '16 at 16:03
@ZevSpitz You asking for the reason to prefer which one, I give you a reason. Which use case doesn't matter since it is matter of taste. – kirie Nov 14 '16 at 16:05

Reason to prefer RIGHT JOIN over LEFT JOIN

5 Answers5