When or why would you use a right outer join instead of left?

Wikipedia states:

"In practice, explicit right outer joins are rarely used, since they can always be replaced with left outer joins and provide no additional functionality."

Can anyone provide a situation where they have preferred to use the RIGHT notation, and why? I can't think of a reason to ever use it. To me, it wouldn't ever make things more clear.

Edit: I'm an Oracle veteran making the New Year's Resolution to wean myself from the (+) syntax. I want to do it right

The only reason I can think of to use RIGHT OUTER JOIN is to try to make your SQL more self-documenting.

You might possibly want to use left joins for queries that have null rows in the dependent (many) side of one-to-many relationships and right joins on those queries that generate null rows in the independent side.

This can also occur in generated code or if a shop's coding requirements specify the order of declaration of tables in the FROM clause.

B RIGHT JOIN A is the same as A LEFT JOIN B

B RIGHT JOIN A reads: B ON RIGHT, THEN JOINS A. means the A is in left side of data set. just the same as A LEFT JOIN B

There are no performance that can be gained if you'll rearrange LEFT JOINs to RIGHT.

The only reasons I can think of why one would use RIGHT JOIN is if you are type of person that like to think from inside side out (select * from detail right join header). It's like others like little-endian, others like big-endian, others like top down design, others like bottom up design.

The other one is if you already have a humongous query where you want to add another table, when it's a pain in the neck to rearrange the query, so just plug the table to existing query using RIGHT JOIN.

I've never used right join before and never thought I could actually need it, and it seems a bit unnatural. But after I thought about it, it could be really useful in the situation, when you need to outer join one table with intersection of many tables, so you have tables like this:

And want to get result like this:

Or, in SQL (MS SQL Server):

declare @temp_a table (id int)
declare @temp_b table (id int)
declare @temp_c table (id int)
declare @temp_d table (id int)

insert into @temp_a
select 1 union all
select 2 union all
select 3 union all
select 4

insert into @temp_b
select 2 union all
select 3 union all
select 5

insert into @temp_c
select 1 union all
select 2 union all
select 4

insert into @temp_d
select id from @temp_a
union
select id from @temp_b
union
select id from @temp_c

select *
from @temp_a as a
	inner join @temp_b as b on b.id = a.id
	inner join @temp_c as c on c.id = a.id
	right outer join @temp_d as d on d.id = a.id

id          id          id          id
----------- ----------- ----------- -----------
NULL        NULL        NULL        1
2           2           2           2
NULL        NULL        NULL        3
NULL        NULL        NULL        4
NULL        NULL        NULL        5

So if you switch to the left join, results will not be the same.

select *
from @temp_d as d
	left outer join @temp_a as a on a.id = d.id
	left outer join @temp_b as b on b.id = d.id
	left outer join @temp_c as c on c.id = d.id

id          id          id          id
----------- ----------- ----------- -----------
1           1           NULL        1
2           2           2           2
3           3           3           NULL
4           4           NULL        4
5           NULL        5           NULL

The only way to do this without the right join is to use common table expression or subquery

select *
from @temp_d as d
	left outer join (
        select *
        from @temp_a as a
            inner join @temp_b as b on b.id = a.id
            inner join @temp_c as c on c.id = a.id
    ) as q on ...

The only time I would think of a right outer join is if I were fixing a full join, and it just so happened that I needed the result to contain all records from the table on the right. Even as lazy as I am, though, I would probably get so annoyed that I would rearrange it to use a left join.

This example from Wikipedia shows what I mean:

SELECT *  
FROM   employee 
   FULL OUTER JOIN department 
      ON employee.DepartmentID = department.DepartmentID

If you just replace the word FULL with RIGHT you have a new query, without having to swap the order of the ON clause.

SELECT * FROM table1 [BLANK] OUTER JOIN table2 ON table1.col = table2.col

Replace [BLANK] with:

LEFT - if you want all records from table1 even if they don't have a col that matches table2's (also included are table2 records with matches)

RIGHT - if you want all records from table2 even if they don't have a col that matches table1's (also included are table1 records with matches)

FULL - if you want all records from table1 and from table2

What is everyone talking about? They're the same? I don't think so.

SELECT * FROM table_a
INNER JOIN table_b ON ....
RIGHT JOIN table_c ON ....

How else could you quickly/easily inner join the first 2 tables and join with table_c while ensuring all rows in table_c are always selected?

I've not really had to think much on the right join but I suppose that I have not in nearly 20 years of writing SQL queries, come across a sound justification for using one. I've certainly seen plenty of them I'd guess arising from where developers have used built-in query builders.

Whenever I've encountered one, I've rewritten the query to eliminate it - I've found they just require too much additional mental energy to learn or re-learn if you haven't visited the query for some time and it hasn't been uncommon for the intent of the query to become lost or return incorrect results - and it's usually this incorrectness that has led to requests for me to review why the queries weren't working.

In thinking about it, once you introduce a right-join, you now have what I'd consider competing branches of logic which need to meet in the middle. If additional requirements/conditions are introduced, both of these branches may be further extended and you now have more complexity you're having to juggle to ensure that one branch isn't giving rise to incorrect results.

Further, once you introduce a right join, other less-experienced developers that work on the query later may simply bolt on additional tables to the right-join portion of the query and in doing so, expanding competing logic flows that still need to meet in the middle; or in some cases I've seen, start nesting views because they don't want to touch the original logic, perhaps in part, this is because they may not understand the query or the business rules that were in place that drove the logic.

SQL statements, in addition to being correct, should be as easy to read and expressively concise as possible (because they represent single atomic actions, and your mind needs to grok them completely to avoid unintended consequences.) Sometimes an expression is more clearly stated with a right outer join.

But one can always be transformed into the other, and the optimizer will do as well with one as the other.

For quite a while, at least one of the major rdbms products only supported LEFT OUTER JOIN. (I believe it was MySQL.)

The only times I've used a right join have been when I want to look at two sets of data and I already have the joins in a specific order for the left or inner join from a previously written query. In this case, say you want to see as one set of data the records not included in table a but in table b and in a another set the records not in table b but in table a. Even then I tend only to do this to save time doing research but would change it if it was code that would be run more than once.

In some SQL databases, there are optimizer hints that tell the optimizer to join the tables in the order in which they appear in the FROM clause - e.g. /*+ORDERED */ in Oracle. In some simple implementations, this might even be the only execution plan available.

In such cases order of tables in the FROM clause matters so RIGHT JOIN could be useful.

I think it's difficult if you don't have right join in this case. ex with oracle.

with a as(
     select 1 id, 'a' name from dual union all
     select 2 id, 'b' name from dual union all
     select 3 id, 'c' name from dual union all
     select 4 id, 'd' name from dual union all
     select 5 id, 'e' name from dual union all
     select 6 id, 'f' name from dual 
), bx as(
   select 1 id, 'fa' f from dual union all
   select 3 id, 'fb' f from dual union all
   select 6 id, 'f' f from dual union all
   select 6 id, 'fc' f from dual 
)
select a.*, b.f, x.f
from a left join bx b on a.id = b.id
right join bx x on a.id = x.id
order by a.id