SQL inner joining to left joined table

Question

So this might be more of a theoretical question about how joins in MySQL work, but I'd love some guidance.

Let's say I have three tables, table a, b and c, where table a and b are fact tables and table c is table b's dimension table. If I want to left join table b to table a (I want to keep all of the contents of table a, but also want matching contents in table b), can I still inner join table c to table b even table b is left joined? Or do I have to left join table c to table b? Or would both of these for all intents and purposes produce the same result?

select a.column, c.name
from tablea a
left join tableb b on a.id = b.id
inner join (?) tablec c on b.name_id = c.name

Answer 1

MySQL supports syntax that allows you to achieve what you want:

select a.column, c.name
from
  tablea a
  left join
    tableb b
    inner join tablec c on b.name_id = c.name
   on a.id = b.id
;

In this case tables tableb and tablec are joined first, then the result of their join is outer-joined to tablea.

The final result set, however, would be same as with @simon at rcl's solution.

Answer 2

In this case if there is no tablec entry for a tableb, then the whole join will fail and the tablea row will not be included. To include the tablsa entry you would need to make the join to tablc a left join:

select a.column, c.name
from tablea a
left join tableb b on a.id = b.id
left join tablec c on b.name_id = c.name

That will get you every tablea row even when there is no matching tableb row, and also every tablea and tableb when there is no tablec row.