Utilizing a Right and Left Outer Joins in same SELECT [closed]

Question

I am looking to better build my SQL Development skill set. Where I work currently most all the data fits into a hierarchy and we almost always start with the top most element to work our way "down". This results in really only needing to use INNER and LEFT OUTER joins for the various queries.

I am hoping to get some guidance on a situation where I might need to use a RIGHT OUTER and a LEFT OUTER JOIN in the same query.

Referencing a previous question on StackOverflow What is the difference between Left, Right, Outer and Inner Joins? I understand what a RIGHT OUTER and a LEFT OUTER JOIN is, however all problems I have been asked to solve, could be solved using only INNER and LEFT OUTER joins.

EDIT:

I did find that this is basically a duplicate of a previous StackOverflow question When or why would you use a right outer join instead of left?. It would appear that in general there is a consensus that

1. Most all queries using a RIGHT OUTER JOIN can be converted into an INNER and LEFT OUTER version.
2. There are some benefits for read-ability formatting or organizational processes which might require using a RIGHT OUTER instead of a LEFT OUTER.

There is also an answer on that question which details a particular situation where you would INNER JOIN a group of tables together and then RIGHT OUTER JOIN to find where the "right" table has records in the whole INNER JOIN'ed subset. I think this can be accomplished with an INNER JOIN and LEFT OUTER JOIN version it would just require some work.

Answer 1

In practice you never need to use a RIGHT JOIN, and I can't think of a scenario where I would want to.

Any query involving RIGHT JOINs that can't be trivially transformed into a query using only LEFT JOINs should probably be burned with fire.

You can solve almost any problem using a pipeline of CTEs, each of which uses 0 or more INNER JOINs followed by 0 or more LEFT JOINs.