Cascading Left Outer Joins

Question

What is the correct syntax for performing an external join with the following requirements:

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

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

I know how to write A-> B-> C, but I don’t know how to add D. I need a region or a bracket or something else.

Answer 1

this should work the way 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 you are joining, not the join order. D joins A and has nothing to do with B or C

Answer 2

The order you enter does not matter, the database will build a result set from each combination of rows in all tables, limited by the on clause. For example, since 1 = 1 is always true, this will give you 1000 lines:

 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 will give you 10 lines:

 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 complex queries a little more readable with indentation. We distinguish the second and subsequent dependencies with four spaces, such as:

 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