CODEWITHSUNDEEP
mysqlsqldatabasejoin
user84037
user84037

Reputation: 93

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

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?

Upvotes: 3

Views: 3657

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

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.

Upvotes: 6

Gerard H. Pille
Gerard H. Pille

Reputation: 2578

I sometimes read that the order in which the tuples are returned is insignificant. The order in which a real life database returns your records, may change because the engine decides it has found a better path, using an index or not, because a block of data has been moved, ... There are big differences between the relational theory and the database of your choice. I don't mean MySQL with that.

Upvotes: 2

Related Questions