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
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.