In Craig Freedman's blog, Nested Loops Join, he explains why the nested loops join cannot support a right outer join:
The problem is that we scan the inner table multiple times – once for each row of the outer join. We may encounter the same inner rows multiple times during these multiple scans. At what point can we conclude that a particular inner row has not or will not join?
Can someone please explain this in a really simple and educational way?
Does it mean that the loop starts with the outer table (
R1) and the scans the inner (
I understand that for a
R1 value that doesn't join with
R2, it should be replaced with a
NULL so the result set becomes (
NULL, R2). For me it seems impossible to return an
R2 value when
R1 does not join, for the reason that it cannot know which
R2 value to return. But that's not the way it's explained. Or is it?
SQL Server does in fact optimize (and often replaces)
RIGHT JOIN with
LEFT JOIN, but the question is to explain why it's technically impossible for a
NESTED LOOPS JOIN to use/support
RIGHT JOIN logic.