2

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.

closed as too broad by MDCCL, Erik Darling, mustaccio, McNets, LowlyDBA Feb 27 at 0:23

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • You should describe the "situation where I might need to use a RIGHT OUTER and a LEFT OUTER JOIN in the same query". – MDCCL Feb 26 at 16:52
  • @MDCCL That is what the question is. Can we provide guidance on when this would ever be required? – Martin Smith Feb 26 at 17:08
  • @MartinSmith So that means that the OP wants someone to describe such a situation, that is, provide an example? I'm interpreting the issue as something like "I'm involved in a situation where I might need to use a RIGHT OUTER... in the same query, so I want guidance on it", but it looks like you are understanding the question correctly. – MDCCL Feb 26 at 17:18
  • @kirk Could you confirm Martin's interpretation? – MDCCL Feb 26 at 17:18
  • Martin does have the correct interpretation, however I can see how my phrasing would indicate the version that MDCCL came to. I apologize for the poor phrasing. I don't currently have a situation where I might need both. I am looking for an example where I might so that I can improve my skillset. And prevent myself from trying to solve all problems one way. The whole idea of "if you are a hammer, everything looks like a nail". – Kirk Saunders Feb 26 at 17:24
7

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.

  • I hit one once a long time ago; some horrible nasty combination of RIGHT JOIN and NOT IN clauses that was not transformable. I forget exactly what the concoction was. Incidentally, SQL server can't do a right loop join but must hash join the thing. – Joshua Feb 26 at 21:44
  • 1
    Not to worry, it's not in production anymore (if it ever was). – Joshua Feb 26 at 21:51

Not the answer you're looking for? Browse other questions tagged or ask your own question.