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.

  • 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 '19 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 '19 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 '19 at 17:18
  • @kirk Could you confirm Martin's interpretation? – MDCCL Feb 26 '19 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 '19 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 '19 at 21:44
  • 1
    Not to worry, it's not in production anymore (if it ever was). – Joshua Feb 26 '19 at 21:51

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