Cascading left outer joins

Question

What is the correct syntax to perform an outer join with the following requirements:

A left outer join B on A.c1 = B.c1
B left outer join C on B.c2 = C.c2
A left outer join D on A.c1 = D.c1

So A, B, and C cascade and A and D cascade.

I know how to write the A->B->C but I don't know how to add D. I need scope or parenthesis or something.

Answer 1

this should work as you want:

SELECT
    *
    FROM A 
    left outer join B on A.c1 = B.c1
    left outer join C on B.c2 = C.c2
    left outer join D on A.c1 = D.c1

the DB engine looks at what your are joining to, not the order of the joins. D joins to A, and has nothing to do with B or C

Answer 2

The order in which you join doesn't matter, the database will build a result set of every combination of rows in all tables, limited by the on clause. For example, because 1=1 is always true, this would give you 1000 rows:

select *
from ten_row_table A
left join ten_row_table B on 1=1
left join ten_row_table C on 1=1

But this would give you 10 rows:

select *
from ten_row_table A
left join ten_row_table B on A.id = B.id
left join ten_row_table C on A.id = C.id

You can make complicated queries slightly more readable by indentation. We indent second and further dependencies by four spaces, like:

from A
left outer join B on A.c1 = B.c1
    left outer join C on B.c2 = C.c2
        left outer join D on C.c3 = D.c3
    left outer join E on B.c2 = E.c2
left outer join F on A.c1 = F.c1