SQL a left join b -> b right join a: difference in order

Question

I sometimes read that it is equivalent to write "a left join b" and "b right jojn a". I thought I would understand this but I read in a book that this is not the case. It says that the result tuples are the same but they might be in different order. I could not find an explanation for that. I also tried to reproduce such a difference in order on my local MySQL Server, but I could not. The only difference seems to be order of attributes. Can anyone explain to me when or why a difference in tuple order occures?

Answer 1

This is more complicated than it sounds. First:

select *
from a left join b on . . . ;

and:

select *
from b right join a on . . . ;

Are likely to produce result sets that differ in two ways:

• The columns are in a different order.
• The rows may be in a different order.

Neither of these affects the equivalence of the result set from a set-theory perspective. But they could have practical effects. In general, if you care about ordering, then respectively:

• List the columns explicitly.
• Include an order by.

The more important point is that left join and right join are not interchangeable when there are multiple joins, because joins always associate from left to right regardless of type.

In the following, I'm leaving out the on clauses. Consider:

from a left join b left join c

You would think that the equivalent with right join is:

from c right join b right join a

But, the joins are grouped so the first is interpreted as:

from (a left join b) left join c

The second is:

from (c right join b) right join a

But the equivalent with right joins is:

from c right join (b right join a)

In both cases, every row from a will be int he result set. But the results can differ depending on the overlap among the three tables.