left join and group of inner join
Use paretheses to force joins order, kind of
SELECT * FROM ( TABLE1 JOIN TABLE2 ON ...)LEFT JOIN ( TABLE3 JOIN TABLE3_1 ON ... JOIN TABLE3_2 ON ... JOIN TABLE3_3 ON ...) ON ...
Check this answer.
@Serg answer is correct but you do not need to use parentheses if you specify the ON condition at the end of the statement.
SELECT * FROM TABLE1 JOIN TABLE2 ON ... LEFT JOIN TABLE3 ON ThisConditionShouldBeAtTheEnd JOIN TABLE3_1 ON ... JOIN TABLE3_2 ON ... JOIN TABLE3_3 ON ...you rewrite like this:
SELECT * FROM TABLE1 JOIN TABLE2 ON ... LEFT JOIN TABLE3 JOIN TABLE3_1 ON ... JOIN TABLE3_2 ON ... JOIN TABLE3_3 ON ... ON ThisConditionShouldBeAtTheEndSee also this article for more explanation. The reason is that JOIN conditions are evaluated from left to right (top-down) and you need the LEFT join condition to be evaluated after previous inner joins.
Disclaimer: I didn't have a oracle DB at hand to check but hopefully it will contain ideas to help you.
Solution 1: You could use parenthesis to state the intermediate joined table of (TABLE3 x N). Pseudo-code:
select *FROM TABLE1 inner join TABLE2 on (condition) left join ( table3 inner join table3_1 on (condition) inner join table3_2 on (condition) inner join table3_3 on (condition) ) as table3_joined ON (table3_joined.ID = table2.id)It works on MSSQL, at least. I cannot verify it works in oracle as well, but you could try. I consider this syntax very explicit and easy to follow/maintain.
Solution2: Alternative is to reuse the same left-to-right order that's troubling you for your advantage using right join. Pseudo-code:
select *from table3 inner join table3_1 on (condition) inner join table3_2 on (condition) inner join table3_3 on (condition) right join table2 on (condition) inner join table1 on (condition)This syntax probably works but imho using right joins makes the syntax a bit more uncomfortable to reason about.