In Oracle I was taught to join as
select *
from Table_a a, Table_b b, Table_c c
where a.id = b. Id and b.thing = c.thing
I was using this method, ignoring the training I'd followed to do explicit joins as everyone in my company uses the above approach.
We've moved to SQL server (Azure data studio) and I'm using
select *
from Table_a a
join Table_b b on a.id = b.id
join Table_c c on b.thing = c.thing
For inner joins I get this.
When I change this to left joins like this
select *
from Table_a a
left join Table_b b on a.id = b.id
left join Table_c c on b.thing = c.thing
I understand what a left join is, but I'm struggling to understand how the joining works.
Am I telling it - use all records from a, and then return all values from table b with nulls when nothing is found?
The bit I struggle with is the join to c.
Am I using table a as the base so I am looking for all values from a and then anything matching from c as nulls?
What happens if a to b was an inner join and c was then a left join?
Is c left joining to b?
Or left joining to a?
Example:
select *
from Table_a a
join Table_b b on a.id = b.id
left join Table_c c on b.thing = c.thing
Am I telling it in that scenario that I want the left join to be to b only, so I'm looking for the records only in a, all in b, and matching values with nulls in c?
I get what happens if I use ALL left joins, but if they are mixed and matched I can't get my head around it?
It's helpful here to remember your formal relational algrebra, where what we think of as a table is one way to express what is formally called a relation.
In this context, the result of joining two relations (A and B) is another relation.
The bit I struggle with is the join to c. Am I using table a as the base so I am looking for all values from a and then anything matching from c as nulls?
No. You can think of it as joining C to result of the A + B join. After all, A joined to B is just another relation.
In reality, things may not work this way. The database will take shortcuts where it can, in order to improve efficiency and performance. It is even free to re-order your JOINs when actually executing the query, if it thinks the new order is still accurate and will perform better. But semantically, you can think of it as if the join operations work from top down, or left to right, depending on how you format the query.
... oh, and of course also in accordance with how you build parentheses or nesting. If you think A join B on .. left join C on ... is confusing, wait till you see things like A JOIN (B JOIN C ON ...) ON ... (which, btw, you should generally avoid but is perfectly legal and even useful every once in a while).