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

Question

I think the title says it all. 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 ;-)

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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.

Answer 5

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.

Answer 6

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.