CODEWITHSUNDEEP
sqloracle-databasejoinleft-joininner-join
lorraine batol
lorraine batol

Reputation: 6281

left join and group of inner join

Say, I have the following query:

SELECT * FROM TABLE1 
    JOIN TABLE2 ON ...
    LEFT JOIN TABLE3 ON ...
        JOIN TABLE3_1 ON ...
        JOIN TABLE3_2 ON ...
        JOIN TABLE3_3 ON ...

What I want to achieve is for TABLE3, TABLE3_1, TABLE3_2, TABLE3_3 to have inner joins within them (I only need all the matching data between them, the rest gone). Then for TABLE1, TABLE2 to have inner joins too. But from TABLE1 + TABLE2 result, some won't have a corresponding entries to TABLE3, and that's okay, I will still want it.

Using the above pseudo code if I run it as it is, obviously it will not achieve the same result.

Upvotes: 6

Views: 11412

Answers (4)

BriteSponge
BriteSponge

Reputation: 1054

An alternative to the other answers is a CTE (common table expression). This just has a query for the inner joined table3 group and a query for the inner joined table1/table 2 group and those two groups are outer joined in the main query. For me (and obviously this is subjective) I would find this easier to understand what was going on if I came across it in someone else's code. 

WITH 
t3_group AS
 (SELECT * 
   FROM table3 ON ...
        INNER JOIN table3_1 ON ...
        INNER JOIN table3_2 ON ...
        INNER JOIN table3_3 ON ... ),
t1_t2_group AS
 (SELECT *
   FROM table1
        INNER JOIN table2 ON ...)
SELECT * 
  FROM t1_t2_group
       LEFT JOIN t3_group ON ...

Upvotes: 1

Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8104

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 ThisConditionShouldBeAtTheEnd

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

Upvotes: 5

Imre Pühvel
Imre Pühvel

Reputation: 5004

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.

Upvotes: 2

Serg
Serg

Reputation: 22811

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

Upvotes: 12

Related Questions