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

sql database right-join
DCookie picture DCookie · Jan 12, 2009 · Viewed 40.3k times · Source

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

Yes - that Jake. picture Yes - that Jake. · Jan 12, 2009

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.

Related questions

Insert into ... values ( SELECT ... FROM ... )

I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (…

Read More
What is the difference between "INNER JOIN" and "OUTER JOIN"?

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?

Read More
'IF' in 'SELECT' statement - choose output value based on column values

SELECT id, amount FROM report I need amount to be amount if report.type='P' and -amount if report.type='N'. How do I add this to the above query?

Read More