where to put the ON clause after several joins (inner and left)

Question

My question is that when I have a SELECT with more than one JOIN. Where am I supposed to put the ON clause?

For example:

1. when it's an inner join after an inner join
2. when it's an inner join after a left join
3. when it's a left join after an inner join

In the first example, I've seen people put the ON clause right after each joins. In the second example, I've seen people put all the ON clause after the last JOIN. So right now I'm a little bit confused on where to put it and does it give me the same answer even if it is put in different places.

Answer 1

You should interleave the on clauses, regardless of the type of join. So:

from a join
     b
     on . . . left join
     c
     on . . .

And so on as you add more tables.

MySQL makes the on clause optional, which confuses things. However, standard SQL does allow:

from a join
     b join
     c
     on b.? = c.?
     on a.? = b.?

However, this is generally discouraged. People find that hard to follow.