• Excellent question and explanation.

    But it makes the behaviour in the first right join question seem even more bizarre than it seemed before. In this case there's a query that by very simple but not blindingly obvious reasoning can be shown to be equivalent to the original query and eliminates right loop join, and despite that lack of blinding obviousness the optimiser does the transformation and provides a plan. In the first case there's a blindingly obvious equivalent query (using left join) that doesn't run into the "no loop with right join" restriction, and the optimiser doesn't do the transformation despite the total obviousness. Not only is the statement in BoL misleading, and the actual behaviour with right loop inconsistent, but also the optimiser's choice of when to produce a plan and when not seems perverse! Or actually, it's the whole concept of "no loop with right join" that is ridiculous and perverse, because every right join is (trivially) equivalent to a left join, and there's no "no loop with left join" restriction - to make any sense at all the restriction would have to be purely syntactic, with no semantic content, but this QoTD demonstrates that it is not a syntactic restriction.

    Tom